Apply for locks and release order when SQL Server queries using keys

Source: Internet
Author: User
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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.