SQL Server Tour--a preliminary understanding of the 13th station lock

Original: SQL Server Tour--a preliminary understanding of the 13th station lock

Finally this series is coming to an end, the new year is coming again, there is no mood to write blog ... As a developer, the lock mechanism is something that our programmers must master, long before

In learning the lock, are the textbook how to say, and then I how to recite, lack of a tool to let us see the real ... If so, learning a thing is easy to forget ...

Because it's all you recite ... In this article I will share a tool to help us learn about locks.

One: What are the locks?

Before learning to lock, you must know how many locks there are??? Usually as a code farmer we only need to know the following several locks can ...

1.S (Share) lock

For ease of understanding, we can think of it as a shared lock on a table, a data page, a record on a select.

2.X (Exclusive) lock

When we delete the data, we attach an X lock to the record, and we know that the X lock is not compatible with other locks. If other locks are encountered, they will be waiting, and we'll talk about them later.

3.U (Update) lock

As the name implies, when we update, in the process of looking for records, we will attach a U lock to the record one by one, if the target record is found, then the U lock will be converted to X lock ...

4.I (Intent) lock

This is the so-called intent lock, which is usually attached to the table and data page lock, the advantage is to prevent the other connections to modify the table structure.

Second: The Invincible SQL Server profile

This tool I think we all understand that its monitoring ability is really omnipotent ... The spasm of the lock is all in its grasp.

1. First I make a person table, the Name field is set to 4000 bytes, so a data page can hold 2 data, such as:

DROP TABLEdbo. personCREATE TABLEPerson (IDINT IDENTITY, NAMECHAR(4000)DEFAULT 'AAAAA')--Insert 6, generate 3 data pagesINSERT  intoDbo. PersonDEFAULT VALUESGo 6

2. Let's look at the distribution of data on the data page.

3. Then we open profile, select "Lock:acquired" and "lock:released" in Events of "event selection", and then run, such as:

Three: Using test data

1. First I execute a simple SELECT * FROM dbo. person to see the lock of the table/data page/record.

As you can see, the approximate steps for select are as follows:

The first step: Add the IS (intent shared lock) to the table (Object).

The second step: first to 1:78th data page Plus is lock, scan 78th data page, and then release is lock.

The third step: the same truth after scanning the data page.

Fourth Step: Finally release the is lock of the table, end the entire lock process.

After reading the above series of lock:acquired and lock:released words, did you find a problem, not to say to record (RID) plus s lock??? There is no addition,

It is because the engine entered the No. 78th data page when it was not found that there is IU lock or IX lock ... So... This is a combination of locks, the following will say.

2. Next use UPDATE dbo. Person SET name= ' bbbbb ' WHERE id=3 look at the whole process of update, at first glance, the records captured by profile are still more

, the following specific look at the picture:

The first step: Add IX locks to the table (Object),

Step two: Assign IU locks to the data page (1:78) data page. Then began to scan the 78th data page of the RID record, before entering the acquired, exit after the released, when swept

After you have completed all the RIDs of data page 78th, release the IU lock on page 78th and go to the next data page ...

Step three: We found that id=3 was on page 89th, when the engine swept the RID, we observed that the data page of number 89th was changed from IU Lock to IX lock, and the 1:89:0 (slot

0 of the record) from the U lock into an X-lock, the X-Lock, after the exclusion of all other locks, this time you can do update operation.

Fourth step: Continue to the 90th data page, steps similar, step two and step three.

Do not know careful you have found, before released object we release 1:89:0 x lock, and then release the 89th data page IX lock, this shows what??? Explain this

The update runs through this transaction, unlike a select operation, which releases a data page after sweeping a data page.

3. Finally, look at a DELETE from dbo. Person WHERE id=3 the action.

Probably swept the above figure, perhaps you feel and update operation is not bad, will scan each record in the data page and add U lock. When the target is found in the 1:89:0 slot

After the record is marked, then the U lock is converted to an X lock, which can be referred to update.

Well, the most simple DML operations are shown to you ... We know the production environment is not so simple, but you have this baby, and then more brain can be invincible ...

SQL Server Tour--a preliminary understanding of the 13th station lock