SQL Server Tour--14th station in-depth discussion on lock mechanism

Original: SQL Server Tour--14th station in-depth discussion on lock mechanism

I just made a pile of tables to let everyone get a preliminary understanding of the seizure of the lock, but in the real world is not so simple things, at least my watch does not have an index right,,, or

There is my watch is bound to have a lot of connections, 10:1 of the reading and writing, many yards may encounter similar marvellous of the deadlock, stuck, can't read, plug into the immortal things such as the cause of sex

Can be lowered, this article we discuss together.

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 TABLEdbo. person2 CREATE TABLEPerson (IDINT IDENTITY, NAMECHAR(4000)DEFAULT 'AAAAA')3 --Insert 6 data, just 3 data pages4 INSERT  intoDbo. PersonDEFAULT 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 TRAN 2 UPDATE 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 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 SELECTl.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_mode5  fromSys.dm_tran_locks asL6  Left JOINSys.partitions asP7  onl.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 -- build an index on the ID column 2 CREATE INDEX  on dbo. 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)2DBCC IND (Ctrip,person,-  1)

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

BEGIN TRAN UPDATE 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 from being too large, causing lock This record when it is too expensive to lock up 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 Tour--14th station in-depth discussion on lock mechanism