Can select block update

 Yes, a SELECT can block an UPDATE because SELECT operations typically acquire Shared Locks while UPDATE operations require Exclusive Locks, and these lock types are incompatible. Under the default READ COMMITTED isolation level, the Shared Lock is usually released immediately after the row is read, so blocking is normally very short. However, if the SELECT is executed inside an explicit transaction, uses REPEATABLE READ, SERIALIZABLE, HOLDLOCK, or scans a large amount of data, Shared Locks can be retained for a longer duration and block UPDATE statements. In production systems, this often occurs when applications keep transactions open while performing business logic or user interaction. Modern systems frequently use Read Committed Snapshot Isolation (RCSI) to avoid this problem because readers access row versions from TempDB instead of acquiring Shared Locks on data rows. As a result, SELECT and UPDATE operations can proceed concurrently with minimal blocking. Understanding this behavior is critical when diagnosing blocking chains, lock contention, and concurrency issues in SQL Server environments.

Let’s see the demo

Creating a table a table and inserting few records

DROP TABLE IF EXISTS Employee; GO  CREATE TABLE Employee (     EmployeeID INT PRIMARY KEY,     EmployeeName VARCHAR(100),     Salary INT );  INSERT INTO Employee VALUES (1,'Bagesh',50000), (2,'Rajesh',60000);

On in the one session we are running the select statement in the transaction.

BEGIN TRAN; SELECT * FROM Employee WHERE EmployeeID = 1;  PRINT 'Transaction Still Open';

Transaction is still open. Now we are trying to update this record into another session.

Oohh!!!. Its updated. Here UPDATE doesn't block. Because under READ COMMITTED:

Read Row à Release Shared Lock à Transaction Still Open. The lock may already be released.

If we are using REPEATABLE READ Isolation in this case it will blow.

See below.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; GO  BEGIN TRAN; SELECT * FROM Employee WHERE EmployeeID = 1; PRINT 'Transaction Still Open';

This transaction we have not yet committed. Now updating the record on other session.

This is hung. Let’s run the below query to get the lock detail. Session id 78 is Select query running and Session id 79 is Update statement is running.

SELECT     request_session_id,     resource_type,     request_mode FROM sys.dm_tran_locks where request_session_id in (78,79) order by 1

Here we can see that session 78 hold S lock79 hold X lock.

Once we commit the transaction then it will be release the lock and record will update.

Now we run the above query and see.

All lock released.

A SELECT acquires Shared Locks and an UPDATE requires Exclusive Locks. Since Shared and Exclusive locks are incompatible, a SELECT can block an UPDATE when the Shared Lock is retained long enough, such as under REPEATABLE READ, SERIALIZABLE, HOLDLOCK, or long-running transactions. Under normal READ COMMITTED, blocking is usually brief because Shared Locks are released immediately after reading the row. RCSI and Snapshot Isolation largely eliminate this reader-writer blocking through row versioning.