SQL Server: Locking basics

Vinod Kumar

The learning journey can never stop. As we learn the basic building blocks of SQL Server there is always a need to do refreshers. This blog is on the same lines. I would urge you to read the Blog around Concurrency over at Pinal’s site and then continue the reading below because the basics of Locking is incomplete if we don’t understand what are the concurrency basics. Just like you have traffic signals that regulates traffic to make sure there are no grid locks or long waits for commuters. The parallel lanes for traffic is synonymous to concurrency. SQL Server uses locks as an mechanism to bring the consistency at the same time build the concurrency for the system. Locks are inevitable in any database world and those are also fundamental building blocks.

1. Locks are applied in both optimistic and pessimistic concurrency models.
2. Locking is used for maintaining consistency for concurrent transactions.
3. Read operations acquire shared locks, exclusive locks by write operations while update locks are got during initial portion of an update operation when SQL Server is searching for the data to update.
4. SQL Server releases locks automatically and manages compatibility between various locking modes, resolves deadlocks and lock escalations are done to reduce overheads.
5. SQL Server controls locks on tables, on the pages of the table, on index keys and on individual rows of data based on the operation done.

Lock Modes

1. Shared Locks
   1. Automatically acquired while reading data.
   2. This lock can be on a page, table, index key or individual row based on the isolation level.
   3. Multiple processes can hold shared locks on the same data and read from it too.
   4. No other process can get an exclusive lock on data that has a shared lock. Neither can a process acquire a shared lock when there is an exclusive lock already on the table.
   5. Shared locks are not held for the whole duration of read in the default isolation level.
2. Exclusive Locks
   1. This lock is acquired on data when modifications are done by DML statements like INSERT, UPDATE or DELETE operation.
   2. At any point in time exclusive locks can be held by only one process.
   3. In an exclusive locking mode no other process can take locks in this process.
   4. Exclusive locks are taken for the duration of the transaction length as the process can either commit or rollback.
3. Update Locks

1. This is an hybrid of exclusive and shared locks.
2. This lock is taken when SQL Server searches the table to find the resource that needs to be modified.
3. This can also be initiated using using query hints. It is desirable not to use the same.
4. Even in this lock, it is not the sufficient level to change data. SQL Server must take exclusive lock to modify data.
5. Used as an intermediate lock before escalating to exclusive locks.

Intent Locks

1. As the name suggests is a pre-qualifiers to shared/update/exclusive locks.
2. Since SQL Server can acquire locks at different levels, before the process completes or the statements completes SQL Server might want to know if locks are already acquired.
3. This is an intermediate locking state and not to be confused as a separate locking mode.

Special Lock Modes

1. Schema stability locks – at the time of compilations, this lock makes sure that no other process is modifying the schema of the underlying objects.
2. Schema modification locks – acquired when a table’s structure is being modified.
3. Bulk update locks – locks taken for BULK INSERT command or bcp utility operation. This lock can also be turned on using the table hints.

Conversion Locks

1. As the name suggests, this lock happens during a conversion from one mode to another. There are three types SIX, SIU, UIX where SIX is the most common and the details are below.

Key-Range Locks

1. Locks taken in serializable isolation level for locking ranges of data.
2. Can be taken only on keys.

Let us look at the common abbreviation and locking mode descriptions:

1. S – Shared: Allows processes to read concurrently but cannot change the resource locked.
2. X – Exclusive: Prevents processes from modifying or reading data in the data.
3. U – Update: Prevents external processes from acquiring an update or exclusive lock.
4. IS – Intent shared: Acquired when resource is locked with a shared lock.
5. IU – Intent update: Acquired when the resource is locked with an update lock.
6. IX – Intent exclusive: Acquired when the resource is locked with an exclusive lock.
7. SIX – Shared with intent exclusive: Shows that a shared lock pages have either a page or row with exclusive locks.
8. SIU – Shared with intent update: Shows that a shared lock pages have either a page or row with an update lock.
9. UIX – Update with intent exclusive: Shows that a update lock pages have either a page or row with exclusive locks.
10. Sch-S – Schema stability: Used to show a query using the object is being compiled at that moment.
11. Sch-M – Schema modification: Used to indicate the table’s structure is being updated at this moment.
12. BU – Bulk update: Lock acquired when bulk copy operation (like bcp) is copying data into a table.

Lock Granularity

1. SQL Server can lock resources at the table, page, partition or row level.
2. If locks are escalated, SQL Server can also lock a single partition of a table or index.
3. SQL Server can also lock index keys and ranges of index keys if needed.
4. DMV sys.dm_tran_locks view keeps track of each lock and contains information about the resource which is locked and an identifier for the specific resource

Key Locks

1. SQL Server use of key locks depends on the isolation level of the transaction.

1. If isolation level is Read Committed, Repeatable Read or Snapshot, SQL Server locks the actual index keys accessed while processing the query.
2. If isolation level is serializable, to counter phantoms reads, key-range locks are acquired for the range of values queried.
3. Incase of tables with clustered index, leaf node contains the data and hence the locks are acquired at this level.

There are 9 types of key-range locks:

1. RangeS-S
2. RangeS-U
3. RangeIn-Null
4. RangeX-X
5. RangeIn-S
6. RangeIn-U
7. RangeIn-X
8. RangeX-S
9. RangeX-U 

Other Locking Assets

1. We saw locks on objects, pages, keys, partitions and rows. Three are other SQL Server can lock too.
2. Extents are units of 64KB disk space that can be locked. This is a special type of lock that is taken when an object needs to expand and a new extent is allocated.
3. Generally connections hold a lock on at least one database object and will have resource_type = DATABASE at a minimum running in the session.
4. You occasionally have locks on individual partitions, indicated in the lock metadata as HOBT locks. Occurs when locks are escalated and escalation to that partition level is allowed

Lock Duration

1. The length of time that a lock is held depends primarily on the mode of the lock and transaction isolation level in effect.
2. Shared locks in Read Committed and Snapshot isolation levels are released as soon as SQL Server has read and processed the locked data
3. Shared locks behave the same as exclusive locks in Repeatable Read and Serializable isolation levels – they are not released until the end of the transaction
4. Update locks are also held until the end of the transaction unless it has been promoted to an exclusive lock in which case it behaves like the exclusive lock

Lock Ownership

1. Lock duration is directly affected by lock ownership
2. Ownership == scope of the lock
3. Four types of lock scopes
   1. Transactions – Durations depend on isolation level and lock mode (shared locks and update/exclusive locks)
   2. Cursors – requested explicitly when a cursor is declared. If a cursor is opened using a locking mode of SCROLL_LOCKS, a cursor lock is held on every row fetched until the next row is fetched or the cursor is closed. Even if the transaction commits before the next fetch, the cursor lock is not released
   3. Transaction_Workspaces – acquired every time a database is accessed and the resource associated with these locks is always a database. A workspace holds database locks for sessions – usually there is one workspace per session and all DATABASE locks acquired in the session are kept in the same workspace object. In distributed transactions, multiple sessions are enlisted into the same workspace so they share the database locks
   4. Sessions – must be requested explicitly using the sp_getapplock procedure, and apply only to APPLICATION locks. Its duration is until the session disconnects or the lock is released explicitly

Thanks for reading this far. I know there is more to learn and write like Lock Timeout, Locking Hints, Version Stores, Snapshot Isolation Level, Row Versioning, Deadlocks, Lock Escalations, Row-level Locking and Page-level Locking, Lock Blocks, internal locking etc !!! I will surely keep you posted similarly in future.