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:
When I use the
WITH(NOLOCK) keyword, then I can see following locks granted:
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