Main concept of SQL Server locking

Total: 38 Average: 4

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.

The database theory explains the ACID database in the following way: if a database is called a relational database, it has to meet Atomicity, ConsistencyIsolation, and Durability requirements.

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.

Lock hierarchy

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.

SQL Server lock hierarchy

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

Identifying SQL Server locks - Demo table update

Now, we will check the sys.dm_tran_lock view.

select * from sys.dm_tran_locks  WHERE request_session_id=74

Identifying SQL Server locks - Checking the sys.dm_tran_lock view

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

Query to join the sys.dm_tran_locks view to other views

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

Script that populates some synthetic data on the test table

Lock Escalation

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

SQL Server creates the exclusive lock on the table

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

Query that creates a lock in the AdventureWorks HumanResources table

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.

Conclusions

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.

References

SQL Server Transaction Locking and Row Versioning Guide

SQL Server, Locks Object

Esat Erkeç
Latest posts by Esat Erkeç (see all)

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.