SQL Locks

October 11, 2026
| No Comments

Notes on locking:

  • Understanding and Resolving SQL Server Blocking Problems:
    • Blocking is a characteristic of any relational database management system (RDBMS) with lock-based concurrency. In SQL Server, blocking occurs when one session holds a lock on a specific resource, and another session attempts to acquire a conflicting lock on the same resource
    • The duration and transaction context of a query determine how long its locks are held and their effect on other queries
  • Sleeping Processes:
    • Sleeping connections can hold locks within the SQL Server engine, leading to blocking issues. This behaviour is often related to application design flaws where transactions are left open on sessions that have been reset for connection pooling
  • SQL Error 1222:
    • SQL error 1222, also known as “Lock request time out period exceeded,” occurs when a query waits longer than the lock timeout setting. This error indicates that another transaction held a lock on a required resource longer than the query could wait for it
  • Locking in SQL Server:
    • Locking is essential for successful SQL Server transaction processing and is what lets many users work on the same data at once. Locks are in-memory structures that manage transaction concurrency and ensure data integrity by forcing every SQL Server transaction to pass the ACID test
    • Lock modes include Exclusive (X), Shared (S), Update (U), Intent (I), Schema (Sch), and Bulk update (BU). Each lock type serves a specific purpose in managing access to resources
  • Transaction Locking and Row Versioning Guide:
    • This guide describes the locking and row versioning mechanisms used by the SQL Server Database Engine to make sure transaction integrity. It provides information on how applications can control transactions
  • SQL Server Locks Object:
    • The SQL Server: Locks object in Microsoft SQL Server provides information about locks on individual resource types. Locks are held on resources such as rows read or modified during a transaction to prevent concurrent use by different transactions
  • Use SQL Server Profiler:
    • SQL Server Profiler can be used to collect SQL traces and monitor locks. You can create a new trace, connect to the SQL Server, and configure options to include events related to locks and deadlocks
    • Make sure the following columns are checked: LoginName, SPID, StartTime, TextData, and TransactionID
  • Monitor with SQL Sentry:
    • We use SQL Sentry to monitor performance and spot blocking. Document the process of using SQL Sentry to identify and handle blocks
  • Dynamic Management Views (DMVs):
    • Use DMVs such as sys.dm_tran_locks to identify sleeping processes and locks within the SQL Server engine. This helps in understanding the current state of locks and addressing any blocking issues
  • Keep Transactions Short: – Design transactions to be as short as possible to reduce the likelihood of blocking and deadlocking. Commit or roll back changes promptly to release locks – Monitor Lock Waits and Timeouts: – Regularly monitor lock waits and timeouts to identify and resolve locking issues. This proactive approach helps prevent costly downtime and improves overall database efficiency
  • Analyse Workload Patterns: – Understand your workload patterns to determine the best lock escalation settings for your system. This helps keep lock contention down – Use Extended Events: – Extended Events can be used to monitor lock escalation metrics and identify locking-related contention. This shows you how locking behaves on the server
  • Read Committed Snapshot Isolation: – Consider using Read Committed Snapshot Isolation or Snapshot Isolation levels to reduce locking-related contention. These isolation levels help in minimising the impact of locks on concurrent transactions
  • Monitor Wait Types: – Wait types are key performance indicators in SQL Server and are helpful in determining the reasons for performance issues. Monitoring wait types can help in identifying blocking problems and taking appropriate actions
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()
ORDER BY request_session_id, resource_associated_entity_id

From my SQL Server DBA handbook.

Troubleshooting