Can update block select

 Yes, an UPDATE can block a SELECT because UPDATE operations acquire Exclusive Locks on the rows being modified. Under locking-based isolation levels such as READ COMMITTED, a SELECT must obtain a Shared Lock to read the same row. Since Shared and Exclusive locks are incompatible, the SELECT waits until the UPDATE transaction commits or rolls back. This behavior prevents dirty reads and ensures data consistency. In production systems, long-running transactions are a common cause of reader blocking because Exclusive Locks remain active for the duration of the transaction. SQL Server's row-versioning-based isolation levels, such as Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation, largely eliminate this type of blocking by allowing readers to access committed row versions stored in TempDB instead of waiting for locks to be released. Understanding this behavior is essential when troubleshooting blocking chains, application slowness, and concurrency issues in high-volume OLTP systems.

Let’s see the demo

Updating the record

begin Tran UPDATE Employee SET Salary = 80000 WHERE EmployeeID = 1; --commit;

Transaction is not yet committed. SQL Server places Exclusive Lock (X) on the row.

Session 2:

Running select

SELECT * FROM Employee WHERE EmployeeID = 1;

Result: WAITING... It hold Shared (S).

Lock Type	Shared (S)	Exclusive (X)
Shared (S)	✔	❌
Exclusive (X)	❌	❌

S and X are incompatible. Result is waiting select is blocked.

With the help of below query we can see which session is blocked and why.

SELECT session_id,                  blocking_session_id,                  wait_type,                  wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

Isolation Level Behavior

Isolation Level	Can UPDATE Block SELECT?
READ UNCOMMITTED	No
READ COMMITTED	Yes
REPEATABLE READ	Yes
SERIALIZABLE	Yes
SNAPSHOT	No
RCSI	No

 

When an UPDATE modifies a row, SQL Server acquires an Exclusive Lock (X) on that row. If a SELECT needs to read the same row under normal locking-based isolation levels (such as READ COMMITTED), it must acquire a Shared Lock (S). Since Shared and Exclusive locks are incompatible, the SELECT waits until the UPDATE transaction commits or rolls back.