Locks are an essential and integral part of SQL Server. When we deal with a multi-user system, there will always be cases of simultaneous access to the same resources from different users. Thus, the SQL Server must handle that concurrency correctly. Locking allows working seamlessly under such circumstances.
There are several types of locks. The implementation of each particular lock type depends on the resources accessed by the query and their usage. This article will clarify the SQL Server lock mechanism and explain how to monitor SQL Server locking with SQL Server standard dynamic management views.
But before we start to explain SQL Server lock architecture, let’s first refer to the ACID database concept. It has a significant value for locking; therefore, we can’t omit it here.
Now, we explain these requirements briefly.
• Atomicity reflects the principle of indivisibility, which is the main transaction process feature. A transaction block cannot be left unattended. Half of the remaining transaction block causes data inconsistency. Either the entire transaction performs, or it returns to the beginning. If even one of the transactions fails, all transaction blocks will fail. All changes made by the transaction are undone and returned to their previous state.
• Consistency sets the substructure of the non-divisibility rule. Transaction data must provide consistency. If the update operation is performed in a transaction, either all remaining transactions must be performed, or the update operation must be canceled. This data is very important in terms of consistency.
• Isolation is a request packet for each transaction database. Changes made by a request packet must be invisible to another transaction before it completes. Each transaction must be processed separately. All transactions must be visible to another transaction after they occur.
• Durability. Transactions can perform complex operations with data. To secure all these transactions, they must be resistant to transaction errors. System problems in SQL Server should be prepared and resilient against power failure, operating system, or other software-induced errors.
A lock is a mechanism to ensure data consistency. SQL Server locks objects when the transaction starts (transaction is the smallest stack of the process, which cannot be divided into smaller pieces). After the transaction is completed, SQL Server releases the locked object.
In other words, when the object gets locked, all other transactions related to that object will have to wait. It is only after the lock release they can get access and make the data changes as required. The wait is also registered on SQL Server.
It refers directly to the isolation part.
SQL Server has a lock hierarchy which acquires lock objects in this hierarchy. A database is located at the top of the hierarchy and a row is located at the bottom. The below image illustrates the lock hierarchy of SQL Server.
Types of Locks in SQL Server
Depending on each particular case, SQL Server can apply different lock types to the object.
• Shared (S) Locks: This lock type occurs when the object needs to be read. This lock type does not cause many problems.
• Exclusive (X) Locks: When this lock type occurs, it occurs to prevent other transactions to modify or access a locked object.
• Update (U) Locks: This lock type is similar to the exclusive lock but it has some differences. We can divide the update operation into different phases: read phase and write phase. During the read phase, SQL Server does not want other transactions to have access to this object to be changed. For this reason, SQL Server uses the update lock.
• Intent Locks: The intent lock occurs when SQL Server wants to acquire the shared (S) lock or exclusive (X) lock on some of the resources lower in the lock hierarchy. In practice, when SQL Server acquires a lock on a page or row, the intent lock is required in the table.
• Schema (Sch) lock: This type prevents a table or index used by another session from being dropped or prevents modifications to its schema. When this lock type applies to a resource, access to the object is blocked.
• Bulk update (BU) locks: These locks don’t allow processes to access a table during a bulk load procedure in progress. However, concurrent bulk load processes will be allowed. Thus, you can run parallel loads. A bulk load procedure is done by bulk copy program (bcp) or BULK INSERT.
Identifying SQL Server locks
SQL Server offers a lot of dynamic management views to access metrics. To identify SQL Server locks, we can use the sys.dm_tran_locks view. It allows us to find a lot of information about the lock manager resources that are currently active.
In the first example, we create a demo table that does not include any indexes. Then, we try to update this demo table.
CREATE TABLE TestBlock (Id INT , Nm VARCHAR(100)) INSERT INTO TestBlock values(1,'CodingSight') In this step, we will create an open transaction and analyze the locked resources. BEGIN TRAN UPDATE TestBlock SET Nm='NewValue_CodingSight' where Id=1 select @@SPID
Now, we will check the sys.dm_tran_lock view.
select * from sys.dm_tran_locks WHERE request_session_id=74
This view returns the information about active lock resources, but it is impossible to understand some of the data in this view.
For this reason, we have to join the sys.dm_tran_locks view to other views.
SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() and request_session_id=74 ORDER BY request_session_id, resource_associated_entity_id
In the above image, you can see the locked resources – SQL Server acquires the exclusive lock. (RID: A row identifier used to lock a single row within a heap)
Simultaneously, SQL Server acquires the intent exclusive lock on the page and the TestBlock table.
It means that any other process cannot read this resource until the SQL Server releases the locks. This is the basic lock mechanism in the SQL Server.
Now, we populate some synthetic data on our test table.
TRUNCATE TABLE TestBlock DECLARE @K AS INT=0 WHILE @K <8000 BEGIN INSERT TestBlock VALUES(@K, CAST(@K AS varchar(10)) + ' Value' ) SET @K=@K+1 END After completing this step, we will run two queries and check the sys.dm_tran_locks view. BEGIN TRAN UPDATE TestBlock set Nm ='New_Value' where Id<5000
In the above query, SQL Server acquires the exclusive lock on every single row. Now, we will run another query.
BEGIN TRAN UPDATE TestBlock set Nm ='New_Value' where Id<7000
As shown above, SQL Server creates the exclusive lock on the table. The reason is, SQL Server tries to acquire a lot of RID locks for these rows, which will be updated.
It causes severe resource consumption in the database engine. Therefore, SQL Server automatically moves this exclusive lock to the up-level object in the lock hierarchy.
We define this mechanism as Lock Escalation.
Lock Escalation can be changed at the table level.
ALTER TABLE XX_TableName SET ( LOCK_ESCALATION = AUTO -- or TABLE or DISABLE ) GO
Note also some additional details about lock escalation.
If you have a partitioned table, then we can set the escalation to the partition level.
In this step, we execute a query that creates a lock in the AdventureWorks HumanResources table. This table has clustered and non-clustered indexes.
BEGIN TRAN UPDATE [HumanResources].[Department] SET Name='NewName' where DepartmentID=1
As you can see in the below result pane, our transaction acquires exclusive locks in the PK_Department_DepartmentID cluster index key. Also, it acquires exclusive locks in the AK_Department_Name non-clustered index key.
Now, we can ask, “Why SQL Server locks a non-clustered index?”
The Name column is indexed in the AK_Department_Name non-clustered index, and we try to change the Name column. In this case, SQL Server needs to change any non-clustered indexes on that column. The non-clustered index leaf level includes every KEY value sorted out.
This article highlighted the main lines of the SQL Server lock mechanisms and considered the use of sys.dm_tran_locks.
The sys.dm_tran_locks view returns a lot of information about the currently active lock resources. You can also find lots of sample queries about this view.