Sql-server – NOLOCK or Read Uncommitted locking / latching behaviours

isolation-levellatchlockingsql server

I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken.

When using a SELECT with NOLOCK or in the Read Uncommitted Isolation level, is the only lock taken out a Schema Stability Lock, or do shared locks get taken on a rolling basis as the query works through the rows? (Obviously these locks would need to be dropped straight away)

What about latches? How are the pages in memory dealt with as I assume referencing an in-memory object that's in the middle of being modified isn't allowed?

Best Answer

Reads under READ UNCOMMITTED isolation only take Sch-S. No shared locks are acquired (there are a couple of rare exceptions).

Reads under locking READ COMMITTED take S locks that are usually released as soon as the query processor moves on to the next row (again, there are some exceptions).

Pages are always latched while being read or written to ensure physical consistency, generally a shared latch on read, and an exclusive latch on write. For details see Inside SQL Server Latches by Bob Ward via PASStv.

Locks are an implementation detail used by SQL Server to deliver the guarantees provided by the isolation level.

Related Question