High-Speed Locking and Concurrency Control

A multiuser environment must have the means to prevent concurrent transactions from interfering with each other. SQL Server employs a fast, sophisticated, automatic, and transparent locking scheme that arbitrates user requests. The process of obtaining a lock is exceptionally fast—a matter of microseconds—because lock information is stored in a memory-resident table. SQL Server employs multiple levels of locking (depending on the degree of exclusion required) and uses the least restrictive lock needed to protect data during a given operation.

SQL Server automatically acquires the necessary locks whenever a SQL statement is executed. SQL Server uses:

Shared locks for read-only operations. If a shared lock has been applied to a data page or pages, a second transaction can also acquire a shared lock even though the first transaction has not completed. By default, SQL Server releases shared locks as soon as the data page(s) is no longer needed.
Exclusive locks for write operations—UPDATE, INSERT, DELETE. When an exclusive lock is set, no other transaction can acquire a lock of any kind on the same object until SQL Server releases the original lock at the end of the transaction.
Update locks whenever a transaction intends to modify a page prior to a write operation. Update locks reduce contention among multiple writers.

Within SQL Server, the efficient use of automatic locking prevents destructive interference from concurrent transactions. By default, the changes made by the SQL statements only become visible to other transactions after those changes are committed.