Locks in SQL Server

The lock is a mechanism associated with a table for restricting unauthorized access to the data. It is mainly used to solve the concurrency problem in transactions. We can apply a lock on row level, database level, table level, and page level. This article will discuss the lock mechanism and how to monitor them in SQL Server.

We know that multiple users can access databases at the same time. As a result, locking is essential for a successful transaction and protects data from being corrupted or invalidated when several users attempt to read, write, or update a database. Usually, the lock is an in-memory structure with owners, types, and the hash of the resource they are supposed to protect. As an in-memory structure, the size of a lock is 96 bytes.

It is better to understand that "A lock is designed to ensure data integrity and consistency while enabling concurrent data access, as it forces each transaction to pass the ACID test. When several users access a database to alter its data at the same time, it implements concurrency control."

The ACID test contains the following requirements for making the transaction successful:

• Atomicity: It ensures all statements or operations included in the transaction must be successful. Otherwise, operations are rolled back into their previous state.
• Consistency: It assures that the database must only construct a valid state when a transaction is successfully committed.
• Isolation: It guarantees that all transactions are independent of other transactions. It also ensures that statements are transparent to each other.
• Durability: It guarantees that the result of committed transactions persists in the database permanently, even in a case of system crashes or failure.

Where are locks placed in the database?

Now, we are going to know where locks are actually present in the database, i.e., on which resource they lock or do not lock. The below table shows the resources on which the SQL Server can place locks:

Lock Modes

Lock mode is used to prevent other people to reads or change the locked resource. It can be categorized into the following six types listed below:

• Exclusive Lock (X)
• Shared Lock (S)
• Update Lock (U)
• Intent Lock (I)
• Schema Lock (Sch)
• Bulk Update Lock (BU)

Exclusive locks

The exclusive locks are useful in DML operations like INSERT, UPDATE, or DELETE statements. This lock, when imposed on a transaction, prevents other persons from accessing the locked resources. It means that an exclusive lock can hold only one transaction on a resource at the same time. The user of this lock is known as a writer. This lock is imposed when the transaction wants to modify the page or row data. It can only be held by the page or row when there is no other shared or exclusive lock hold on the target.

Shared Locks

Once the shared locks are applied on the page or row, they will be reserved for reading-only purposes. It implies that no other transaction can alter the locked resource as long as the lock is active. As the name implies, several transactions can hold this lock on the same resource simultaneously. The user of this lock is known as a reader. In addition, this lock will also allow write operations, but no DDL changes will be permitted.

Update Locks

The update lock is the same as an exclusive lock, but it is designed to be more adaptable. A transaction that already holds a shared lock can be given an update lock. In such cases, the update lock can hold another shared lock on the target page or row. This lock can be changed to an exclusive lock whenever the transaction that has the update lock is going to alter the data. It is generally used when the server filters the records for making updation. In addition, it is the solution to the problem of cycle deadlock.

Intent Locks

The intent lock is a way for a transaction to inform other transactions about its intention to acquire a lock. This lock aims to prevent another transaction from getting a lock on the next object in the hierarchy, ensuring that data modifications are appropriately performed. It indicates that this lock is used to create a lock hierarchy. It's an important type of lock in the performance aspect. It can be divided into three types:

1. Intent shared (IS): If a page or row holds this lock, then the transaction intends to read resources in the lower hierarchy by obtaining shared locks (S) on those resources independently.
2. Intent exclusive (IX): If a page or row holds this lock, the transaction intends to change some lower hierarchy resources by obtaining exclusive (X) locks on those resources independently.
3. Intent update (IU): This lock can only be obtained at the page level, and it transforms to the intent exclusive lock when the update operation is completed.

Schema Locks

Schema lock is used in the SQL Server when an operation that depends on the schema of a table is executed. It can be divided into two types:

1. Schema modification (Sch-M): It is used when a DDL statement executes and prevents access to the locked object data while the object's structure is altered.
2. Schema stability (Sch-S): It is used when a schema-dependent query is compiled and executed as well as generates the execution plan.

Bulk Update Locks

This lock is used to copy bulk data into a table by specifying the TABLOCK hint. The user generally uses it when he wants to insert a large amount of data into the database.

Locking Hierarchy

The locking hierarchy was introduced by SQL Server, which is used while reading or modifying the data. The lock hierarchy begins with the database at the top of the hierarchy and descends through tables and pages to the row at the bottom. The below diagram explains it more clearly:

Lock Compatibility Matrix

After understanding the lock modes and lock hierarchy, let us see how the lock modes are translated to a lock hierarchy. There are mainly two locking levels available on which lock mode is applied: Row level and Table level.

We can apply the following three lock modes at the row level:

• Exclusive (X)
• Shared (S)
• Update (U)

The following table illustrates the compatibility of these modes:

Here ✗ indicates incompatibility and ✓ indicates compatibility.

We can apply the following five lock modes at the table level:

• Exclusive (X)
• Shared (S)
• Intent exclusive (IX)
• Intent shared (IS)
• Shared with intent exclusive (SIX)

The following table illustrates the compatibility of these modes:

Example

Let us understand how we use locking in SQL Server with an example. We will do this by first creating a demo table without including any indexes and try to update the table.

In the next step, we will create a transaction to update the records and then analyze the locked resources:

We will get the below output:

Now, we will execute the below command to check the sys.dm_tran_lock view:

It will display several data regarding active lock resources:

However, some of its data is not possible to understand clearly. Due to this reason, we will use the sys.dm_tran_locks view to other views. Here is the code script:

Executing the script will return the following output where we can see the locked resources and lock modes.