Disregarding any of the higher isolation levels and using the out-of-box read committed isolation level you’d expect an exclusive lock to block a share lock. Here’s a case, admittedly not that common, where blocking does not occur.
-- Create a demo table CREATE TABLE dbo.t (c int NOT NULL); INSERT INTO dbo.t VALUES (1); -- In one session run this BEGIN TRAN; UPDATE dbo.t SET c = c; -- Examine the locks. You'll see the RID X lock SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID -- In another session run this SELECT c FROM dbo.t -- The SELECT is not blocked
This is a contrived example. The column value is being updated to itself but this may happen, for example, in an ETL load where your transaction begins, the daily update value doesn’t change, an update trigger on the table fires that updates other tables. In the transaction time frame you rely on SQL Server’s locking mechanisms to prevent other users reading data while transactions involving it are taking place. As the example shows, for this data scenario your users won’t be blocked.
The real world cases aside, there’s also a SQL Server inconsistency. If the table definition is changed so that a clustered primary key is placed on the column then blocking does occur. You’ll see a granted exclusive key lock from the updating spid and a waiting share key lock from the selecting spid. So the behaviour is dependent on whether the table is a heap or a btree.
Realistically speaking there’s little chance of this being addressed. Nevertheless, the following CONNECT item has been raised:
UPDATE: Martin Smith kindly replied to the CONNECT item. He cited a couple of posts by Paul White that explain the behaviour described in this post:
Using fn_dblog for the two scenarios mentioned here show no log entries for the heap case compared to a delete and an insert operation for the btree case. This explains the blocking for the btree case.