This is a continuation of my prior article on SQL Server database concurrency.

Locking

Every request to read from, modify, or even insert or delete data must first acquire a lock on a database object. That object could be anything from the entire database to a table to a range of rows, to an individual row. On top of that, there are different modes of locks, with some being more aggressive than others as far as what other behavior they allow.

Frequent waiting to acquire locks is one of the most common characteristics of a slow database. Generally speaking, you're going to want your SQL statements to lock the minimally necessary set of database objects in order to keep traffic flowing steadily. This means you need useful and efficient indexes that prevent behaviors such as a table scan.

Standard Lock Modes

In order to understand locking at a granular level, we need to look deeper into lock modes. Lock modes dictate what other types of queries can be touching the same data a query is locking on.

Note: The letters in parentheses in the list below are the standard SQL Server abbreviations for these lock modes and appear in some areas of SQL Server Management Studio (SSMS)

Exclusive (X)

An exclusive lock requires that no other active transaction (including auto-commit transactions / single SQL statements) can touch the locked object while the exclusive lock is held.

Exclusive locks are typically acquired by inserts, updates, and deletes.

Shared (S)

Shared locks, as the name implies, can be shared between transactions that only desire read access to the data in question. Shared locks do not allow exclusive locks while they are active, so exclusive locks must wait for shared locks to complete before they are acquired.

Update (U)

Update locks are a combination of an exclusive and shared lock. These are typically used when a record or set of records must be found and updated. They allow for shared locks on the range of data being scanned, but acquire an exclusive lock on data being modified.

Intent Lock Modes

Intent locks are essentially publishing an intent to acquire a lock in the near future. These are essentially internal coordination locks used by SQL Server to allow the primary locking modes to function properly.

You can think of these locks as effectively a form of traffic light allowing queries already executing to clear in preparation for the lock being requested. When delays occur in data querying due to locking, they occur while intent locks are waiting to acquire their actual lock - essentially traffic waiting for the lock to be acquired and the light to turn green.

As such, those lock modes don't need a lot of additional detail, but can be listed as follows:

• Intent Exclusive (IX)
• Intent Shared (IS)
• Intent Update (IU)

Conversion Locks

Conversion locks occur internally when SQL server must convert from one lock type to another. They effectively represent transitioning from one lock type to another between multiple queries in a transaction. The shared lock types are:

• Shared with Intent Exclusive (SIX) - Moving from Shared to Exclusive
• Shared with Intent Update (SIU) - Moving from Shared to Update
• Update with Intent Exclusive (UIX) - Moving from Update to Exclusive

Other Locks

There are a few other lock types like schema locks and bulk update locks, but they are not as common and are not covered in this article.

Lock Hierarchy

As referenced earlier, locks can be acquired at a range of levels. Each level contains a hierarchy of children. For example, the database contains multiple tables, tables can contain multiple pages of data, and pages can contain multiple rows.

Locks granted on high-level resources cascade down to their children resources, so it's more efficient to lock only a few rows than to lock a page or two or even a table. This can be somewhat controlled by the use of indexes within the database.

Why is this Important?

Understanding locking at a basic level is helpful for designing efficient databases and transactions as indexes impact more than just pure lookup speed - the lack of proper index usage will slow down other queries by causing needless database locking.

Additionally, if competing locks are prevalent in your transactions, you are likely to encounter deadlocks, the topic of my next article in this series.