Mehedi's Earth

In the world of SQL Server, ensuring data consistency, and concurrency is critical — but it often comes with challenges like locking, blocking and deadlocking. These negatively impact on performance. As databases grow in size and usage, understanding how SQL Server handles concurrent data access becomes essential for any DBA.

In this first part of the series, we will dive deep into the core concepts of locks and blocks, explore how they work, and examine why they occur. Through real-world examples and practical scripts, this article aims to equip you with the knowledge to identify, analyze, and handle common blocking scenarios. Mastering these fundamentals will prepare you for the more complex topic of deadlocks, which we will cover in Part 2.

Locking, Blocking and Deadlocking

Locking

Locking is a mechanism SQL Server uses to protect data integrity when multiple users try to access data simultaneously. When a query reads or modifies data, it places a lock on the resource (row/page/table/database) to prevent others from making conflicting changes at the same time. 

Blocking

Blocking occurs when one query holds a lock on a resource, and another query is waiting for that lock to be released. It’s not an error—just a delay caused by one process waiting for another to finish.

Deadlocking

Deadlocking happens when two or more queries block each other in a circular loop. Each process holds a resource the other needs, and neither can proceed, resulting in a standoff. SQL Server detects this and kills one process (called the victim) to break the deadlock.

Reasons Behind Locking

The main reasons behind locking is to maintaining data integrity and consistency in a multi-user environment. This is rooted to the fundamental of a relational database i.e. ACID properties (Atomicity, Consistency, Isolation, Durability). 

Atomicity

To ensure atomicity (where all operations of a transaction are completed or none are applied) SQL Server uses exclusive locks on the affected resources. This guarantees that if the transaction needs to roll back, it can undo all changes safely. During this time, other transactions are blocked from accessing those same resources.

Consistency

Locks also help maintain consistency by ensuring the database transitions from one valid state to another after a transaction completes. This means any intermediate or partial changes are hidden from other transactions, which can cause blocking on the involved objects until the transaction is done.

Isolation

In SQL Server, the isolation is a key reason for blocking which is enforced through locks. In a multi-user environment where multiple transactions run concurrently, locks ensure that one transaction's intermediate changes do not interfere with others. This maintains data consistency. For instance, if one session is updating a row and another session is trying to delete the same row will be blocked, as these actions conflict and cannot occur simultaneously.

Durability

While durability itself does not directly cause blocking, it can extend its duration. Durability ensures that all changes made by a committed transaction are permanently recorded in the transaction log. Because this requires writing both the before and after images of data to disk, it can increase transaction time, which in turn prolongs any blocking that occurs.

Effects of Concurrency

Let say a database has no concurrency control. In that case if an user is modifying data and another user is trying to read to update it then there will be following side effects:

• Lost updates - When two or more transactions select and update the same row concurrently based on the value initially selected and none is aware of other then the last update overwrites updates made by the other transactions, which results in lost data.
• Dirty read (Uncommitted dependency) - When a second transaction reads a row that is being updated by first transaction. The second transaction is reading data that is not committed yet and might be changed by the transaction updating the row.
• Nonrepeatable Read (Inconsistent analysis) - When a second transaction accesses the same row several times and reads different data each time. It is similar to dirty read in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term nonrepeatable read.
• A phantom read is a situation that occurs when two identical queries are executed and the set of rows returned by the second query is different.
• Missing and double reads caused by row updates
  • Missing an updated row or seeing an updated row multiple times - Transactions that are running at the READ UNCOMMITTED level (or NOLOCK hint) do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. Similarly, the row might not be read at all if the key change moved the row to a position in the index that you had already read. To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning.
  • Missing one or more rows that were not the target of update - When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. This does not occur when you are using the READ COMMITTED isolation level.

Lock Granularity

SQL Server maintain concurrency by implementing following locking granularities:

• Row (RID) - The lowest level of locking which is placed on a singe row of a table.
• Key (KEY) - If RID lock is maintained on a index then it is called the KEY lock. 
• Page (PAG) - A page-level lock (PAG lock) applies to a single page within a table or index. When a query needs multiple rows from the same page, SQL Server can maintain data consistency either acquiring RID/KEY locks on each individual row, or by locking the entire page with a PAG lock. The lock manager evaluates the cost of holding multiple RID/KEY locks, and if the overhead is high, it will choose to take a single PAG lock instead.
• Extent (EXT) - EXT lock is placed on an extent (group of 8 contiguous pages) for maintaining extent's data integrity. For example, during ALTER INDEX REBUILD command EXT lock may be used.
• Table (TAB) - This lock is placed on a table level which is the highest level of locking on a table to reserves access to the full table and all its indexes. If the cost of acquiring locks at the row level or the page level is higher, then the lock manager acquires a table-level lock for a query.
• Heap or B-tree (HoBT) - HoBT lock is similar to table lock but usually used on partition.
• File (FIL) - A FIL lock is a file-level lock in SQL Server that protects access to the file header page of a database file. It ensures that critical metadata about the file’s structure and allocation is read or modified safely without conflicting operations.
• Application (APP) - It is a logical lock in SQL Server that lets applications coordinate access to resources that SQL Server itself does not directly manage. It is often used to serialize custom business logic across sessions.
• MetaData (MDT) - This lock protects changes to database metadata, such as schema modifications or index creation, ensuring consistency during structural changes. 
• Allocation Unit (AU) - An Allocation Unit (AU) lock safeguards specific allocation units (part of a table or index’s storage) during operations like page allocations, preventing conflicts in space management.
• Database (DB) - A database level lock is maintained when an user access a database. It protects dropping/restoring of database while others are using it.

Lock Escalation

When executing a query, SQL Server acquires the necessary locks on referenced objects and tracks their count during execution. If the number of row or page locks exceeds a threshold, the lock manager escalates them to a table-level lock, releasing the lower-level locks. This dynamic escalation reduces locking overhead and improves efficiency.

Lock Modes

• Shared lock (S) - It is used for read-only queries like SELECT which allows concurrent reads. However, it blocks data modifications/deletion to maintain integrity.
• An Exclusive (X) - This lock grants sole rights to modify a resource. INSERT, UPDATE, and DELETE commands use this lock. It blocks other transactions from accessing the resource during modification. This ensures other transactions see only committed values and also enables safe rollback if needed.
• An Update (U) - This lock is like a Shared (S) lock with intent to modify the data. Only one (U) lock is allowed at a time to ensure integrity, though concurrent (S) locks are permitted. It is used in UPDATE operations, which first read (place U lock) and then modify data place X lock.
• Intent exclusive (IX)/Intent shared (IS)/Intent update (IU) - If there is Intent exclusive (IX)/Intent shared (IS)/Intent update (IU) maintained on an object it indicates that corresponding (X), (S) or (U) lock is placed in lower level. For example, if any (S), (U) or (X) lock is hold on RID/KEY then there is also corresponding (IS), (IU) or (IX) lock on page.
• Shared with intent exclusive (SIX) - If a transaction acquires a SIX lock on the table, it will place intent exclusive lock (IX) on the modified pages and exclusive lock (X) on the modified rows. Only one (SIX) lock can be acquired on a table at a time and it will prevent other from updating. However, it will allow reading at the lower hierarchy resources they can acquire the intent shared (IS) lock on the table also. 
• Shared with intent update (SIU) - This lock is acquired when a transaction is using an update query with the PAGELOCK hint. After the transaction acquires an SIU lock on the table, the query with the PAGELOCK hint will acquire the shared (S) lock while the update query will acquire intent update (IU) lock. 
• Update with intent exclusive (UIX) - If update lock (U) and intent exclusive (IX) locks are acquired at lower hierarchy resources in the table simultaneously, the update with intent exclusive lock will be acquired at the table level as a consequence.
• Schema Modification (Sch-M) -  A DDL statement that works on the schema of a table, acquires an (Sch-M) lock on the table and prevents other transactions from accessing the table. 
• Schema Stability (Sch-S) - An (Sch-S) lock is acquired for database activities that depend on the schema but do not modify the schema, such as a query compilation. It prevents an (Sch-M) lock on the table, but it allows other locks to be granted on the table.
• Bulk Update (BU) Mode - It is related to bulk load operations. For example, bcp (bulk copy), the BULK INSERT statement, and inserts from the OPENROWSET using the BULK option.
• Key-Range Mode - This is applicable only while the isolation level is set to Serializable. The Key-Range locks are applied to a series, or range, of key values that will be used repeatedly while the transaction is open. Locking a range during a serializable transaction ensures that other rows are not inserted within the range, possibly changing result sets within the transaction.

Isolation Level

Lock modes protect a transaction’s data consistency, but the isolation level determines how they behave. For example, by default, an (S) lock is released right after reading, but changing the isolation level can hold it longer to meet a specific application needs. There are 6 isolation levels.

 Table-1: Isolation Level

Concurrency side effects Vs Isolation Levels

  Table-2: Concurrency side effects Vs Isolation Levels

Locking Hierarchy

SQL Server uses locking hierarchy during reading or modifications. It starts with the database at the highest hierarchy level and down via table and page to the row at the lowest level. For example, for SELECT statement, (S) lock will be imposed on the database level, (IS) will be imposed on the table and page level, and a (S) lock on the row itself.  In case of INSERT/UPDATE/DELETE, a (S) lock will be placed on the database level, an (IX)/(IU) lock will be imposed on the table and on the page level, and an (X)/(U) on the row level. SQL Server prevents race condition by acquiring locks from top to the bottom.

Lock Compatibility

Lock compatibility determines if multiple transactions can lock the same resource simultaneously. A new lock is granted only if its mode is compatible with the existing lock; otherwise, the request waits or times out. Exclusive (X) locks block all other locks, while shared (S) locks allow other shared or update (U) locks but block exclusive locks. Below table shows the compatibility of different lock modes.

 Table-3: Lock compatibility 

Effect of Indexes on Locking

Indexes affect the locking behavior on a table.

• Table with no indexes - The lock granularities are RID, PAG (on the page containing the RID), and TAB.
• Nonclustered Index - Because the leaf pages of the nonclustered index are separate from the data pages of the table, the resources associated with the nonclustered index are also protected from corruption. SQL Server automatically ensures this. It also introduces an additional locking overhead on the table.
• Clustered Index - For a clustered index the leaf pages of the index and the data pages of the table are the same. Unlike nonclustered index, it has no additional pages (leaf pages) locking overhead.

Getting Lock Info

DMV sys.dm_tran_locks displays lock related information. For example, below query will produce output like figure-2.

DROP TABLE IF EXISTS dbo.LockTest1;
CREATE TABLE dbo.LockTest1 (C1 INT, C2 DATETIME);
GO
GO
INSERT INTO dbo.LockTest1(C1,C2)
VALUES (1, GETDATE());
GO

BEGIN TRAN

UPDATE dbo.LockTest1
SET C2 = GETDATE()
WHERE C1 = 1 ;


SELECT request_session_id,
DB_NAME(resource_database_id) as DBName,
CASE WHEN resource_type = N'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
ELSE NULL END as ObjectName,
resource_associated_entity_id,
resource_type,
resource_description,
request_mode,
request_status
FROM sys.dm_tran_locks 
WHERE request_session_id = @@SPID;
ROLLBACK

Figure-2: Output of sys.dm_tran_locks

Description of columns:

• request_session_id - Login session id
• DBName - Database name
• ObjectName - Table name otherwise null
• resource_associated_entity_id - ID of the object like database, RID, PAGE etc.
• resource_type - Type of resources like database, RID, PAGE etc.
• resource_description - For the RID type this column represents as FileID:PageID:Slot(row). In this case, it is 1:177992:0 i.e. a FileID of 1 is the primary data file, a PageID of 177992 is a page belonging to the dbo.LockTest1 table identified by the C1 column, and a Slot (row) of 0 represents the row position within the page. 
• request_mode - Lock modes.
• request_status - Requested status.

Getting Block Info

You can retrieve blocking information in several ways. Let's walk through couple of them.

SQL Query

Let's open three query windows in your SSMS and paste below queries in three windows (Table-4). First run window-1 query, then window-2 finally window-3. It will display information about waiting session id, blocking session id, resource description, wait type, wait duration, waiting and blocking query like figure-3.

DROP TABLE IF EXISTS dbo.BlockTest;
GO
CREATE TABLE dbo.BlockTest (C1 INT,
C2 INT,
C3 DATETIME);
INSERT INTO dbo.BlockTest
VALUES (11, 12, GETDATE()),
(21, 22, GETDATE());

BEGIN TRAN User1
UPDATE dbo.BlockTest
SET C3 = GETDATE();
--commit

BEGIN TRAN User2
SELECT C2
FROM dbo.BlockTest
WHERE C1 = 11;
COMMIT

SELECT dtl.request_session_id AS WaitingSessionID,
der.blocking_session_id AS BlockingSessionID,
dowt.resource_description,
der.wait_type,
dowt.wait_duration_ms,
DB_NAME(dtl.resource_database_id) AS DatabaseName,
dtl.resource_associated_entity_id AS WaitingAssociatedEntity,
dtl.resource_type AS WaitingResourceType,
dtl.request_type AS WaitingRequestType,
dest.[text] AS WaitingTSql,
dtlbl.request_type BlockingRequestType,
destbl.[text] AS BlockingTsql
FROM sys.dm_tran_locks AS dtl
JOIN sys.dm_os_waiting_tasks AS dowt
ON dtl.lock_owner_address = dowt.resource_address
JOIN sys.dm_exec_requests AS der
ON der.session_id = dtl.request_session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
LEFT JOIN sys.dm_exec_requests derbl
ON derbl.session_id = dowt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(derbl.sql_handle) AS destbl
LEFT JOIN sys.dm_tran_locks AS dtlbl
ON derbl.session_id = dtlbl.request_session_id

Table-4: Block info retrieval queries 

Figure-3: Output of blocking query

Activity Monitor

Right click on SQL Server from SSMS's Object Explorer and click on the Activity Monitor. Run Window-1 and Window-2 queries of Table-4 and from activity monitor's process section, you can observe the blocking info.

Figure-4: Activity Monitor output

Extended Events

You can use extended events to watch blocking information. Open a new session from extended event. Type block in the Event library and add the blocked process report. Save it with a meaningful name. Once done view the live data. For detail about extended event check my article.

Figure-5: Extended event configuration

Perfmon Counter

You can use Performance Monitor counters to capture blocking info. Monitor Average Wait time and lock wait time. For details about Performance Monitor counters configuration check my article.

Figure-6: Perfmon counters

Reducing Blocking

There are couple of ways to reduce blocking:

• Optimize the blocking and blocked queries.
  • Keep transactions short.
  • Reduce the number of steps/logic within a transaction.
  • Avoid any external operation within a transaction. Like sending mail or waiting for end user input.
• Create a covering index to for the blocked SELECT statements.
• Do not create clustered index on frequently updated columns. Updates to clustered index key columns require locks on the clustered index and all nonclustered indexes (since their row locator contains the clustered index key).
• Use the lowest isolation level. Consider using row versioning, one of the SNAPSHOT isolation levels, to help reduce contention.
• Encompass SQL Statements within TRY/CATCH blocks. Use IF @@TRANCOUNT > 0 ROLLBACK after executing SQL Statements. 
• Partition the contended data.

Final Words

Understanding SQL Server locks and blocks is essential for any DBA aiming to maintain high database performance and data integrity. Locks safeguard consistency in multi-user environments, but they can also introduce blocking when resources are contested. By knowing the different lock types, modes, isolation levels, and how compatibility works, DBAs can better diagnose performance issues and design strategies that balance concurrency with stability. Mastering these fundamentals sets the stage for tackling more complex scenarios like deadlocks, which we will explore in Part 2.

References

• Transaction locking and row versioning guide
• All about locking in SQL Server
• SQL Server 2017 Query Performance Tuning Troubleshoot and Optimize Query Performance, Fifth Edition By Grant Fritchey

Going Further

If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too! 

Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.

Let’s make your SQL Server to take your business Challenge!

For any queries, mail to mamehedi.hasan[at]gmail.com.