Why does WITH(NOLOCK) take shared locks and don't such locks block writes?

Question

select * from sys.dm_tran_locks
where resource_database_id=db_id() 

I'm using the above query to monitor the locks.

When SELECT query is running without the WITH(NOLOCK) keyword, then I can see the following locks granted: S, IS

When I use the WITH(NOLOCK) keyword, then I can see following locks granted: Sch-S

Since it involves locks (shared locks), it means that it doesn't block other readers. Also, since this keyword allows dirty reads then it means it can read rows that are being modified by a transaction that are not yet committed.

But my understanding was that shared lock on a row/table will prevent that row/table from being written. Does this concept also apply to Sch-S and any other locks (if any) that are taken by the with(nolock) keyword?

Answer 1

The Sch-S lock is not a shared lock. It is a schema stability lock. It prohibits somebody trying to run an ALTER TABLE command while your dirty read query is executing.

When a schema change is performed (like ALTER TABLE), SQL Server acquires schema-modification lock (Sch-M). It is the most restrictive lock of all and not compatible with any other type of lock.

When you attempt a dirty read, SQL Server tries to acquire a schema stability lock, which is only blocked by schema modification locks.