Shulou(Shulou.com)05/31 Report--

The purpose of this article is to share with you about the role of intention lock in sql server. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is the meaning of intention lock in sql server

The database engine uses intentional locks to protect the underlying resources of the lock hierarchy to prevent other transactions from harming the resources it locks and to improve the performance of lock conflict detection. For example, when reading a page in a table, the transaction requests a shared intention lock at the table level before requesting a page sharing lock (S lock). This prevents other transactions from subsequently acquiring an exclusive lock (X lock) on the table and modifying the entire table. Intentional locks can improve performance because the database engine checks intentional locks only at the table level to determine whether a transaction can safely acquire a lock on the table, without checking the locks on each row or page in the table to determine whether the transaction can lock the entire table.

How to understand the meaning of the above sentence? Let's take a practical example to illustrate.

If you have a Student that contains 1000 pieces of data, the test data is as follows:

Create table Student

(

Id int

Name char (30)

Constraint pk_id primary key (id)

)

-3. Insert 1000 records

SET NOCOUNT ON

GO

DECLARE @ I int

SET @ I = 1

WHILE @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.