Locking is a crucial function of a multiuser database system
such as SQL Server, even if you are operating primarily in the Snapshot
isolation level with optimistic concurrency.
SQL Server lets you manage
multiple users simultaneously and ensures that the transactions observe the
properties of the chosen isolation level.
Even though readers do not block
writers and writers do not block readers in Snapshot isolation, writers do
acquire locks and can still block other writers, and if two writers try to
change the same data concurrently, a conflict occurs that must be resolved.
The
locking code acquires and releases various types of locks, such as share locks
for reading, exclusive locks for writing, intent locks taken at a higher
granularity to signal a potential “plan” to perform some operation, and extent
locks for space allocation. It manages compatibility between the lock types,
resolves deadlocks, and escalates locks if needed. The locking code controls
table, page, and row locks as well as system data locks.
