SQL Server Q&A Topic 8: Locks, Blocking & Deadlocks

SQL Server Interview Questions & Answers

Topic 8: Locks, Blocking & Deadlocks

Questions 75–82 | Intermediate to Advanced

This section covers SQL Server locking, blocking, deadlocks, lock escalation, common lock modes and the risks of using NOLOCK as a shortcut for concurrency problems.

These concepts are especially important for production DBAs because an application can appear slow even when CPU, memory and storage are healthy if sessions are waiting for locks held by other transactions.

75. What is locking?

Answer:
Locking is a SQL Server concurrency-control mechanism used to protect data while multiple transactions access or modify database resources.

Locks help maintain transactional consistency by controlling whether concurrent sessions can read or modify the same resources at the same time.

SQL Server can acquire locks at different resource levels, including:

• Rows or keys
• Pages
• Tables
• Database objects
• Metadata and other internal resources

Common lock modes include:

• Shared (S)
• Update (U)
• Exclusive (X)
• Intent Shared (IS)
• Intent Exclusive (IX)
• Shared Intent Exclusive (SIX)
• Schema Stability (Sch-S)
• Schema Modification (Sch-M)

Interview Tip:
Locking itself is normal and necessary. The performance problem normally occurs when incompatible locks cause sessions to wait for an excessive amount of time.

76. What is blocking?

Answer:
Blocking occurs when one session holds a lock on a resource and another session requests an incompatible lock on that same resource.

The requesting session must wait until the blocking transaction releases the required resource.

A simplified example is:

Session 51
BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 1000
WHERE AccountID = 101;

-- Transaction remains open

Meanwhile:

Session 62

UPDATE dbo.Accounts
SET Balance = Balance + 500
WHERE AccountID = 101;

Session 62 may need to wait because Session 51 is still holding an incompatible lock.

Blocking can become problematic when:

• Transactions remain open for too long
• Queries process unnecessarily large numbers of rows
• Indexes are inefficient or missing
• Applications leave transactions open
• Large batch operations run during peak periods
• Many sessions build up behind a single head blocker

DBA Tip:
When troubleshooting blocking, identify the head blocker rather than investigating only the sessions waiting at the end of the blocking chain.

77. What is a deadlock?

Answer:
A deadlock occurs when two or more sessions form a circular dependency in which each session is waiting for a resource held by another session in the same cycle.

For example:

Session A
Locks Resource 1
       ↓
Needs Resource 2
       ↓
Resource 2 held by Session B


Session B
Locks Resource 2
       ↓
Needs Resource 1
       ↓
Resource 1 held by Session A

Neither session can continue normally.

SQL Server detects the deadlock and selects one transaction as the deadlock victim.

The selected transaction is rolled back, allowing the other transaction to continue.

The application receiving the victim error typically sees:

Transaction (Process ID ...) was deadlocked on lock
resources with another process and has been chosen
as the deadlock victim.

Rerun the transaction.

Interview Tip:
SQL Server resolves a deadlock automatically by choosing a victim, but the DBA or developer still needs to investigate and fix the underlying cause if deadlocks occur repeatedly.

78. Blocking vs deadlocking?

Answer:
Blocking and deadlocking both involve sessions waiting for resources, but they are fundamentally different situations.

Blocking

Session A
Holds Lock
   ↓
Session B
Waits
   ↓
Session A completes
   ↓
Session B continues

Blocking can resolve naturally when the blocking transaction finishes.

Deadlocking

Session A
Needs resource held by B
        ↕
Session B
Needs resource held by A

This creates a circular dependency. SQL Server must break the cycle by choosing a deadlock victim.

Easy Interview Answer:
Blocking: one session waits for another.
Deadlock: sessions wait on each other in a cycle, so SQL Server terminates one transaction to resolve it.

79. How do you troubleshoot deadlocks?

Answer:
The first step is to capture and analyze the deadlock information rather than guessing which query caused the problem.

A practical approach is:

1. Capture the deadlock graph.
2. Identify all sessions and queries involved.
3. Identify which resources each session owns and requests.
4. Determine which transaction SQL Server selected as the victim.
5. Review the order in which objects are accessed.
6. Check whether transactions remain open longer than necessary.
7. Review indexes and execution plans.
8. Determine whether excessive rows are being locked.
9. Review the isolation level and application transaction design.
10. Implement the smallest appropriate fix and retest.

SQL Server's system_health Extended Events session normally captures deadlock information and is a useful starting point.

Common solutions include:

• Accessing objects in a consistent order
• Keeping transactions short
• Creating appropriate indexes
• Reducing the number of rows processed
• Removing unnecessary user interaction from inside transactions
• Reviewing isolation strategy
• Adding application retry logic for deadlock victims where appropriate

Interview Tip:
Do not say "I would kill the blocking session" as your first deadlock solution. SQL Server already resolves the deadlock by selecting a victim. Your task is to determine why the circular dependency occurred.

80. What is lock escalation?

Answer:
Lock escalation is SQL Server's process of replacing many fine-grained locks with a larger-grained lock to reduce lock-management overhead.

For example, a transaction may initially hold many key or row-level locks.

SQL Server may attempt to escalate those locks to a table-level lock when lock-count and memory-related conditions justify escalation.

Many Fine-Grained Locks
        ↓
Lock Management Overhead Increases
        ↓
SQL Server Evaluates Escalation
        ↓
Possible Table-Level Lock

Lock escalation can reduce SQL Server's lock-management overhead, but a larger lock can also increase blocking for concurrent sessions.

Possible areas to investigate include:

• Large UPDATE or DELETE operations
• Batch size
• Index design
• Transaction duration
• Number of rows touched by the statement
• Partitioning and lock-escalation configuration where applicable

DBA Tip:
The commonly discussed threshold of approximately 5,000 locks is useful background knowledge, but lock escalation should not be treated as a simple guaranteed "5,000 rows = table lock" rule. SQL Server also considers lock memory and whether escalation can successfully occur.

81. What are Shared, Update and Exclusive locks?

Answer:
Shared, Update and Exclusive are important SQL Server lock modes used during read and data-modification operations.

Shared Lock (S)

Shared locks are commonly used when reading data under locking-based isolation.

Multiple sessions can generally hold compatible Shared locks on the same resource.

Update Lock (U)

An Update lock is used in certain update-processing scenarios before SQL Server converts the lock to an Exclusive lock.

One purpose of Update locks is to reduce particular deadlock patterns that could occur if multiple transactions first acquired Shared locks and then simultaneously attempted to convert them to Exclusive locks.

Exclusive Lock (X)

Exclusive locks are required when data is being modified.

They prevent incompatible access to the protected resource until the lock is released according to transaction and isolation semantics.

Simplified view:

Shared (S)
   ↓
Primarily Reading

Update (U)
   ↓
Potential Modification

Exclusive (X)
   ↓
Data Modification

Interview Tip:
A good answer should explain lock compatibility and purpose rather than simply expanding S, U and X.

82. Is NOLOCK a solution to blocking?

Answer:
No. NOLOCK should not be treated as a general solution to SQL Server blocking.

NOLOCK is effectively equivalent to reading under READ UNCOMMITTED isolation for the referenced table.

Example:

SELECT
    CustomerID,
    CustomerName,
    Balance
FROM dbo.Customers WITH (NOLOCK);

It can reduce some blocking associated with shared data locks, but it weakens read consistency.

Possible issues include:

• Dirty reads
• Reading data that may later be rolled back
• Missing rows
• Reading rows more than once
• Inconsistent query results

It also does not mean that the query takes absolutely no locks. For example, schema locks can still participate in blocking scenarios.

Better approaches depend on the root cause and may include:

• Optimizing slow queries
• Adding or improving indexes
• Keeping transactions short
• Processing large modifications in sensible batches
• Correcting application transaction handling
• Evaluating Read Committed Snapshot Isolation (RCSI)
• Reviewing the required transaction isolation level

Common Interview Trap:
Never describe NOLOCK as "faster reads with no downside." It changes consistency guarantees and can return data that would not be acceptable for many business workloads.

Useful DBA Script: Identify Blocking Sessions

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,
    DB_NAME(r.database_id) AS DatabaseName,
    SUBSTRING
    (
        st.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS RunningStatement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
    r.session_id <> @@SPID
    AND
    (
        r.blocking_session_id <> 0
        OR r.wait_type LIKE 'LCK_M_%'
    )
ORDER BY
    r.blocking_session_id,
    r.session_id;

Useful DBA Script: Check Current Locks

SELECT
    request_session_id AS SessionID,
    resource_type AS ResourceType,
    resource_database_id AS DatabaseID,
    resource_description AS ResourceDescription,
    request_mode AS LockMode,
    request_status AS LockStatus
FROM sys.dm_tran_locks
WHERE
    resource_database_id = DB_ID()
ORDER BY
    request_session_id,
    resource_type;

DBA Tip:
A lock list by itself may be large and difficult to interpret. Correlate lock information with active requests, wait types, transaction duration and blocking-session relationships.

Useful DBA Script: Find Open Transactions

A useful initial command when investigating a long-running transaction is:

DBCC OPENTRAN;

This can help identify the oldest active transaction in the current database.

SQL Server Blocking Troubleshooting Flow

Application Slowness
   ↓
Check Active Requests and Wait Types
   ↓
Look for LCK_M_* Waits
   ↓
Identify Blocking Session
   ↓
Trace Blocking Chain
   ↓
Identify Head Blocker
   ↓
Check Open Transaction
   ↓
Review Query and Execution Plan
   ↓
Review Indexing and Transaction Scope
   ↓
Resolve Root Cause
   ↓
Monitor for Recurrence

SQL Server Deadlock Troubleshooting Flow

Deadlock Reported
   ↓
Capture Deadlock Graph
   ↓
Identify Victim and Other Processes
   ↓
Identify Locked Resources
   ↓
Review Query Execution Plans
   ↓
Check Object Access Order
   ↓
Review Indexes and Transaction Scope
   ↓
Apply Targeted Fix
   ↓
Add Application Retry Logic if Appropriate
   ↓
Monitor for Recurrence

Interview Quick Recap

• Locking is a normal SQL Server mechanism for maintaining transactional consistency.
• Blocking occurs when one session waits for an incompatible lock held by another session.
• A deadlock creates a circular dependency between two or more sessions.
• SQL Server resolves a deadlock by selecting a victim and rolling back that transaction.
• Troubleshoot deadlocks using the deadlock graph rather than guessing.
• The system_health Extended Events session is a useful source of deadlock information.
• Lock escalation replaces many fine-grained locks with a larger-grained lock when appropriate.
• Shared locks are associated primarily with reads, Update locks help coordinate potential modifications, and Exclusive locks protect modifications.
• NOLOCK is not a general-purpose blocking solution.
• NOLOCK can return dirty or inconsistent results.

← Previous Topic
# Topic 7: Query Processing & Performance Tuning

Next Topic →

# Topic 9: Isolation & Concurrency

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning