SQL Blocking

By /

SQL blocking occurs when one query holds a lock on any resources like a row, table or page and another
query needs to access which prevent second query from proceeding. This can lead to delays or even deadlocks.

Blocking i have seen happened on almost all organizations and its very common performance bottle where multiple transactions need to update or read data simultaneously.

Lets dig deeper into causes and strategies for managing it

Causes of SQL Blocking

  1. Long Transactions: Transactions that take a long time to execute can hold locks on resources, causing other transactions to wait.
  2. High Concurrency: When many transactions compete for the same data, it increases the chances of blocking.
  3. Lock Escalation: SQL Server may escalate row or page locks to table locks to conserve resources, which can cause more widespread blocking.
  4. Inappropriate Isolation Levels: Using high isolation levels (e.g., SERIALIZABLE) increases the likelihood of blocking.
  5. Exclusive Locks: Operations like updates or deletes use exclusive locks, which block other operations that need to access the same data.

Key Concepts Related to SQL Blocking

  • Locks: SQL Server uses locks to manage data integrity and concurrency.
    • Shared Locks: Used by read operations; other transactions can also read but not write.
    • Exclusive Locks: Used by write operations; prevent other transactions from reading or writing.
    • Update Locks: Used for modifications; blocks others from modifying but allows reading.
  • Isolation Levels: Determine how transactions interact.
    • Read Committed (default): Holds a lock during reads but releases it quickly.
    • Repeatable Read: Keeps shared locks until the transaction completes.
    • Serializable: The highest level, with the most blocking.
    • Read Uncommitted: Lowest level, avoids locking by reading uncommitted data but can lead to dirty reads.
  • Deadlocks: When two transactions block each other indefinitely, leading SQL Server to choose one transaction as a “victim” to terminate and release resources.

Diagnosing SQL Blocking

  1. SQL Server Management Studio (SSMS)
    • Activity Monitor: Provides a GUI to monitor currently blocked queries.
    • sp_who2: This system stored procedure shows active processes and can reveal blocking sessions by displaying the BlkBy column.
  2. Dynamic Management Views (DMVs)
    • sys.dm_exec_requests: Shows information about current requests, including blocking sessions.
    • sys.dm_exec_sessions: Displays active sessions and can be joined with other DMVs to identify blockers.
    • sys.dm_tran_locks: Shows information about all current locks.
  3. Extended Events or SQL Server Profiler: Helps capture blocking events over time, useful for diagnosing intermittent issues.

Strategies for Reducing SQL Blocking

  1. Optimize Query Performance: Shorter query duration reduces lock hold time. Use indexes, optimize joins, and limit unnecessary data retrieval.
  2. Reduce Transaction Scope: Keep transactions as short as possible by only locking required resources and releasing locks promptly.
  3. Set Appropriate Isolation Levels: Lower isolation levels can reduce blocking but should be chosen based on acceptable data consistency risks.
  4. Use Row Versioning: In SQL Server, the READ COMMITTED SNAPSHOT isolation level helps reduce blocking by using row versioning instead of locks.
  5. Deadlock Monitoring and Prevention: Use deadlock priority settings (SET DEADLOCK_PRIORITY LOW) for non-essential processes, and regularly monitor for and handle deadlocks.
  6. Optimistic Concurrency: In some cases, optimistic concurrency, where data is only locked when it’s about to be updated, can reduce blocking for high-read, low-write systems.
  7. Lock Hints: Using hints like NOLOCK (for read uncommitted) can reduce blocking, but use with caution due to potential for dirty reads.

SQL Blocking Example and Resolution

sqlCopy code-- Transaction 1: Starts a transaction and holds an exclusive lock on the `Employees` table.
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'Finance';

-- Transaction 2: Another transaction that needs to read from the `Employees` table but is blocked.
SELECT *
FROM Employees
WHERE Department = 'Finance';

-- Solution: Commit Transaction 1 to release the lock.
COMMIT TRANSACTION;

In this example, until Transaction 1 completes, Transaction 2 will be blocked. A strategy to avoid such blocking could involve keeping transactions shorter and possibly using a lower isolation level if consistency needs permit.

Monitoring Blocking

  1. Blocking Alerts: Set up alerts for blocking events to be notified when significant blocking occurs.
  2. Regular Maintenance: Regular indexing and statistics updates help improve query performance, reducing blocking incidents.

By proactively monitoring and fine-tuning the above factors, you can mitigate SQL blocking and improve your database’s concurrency and performance.

Scroll to Top