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?