Blocking

A SQL Server concurrency situation where one session holds a lock that prevents another session from accessing the same resource, causing the waiting session to be delayed until the lock is released.

• Normal database behavior: Blocking is a normal part of transactional databases. SQL Server uses locking to enforce transaction isolation and prevent dirty reads. It only becomes a problem when blocking chains are prolonged or involve many sessions.
• Blocking chains: A blocking chain occurs when one session blocks another, which in turn blocks additional sessions, potentially leading to widespread application slowdowns and timeouts.
• Detection: DBAs can identify blocking by using sys.dm_exec_requests (specifically the blocking_session_id column), sys.dm_os_waiting_tasks, Activity Monitor, or Extended Events configured with blocking threshold alerts.
• Common causes: Excessive blocking is often caused by long-running transactions, missing indexes that result in full table scans, inefficient application code, or inappropriate transaction isolation levels.
• Reducing blocking: READ COMMITTED SNAPSHOT ISOLATION (RCSI) minimizes read-write blocking by using the row version store, allowing readers and writers to operate without blocking each other in most scenarios.
• Relevant Idera tools: SQL Diagnostic Manager detects and alerts on blocking chains, capturing blocking session details and lock information to speed up troubleshooting.
• Related terms: Deadlock, Latch Wait, TempDB, Row Version Store, Isolation Level.