Time once again for another installment of SQL Server A to Z. Today we’re talking about locks. Locking is just a fact of life with any RDBMS, it’s what allows you to have multiple concurrent users in your database while still maintaining data integrity. Without locks there would be chaos. Chaos I tell you. So I think we should take the time to familiarize ourselves with the various types of locks in SQL Server and how they play (or don’t play) with each other. Ready?
Shared Lock (S)
Shared lock. Such a nice, friendly name, isn’t it? Shared locks are used by read operations to ensure that data isn’t modified while it’s being read. However shared locks do allow other sessions to read that data and obtain their own shared lock on a resource. Once the data is read, the shared lock is released, the exception to this being if you’re using an isolation level such as REPEATABLE READ.
Exclusive Lock (X)
The exclusive lock is the least friendly of the locks. It effectively says this is mine and you can’t have it, period. Exclusive locks are not compatible with any other locks, not even shared locks. SQL Server obtains exclusive locks on a resource when it wants to modify data.
Update lock (U)
An update lock is used on resources that can be updated. It’s kind of like the mama bear between shared locks (baby bear) and exclusive locks (papa bear). A resource with an update lock on it can still have shared locks from other sessions, so it won’t block reads. However, only one update lock is allowed on a resource at a time, and no exclusive locks can be obtained, so it will block potential writers.
Intent Lock (I)
An intent lock means that SQL Server intends to place a lock on a resource farther down in the hierarchy. For example, if I issue a SELECT statement inside the AdventureWorks database, SQL Server not only places a shared lock on the row(s) I’m reading, it will also place an Intent Shared lock (IS) on the page(s) and on the table itself. The intent locks prevent another session from modifying or dropping the table/page while my session is reading that row.
Schema lock (Sch)
There are two types of Schema locks, Schema Modification (Sch-M) and Schema Stability (Sch-S). SQL Server uses schema modification locks when it performs DDL operations such as ALTER TABLE or DROP TABLE. Like exclusive locks, schema modification locks are incompatible with other locks. Schema stability locks are used during read operations. They’re the equivalent of a shared lock on the object definition. It won’t block normal processing, including write operations, but it will block other DDL operations on that resource.
Bulk Update lock (BU)
Bulk update locks are used during bulk insert operations to allow multiple processes to import data concurrently while still blocking other transactions from reading/writing to the table. Bulk update locks lock the entire table, thus minimizing the locking overhead for the import operation.
Key-Range lock
A key-range lock is used in the serializable transaction isolation level to prevent phantom reads. It locks a range of rows being read by a transaction to not only prevent those rows from being modified, but to also prevent new rows from being inserted into that range. This is how SQL Server ensures that the rows returned by a select statement during a transaction will always be the same no matter how many times that select statement is run within that transaction.
Example
Let’s take a look at locks in practice.
BEGIN TRAN USE AdventureWorks SELECT * FROM Person.Contact WITH (HOLDLOCK) WHERE ContactID = 155 SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks
Because I’m just issuing a SELECT statement, any shared locks would disappear immediately if I didn’t use the HOLDLOCK hint. By using that hint, however, we’re able to see the locks until we rollback the transaction.
We can see with this simple query, SQL Server has placed a Shared Lock on the database itself (so we can’t drop it), it has a schema lock on the table metadata (so we can’t ALTER it), there’s a shared lock on the clustered index key that we’re reading, and intent shared locks on the page where that row is located as well as the table itself. If we were to open another session and try to update that row, that session session would be blocked from obtaining the exclusive lock it needs and would hang until our first session rolled back or was committed.
Further reading
For more information on locking in SQL Server, take a look at the following resources.
Understanding Locking in SQL Server
SQL Server’s Locking Explained