Explain locking behavior in SQL Server

Question

Why is that, with default settings on Sql Server (so transaction isolation level = read committed), that this test:

CREATE TABLE test2 (
ID bigint,
name varchar(20)
)

then run this in one SSMS tab:

begin transaction SH
insert into test2(ID,name) values(1,'11') 
waitfor delay '00:00:30'
commit transaction SH

and this one simultaneously in another tab:

select * from test2

requires the 2nd select to wait for the first to complete before returning??

We also tried these for the 2nd query:

select * from test2 NOLOCK WHERE ID = 1

and tried inserting one ID in the first query and selecting a different ID in the second.

Is this the result of page locking? When running the 2 queries, i've also ran this:

select object_name(P.object_id) as TableName, resource_type, resource_description
from
sys.dm_tran_locks L join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

and gotten this result set:

test2 RID 1:12186:5
test2 RID 1:12186:5
test2 PAGE 1:12186
test2 PAGE 1:12186

Answer 1

requires the 2nd select to wait for the first to complete before returning??

read commited prevents dirty reads and by blocking you will get a consistent result, snapshot isolation gets around this but you will get slightly worse performance because now sql server will hold the old values for the duration of the transaction (better have your tempdb on a good drive)

BTW, try changing the query from

select * from test2

to

select * from test2 where id <> 1 

assuming you have more than 1 row in the table and it will be over a page, insert a couple of thousand rows

Answer 2

List traversal with node locking is done by 'crabbing':

• you have a lock current node
• you grab a lock next node
• you make the next node current
• you release the lock on the previous node (former current)

This techniques is common in all list traversal algorithms and is meant to keep stability while traversing: you are never making a 'leap' w/o having yourself anchored in a lock. It is often compared to the techniques used by rock climbers.

A statement like SELECT ... FROM table; is a scan over the entire table. As such, it can be compared with a list traversal, and the thread doing the table scan traversal will 'crabb' ver the rows just like one doing a list traversal will crabb over the nodes. Such list traversal is guaranteed that it will attempt to lock, eventually, every single node in the list, and a table scan will similarly attempt to lock, at one time or another, every single row in the table. So any conflicting lock held by another transaction on row will block the scan, 100% guaranteed. Everything else you observe (page locks, intent locks etc) is implementation details, irrelevant to the fundamental issue.

The proper solution to this problem is to optimize the queries that they don't scan tables end-to-end. Only after that is achieved you can turn your focus to eliminate whatever contention is left: deploy snapshot isolation based row-level versionning. In other words, enable read-committed snapshot on the database.