SoFunction
Updated on 2025-04-07

sql server database lock tutorial and lock operation method

SQL Server Database Lock Tutorial

SQL Server's database lock is designed to ensure the concurrency and data consistency of the database. The locking mechanism can ensure that multiple transactions do not modify the same data at the same time, thereby avoiding data conflicts and inconsistencies. Understanding the locking mechanism of SQL Server is very important for developing efficient and highly concurrent database applications.

1. Basic concepts of locks

SQL Server lock is a mechanism that ensures synchronization of transactions in a database when accessing shared resources. It allows multiple transactions to be executed concurrently, but prevents them from accessing and modifying the same data row or page until the transaction completes.

SQL Server supports different levels of locks. According to the locked resource type and granularity, locks can be divided into the following categories:

  • Row-Level Lock: Lock a certain row of data.
  • Page-Level Lock: Locking the data page in the database, usually containing multiple data rows.
  • Table-Level Lock: Lock the entire table.
  • Intent Locks: Indicates that the transaction plan acquires the lock at a certain level, which is usually used for multi-level locking.

2. Type of lock

SQL Server provides a variety of types of locks, the most common ones are:

(1) Shared Lock (S - Shared Lock)

  • Used to read data, allowing other transactions to read the data, but not modifying the data.
  • Example: ExecuteSELECTWhen querying.

(2) Exclusive Lock (X - Exclusive Lock)

  • Used to modify data, allow transactions to modify the resource, and other transactions cannot access the resource (including reading and modification).
  • Example: ExecuteUPDATEorDELETEWhen operating.

(3) Update Lock (U - Update Lock)

  • A lock type used to avoid deadlocks, usually used when updating rows. It prevents other transactions from modifying resources, but allows other transactions to read.
  • Example: SQL Server will first add an update lock before updating a row of data.

(4) Intent Locks

  • Used to indicate that the transaction will acquire a lock on a higher level (row, page, table, etc.).
  • Intent Sharing Lock (IS): Indicates that the transaction plan adds a shared lock to the resource.
  • Intention Exclusive Lock (IX): Indicates that the transaction plan has an exclusive lock on the resource.

(5) Bulk Update Lock

  • Used for batch insertion or update operations.
  • It allows for the large range of data to be modified, and other transactions can be avoided.

3. Granularity

The granularity of a SQL Server lock refers to the range of locks. Depending on the amount of data of the operation, the lock granularity can range from row-level lock to table-level lock.

  • Row-level lock: Lock a single row in the database, usually a minimum granularity lock.
  • Page-level lock: Lock a page of data, usually containing 8KB of data.
  • Table-level lock: Lock the entire table, usually the maximum granularity lock.

4. Isolation level of lock

SQL Server provides four main levels of transaction isolation that determine how transactions access data in the database and how locks apply:

(1) Read not submitted (READ UNCOMMITTED)

Transactions can read uncommitted data (dirty read). It does not use a shared lock, allowing other transactions to modify data, which may cause inconsistent results to be read.

(2) Read ComMITTED

This is the default isolation level for SQL Server. Transactions can only read data that has been submitted. It uses a shared lock when reading data, preventing dirty data from being read, but allows other transactions to modify the data.

(3) Repeatable read (REPEATABLE READ)

At this isolation level, the data read by the transaction is immutable throughout the transaction. Even if other transactions commit modifications, the results of the current transaction cannot be affected. The shared lock will be held until the end of the transaction.

(4) Serialization (SERIALIZABLE)

At the highest level of isolation, transactions will completely exclusively access resources. It prevents other transactions from accessing or modifying data through exclusive locks, providing the highest level of data consistency, but seriously affects concurrency.

5. Deadlock

A deadlock occurs when two or more transactions are waiting for each other to release the lock, resulting in the inability to continue execution. SQL Server detects a deadlock and automatically selects a transaction rollback to resolve the deadlock.

  • Example of deadlock: Transaction A locks resource X, waiting for resource Y;
  • Transaction B Lock resource Y, wait for resource X;
  • Two transactions wait for each other, resulting in a deadlock.

6. How to view the current lock

You can use the views provided by SQL Server to view the status of the lock in the current database:

(1) sys.dm_tran_locks

This view shows all current lock information.

SELECT * FROM sys.dm_tran_locks;

(2) sys.dm_exec_requests

This view displays all requests currently executing and their lock information.

SELECT * FROM sys.dm_exec_requests;

(3) sp_who2

This stored procedure displays all active session information in the current SQL Server instance, including locks and process status.

EXEC sp_who2;

7. Lock management

(1) How to avoid deadlock

  • Reduce lock holding time: Try to shorten the transaction processing time and reduce the lock holding time.
  • Consistent locking order: Ensure that all transactions access tables or rows in the same order to avoid deadlocks due to different access orders.
  • Use transaction isolation levels reasonably: Choose the appropriate isolation level according to application needs to avoid unnecessary locks.

(2) Manual management lock

In some cases, useWITH (NOLOCK)To avoid locking reads:

SELECT * FROM Table name WITH (NOLOCK);

This will avoid the use of shared locks, allowing uncommitted data to be read, but may also read dirty data.

8. Optimization of locks

To improve performance, SQL Server provides some locking and adjustment preferences, such as:

  • Query optimization: Generate efficient query plans through query optimizer to reduce competition for locks.
  • Suitable index design: Make sure the table has the appropriate index to reduce the operation of scanning the entire table, thereby reducing the range of locks.
  • Use the appropriate transaction isolation level: Select the appropriate isolation level according to business needs to balance performance and data consistency.

Summarize

The lock mechanism of SQL Server is to ensure data consistency and concurrent execution of transactions. It manages resource access in the database through locks of different types and granularity. Rational selection of transaction isolation levels, management lock usage, avoiding deadlocks, optimizing queries, etc. can all help improve database performance and concurrency capabilities.

SQL commands related to SQL Server lock operation

1. UseWITH (NOLOCK)Tip to avoid locking

WITH (NOLOCK)Prompts can be used to avoid adding shared locks when reading data, thus avoiding blocking other transactions, but this may read uncommitted data (dirty read).

SELECT * FROM Table name WITH (NOLOCK);

Notice:useNOLOCKIt may cause dirty reading and therefore requires caution.

2. UseWITH (ROWLOCK)hint

WITH (ROWLOCK)Force SQL Server to use row-level locks instead of higher granular locks (for example, page-level locks or table-level locks). This is helpful to avoid locking too much data.

SELECT * FROM Table name WITH (ROWLOCK);

3. UseWITH (XLOCK)hint

WITH (XLOCK)SQL Server is forced to use exclusive locks, preventing other transactions from performing any operations on the locked data until the current transaction completes.

SELECT * FROM Table name WITH (XLOCK);

Application scenarios: Used to ensure that no other transaction can modify the data when reading the data.

4. UseWITH (UPDLOCK)hint

WITH (UPDLOCK)Used to request an update lock, it prevents other transactions from modifying the line, but still allows reading.

SELECT * FROM Table name WITH (UPDLOCK);

Application scenarios: Used to prevent other transactions from modifying the data when you are ready to update the data.

5. Check the current lock status

You can query the system view to view all lock information in the current database:

SELECT * FROM sys.dm_tran_locks;

This view shows all locks currently being held.

6. Check the locks and requests of the current transaction

With the following query, you can view all requests currently being executed and the lock information they hold:

SELECT session_id, request_id, lock_type, resource_type, resource_database_id, resource_associated_entity_id FROM sys.dm_exec_requests;

7. View detailed information about lock competition

If you want to know which queries are waiting for the lock, you can check the lock competition using the following command:

SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

illustrate：blocking_session_idNon-zero means that the current transaction is being blocked by other transactions.

8. Check deadlock information

If you suspect a deadlock appears, you can view the log of the deadlock diagram. The deadlock information can be obtained through the following query:

DBCC TRACEON(1222, -1);

This command outputs deadlock information to the SQL Server error log.

9. Manually release the lock

Usually, the lock will be automatically released after the transaction is completed, but if you want to force the lock of a transaction, you can use it.KILLCommand to terminate an executing session:

KILL <session_id>;

Notice:useKILLA transaction will be terminated and unfinished operations will be rolled back, so please use with caution.

10. Automatic rollback of deadlock

SQL Server automatically detects deadlocks and selects one of the transactions to rollback. If you want to see the deadlock rollback situation, you can get more information by viewing the error log.

DBCC TRACEON(1204, -1);

This will output the deadlock details to the SQL Server error log.

EXEC sp_readerrorlog; -- View the current error log

This is the end of this article about the lock tutorial and lock operation of SQL server database. For more related SQL server database lock content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!