Begintranselectcont2fromtestklupwith (HOLDLOCK) wherenlskey1 enables transactions at the default transaction isolation level, and then uses the HOLDLOCK prompt in the query. HOLDLOCK retains the shared lock until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page no longer needs it. Then use the DMV view sys.
Begin tran select cont2 from testklup with (HOLDLOCK) where nlskey = 1 enable the transaction at the default transaction isolation level, and then use the HOLDLOCK prompt in the query. HOLDLOCK retains the shared lock until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page no longer needs it. Then use the DMV view sys.
Begin tran select cont2 from testklup with (HOLDLOCK) where nlskey = 1
Enable the transaction at the default transaction isolation level, and then use the HOLDLOCK prompt in the query. HOLDLOCK retains the shared lock until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page no longer needs it.
Then, use the DMV view sys. dm_tran_locks to view the lock hold. The query results are as follows:
From the query results, the following locks can be applied:
1) applying for the IS lock on the table object
2) applying for the IS lock on the non-clustered index PAGE
3) S lock applied for non-clustered index KEY
4) applied for the IS lock on the PAGE of the clustered Index
5) apply for the S lock on the KEY of the clustered Index
View the lock application and release sequence
We use SQL Profiler to track lock application and release events.
Note: You can also enable the 1200 trace mark before executing the statement and use the statement dbcc traceon (1200,-1) (thanks to CareySon)
The template selects the TSQL-LOCKS, tracks only the session spid of the statement that runs, and the event adds Lock: Acquired and Lock: Released. The query result is as follows: