Learn about update lock (U) and exclusive lock (X)

have not been seriously understood UPDATE operation of the lock, recently in MSDN Forums See a problem, ask the heap table update deadlock problem, the problem is very simple, there are similar tables and data:

CREATE TABLE DBO.TB (     C1 int,     C2 char (Ten),     C3 varchar (10)); Godeclare @id int; SET @id = 0; While @id <5BEGIN;     SET @id = @id + 1;     INSERT DBO.TB VALUES (@id, ' B ' + right (10000 + @id, 4), ' C ' + right (100000 + @id, 4)); END;

In query one, perform the update operation:

BEGIN tranupdate dbo.tb SET c2 = ' xx ' WHERE c1 = 2; WAITFOR DELAY ' 00:00:30 '; UPDATE dbo.tb SET c2 = ' xx ' WHERE c1 = 5; ROLLBACK;

Immediately after the query execution begins, perform the following actions in query two

BEGIN tranupdate dbo.tb SET c2 = ' xx ' WHERE c1 = 1; ROLLBACK;

Why deadlock occurs if the condition changes to C1 = 4 does not deadlock.

The first thing to think about is simple, the performance of deadlocks is to form a cyclic wait (for two queries, you can simply think of each other waiting for each other to lock the release of resources).

For this example, the first query updates two times, updates and locks a record, and then waits for the second update, but the second query updates only one record, which either conflicts with the first query, fails to get the lock, waits for the query to complete, does not lock anything at this time, or gets the lock to complete the update. It seems that there should be no deadlock, and the deadlock will be caused by other causes.

It was a simple test on your own computer, and it did not seem to be a deadlock.

But later through the profile tracking update operation under the lock situation only to find that their analysis is wrong. The main reason is that there is no correct understanding of how the update operation is using locks.

on the online help " Lock Mode " a description of the updated U (update lock) and X (exclusive lock)

http://msdn.microsoft.com/zh-cn/library/ms175519 (v=sql.105). aspx

However, it is quite vague, the inside also mentions the S lock, I always thought is the query data process used in the S lock (also SELECT the same), to find the record to meet the condition with U lock, and then converted to x lock do update.

Profile (Profiler) trace results Let me know this is a wrong understanding, create a new trace in profile, select the locks in the Lock:acquired(locking),Lock:acquired(Release lock)To solve two events, set the SPID in the filter to track only the query window corresponding to the test (you can performPRINT @ @SPID ), and then execute an UPDATE statement, such asUPDATE dbo. TB SET C2 = ' xx ' WHERE C1 = 3

As can be seen in profile, there is a U lock for each record, the U Lock is released immediately for records that do not meet the criteria, and for records that satisfy the condition, the final conversion is to X lock. As shown in.

Notice that the S lock does not appear in this trace result.

Also learned to do some tests:

2. By increasing the number of records to do the update test, you will find that the data scanning involved in the record has a U lock, not limited to the page where the record is updated. This illustrates from another angle the big table in scan scary.

4. When using the index scan, you will also find that the index resource of the scan has a U lock, if the update does not involve the index changes, that only the corresponding records have U-X lock, the index U lock will be released, if the index is affected, then the index U lock will go x lock.

6. Delete operation is similar to update operation

7. UseUPDATE aSET c2 = ' xx ' from dbo. TB as a with(NOLOCK) WHERE c1 = 3 The lock case is the same, and does not add U or X lock because of the NOLOCK hint

Finally, look back at the deadlock problem in the example:

• For query one, the first update scans all records in the table sequentially, for each record, plus u lock, determine if the update condition is met, if compliant, convert to X lock, or release U lock if not eligible. When the first update is complete, the query locks a record (because the transaction is not completed, so the lock remains), and then waits for the second update

• For query two, each record in the table is scanned sequentially (as in the previous update), and if its updated record is scanned before a record of the update is queried, the record will also become an X lock; When you continue and proceed to the X-lock record of the query one of the 0 points, you conflict with the X, you cannot continue, this time query two wait

• Query one of the second update to start execution, scan each record sequentially, there is no conflict within the same transaction, so it does not conflict with its own previously locked records, but to query two locked records, it can not obtain a U lock, it needs to wait for query two release resources. At this point, we wait for each other to meet the deadlock conditions.

• If the query two needs to update the record after the first update record of query one, then there will be no deadlock, because the query two in the scan to query the first updated record when the lock conflict is waiting, this time it does not have any record set and query one of the operation has conflicting locks. I don't have a deadlock when I test myself, that's the case.

  Note that the order in which the data is read is not necessarily the same as the order of storage, the order in which the disks are recorded is not necessarily the same as the order in which the inserts are recorded, which is why I did not test the deadlock with the same condition (in my environment, the order in which it was read is different from the order of insert)

when updating, the order in which records are read can be traced through the profile Lock: acquired ( locking) event, when a large amount of data is involved, there will be concurrent reads if the server supports it. This is also a factor to consider when analyzing deadlocks

This article explains To explain the update lock (U) and exclusive lock (X) knowledge, more relevant content, please follow the PHP Chinese web.

Related recommendations:

SQL Server 2008 handles implicit data type conversions in execution plan enhancements

How to make an infinite hierarchical parent-child relationship query in MySQL

How SQL Server FileStream with progress is accessed