SQL Server explores the locking mechanism in depth

A: What happens when select encounters a performance-poor update?

1. Use the original person table, insert 6 data, because it is 4000 bytes, so two data is a data page, such as:

1 DROP TABLE dbo. Person2 CREATE TABLE person (ID INT identity,name CHAR (4000) DEFAULT ' AAAAA ') 3--Inserts 6 data, just 3 data pages 4 INSERT INTO dbo. Person DEFAULT VALUES5 Go 6

2. In order to simulate the performance of the update operation, we open an explicit transaction to update the records of the id=4, and use profile to look at, such as:

1 BEGIN TRAN2 UPDATE dbo. Person SET name= ' bbbbb ' WHERE id=4

3. Then we open another session connection, read the id=6 record will be what???? Curious????

1 SELECT * from person WHERE id=6

Do you see from the above process that when you scan to the SLOT1 slot on page 89th, it's jammed ... I think you should know that update has just added the X lock to this record ... If you

Careful enough, you will also find that the condition of attaching a record to S lock is to append the S lock to each record in the data page of the number when the engine discovers that the data page of the record is attached with the IX lock.

Right... Well, since I can't see it on profile, there are other ways to determine what state the SELECT statement is in.

4. Use sys.dm_tran_locks to see the current status of each connection holding the lock.

1 SELECT  l.request_session_id,2         db_name (l.resource_database_id), object_name (p.object_id), 3         l.resource_description,l.request_type,4         L.request_status,request_mode 5 from sys.dm_tran_locks as L6 left JOIN Sys.partitions as P7 on l.resource_associated_entity_id=p.hobt_id

As you can see, there are currently 51 and 52nd sessions, number 51st is using the X lock on the 1:89:1 slot and no release, and 52nd is entering the 1:89:1 at this time, and you want to give the

rowID additional S lock, but you also know that s and X lock is repulsive, so very helpless to keep waiting.

Second: Using an index may help you escape

When you read the above Apple told, is not a little afraid??? If this happens in the production environment, do we die badly??? Then the next use of the index is not true

Can help us avoid a robbery??? Let me take a look at it below.

1. New Index

1--A index2 create INDEX Idx_person on dbo is built on the ID column. Person (ID)

2. Then we look at the distribution of the data page, you can see that the 78,89,90 is the table data page, number 93rd is the index data page.

1 DBCC TRACEON (2588,3604) 2 DBCC IND (CTRIP,PERSON,-1)

3. Hemp Egg, continue to perform the above slow update

BEGIN tranupdate dbo. Person SET name= ' bbbbb ' WHERE id=4

4. The exciting moment comes, because there is too little data, so I'm forcing the engine to execute the index I created, and see what happens?

Not even stuck??? Is there a strong curiosity now, dog dog ... Open profile now and see what's going on???

After reading this picture carefully, do you think it is very interesting??? The steps are as follows:

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

Second step: Because to go index, the index data page 93rd Plus is lock.

Step three: Find the 93rd Index data page of the target key, to this key plus s lock, someone may ask ... This key is not 6, why this key= (61005A25560E),

If you're too curious, I can tell you, young man, don't talk too much. Each row of index records has a hash value, which is hashed out by several fields in the index, and the advantage is

Prevent your index length is too large, resulting in lock this record is too expensive lock space .... If you still don't believe it, I'll give you a look at DBCC.

Fourth step: According to this key to jump directly to the record number 90th data page, fortunately, the update record is not in the 90th data page .... Just escaped. Then

After select successfully reads the read record, and finally releases the relevant is lock.

If you understand the above mentioned points, I think you have been introduced to the lock, if you feel a bit confused, it doesn't matter ... You've got the profile weapon, and you've tried to test yourself.

Inspection is good, after all, I am so, the evening to publish the last one, tomorrow night home ... So you can have a safe and smooth over the year.

SQL Server explores the locking mechanism in depth