Explain the relevant knowledge of update lock (U) and exclusive lock (X)

I have never seriously understood the lock of the UPDATE operation. I recently saw one on the MSDN Forum Question, asking about the deadlock problem of heap table update. The question is very simple. There are tables and data similar to this:

##

CREATE TABLE dbo.tb(
     c1 int,
     c2 char(10),
     c3 varchar(10)
);
GO
DECLARE @id int;
SET @id = 0;
WHILE @id <5
BEGIN;
     SET @id = @id + 1;
     INSERT dbo.tb VALUES( @id, &#39;b&#39; + RIGHT(10000 + @id, 4), &#39;c&#39; + RIGHT(100000 + @id, 4) );
END;

Perform the update operation in query one:

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

After the execution of query one starts, Immediately perform the following operations in query two

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

Why does a deadlock occur? If the condition is changed to c1 = 4, there will be no deadlock.

I thought it was relatively simple at the beginning. The performance of deadlock is to form a loop of waiting (for two queries, it can simply be thought that they are waiting for each other. The release of the other party's locked resources).

For this example, the first query is updated twice. It will first update and lock a record, and then wait for the second update; but the second Each query will only update one record. It either conflicts with the first query and cannot obtain the lock, and needs to wait for the query to complete. At this time, it does not lock anything; or it can obtain the lock and complete the update. It seems that deadlock should not occur. Could the deadlock be caused by other reasons?

# I simply tested it on my computer and it seems that there is indeed no deadlock.

#But later I found out that my analysis was completely wrong by tracking the locking situation of the update operation through Profile. The main reason is that there is no correct understanding of how locks are used in update operations.

On the online help"Lock Mode" There are instructions on updating U (update lock) and X (exclusive lock) in

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

But it’s really vague. It also mentions S lock, which I always thought it was. The S lock is used in the process of querying data (the same as SELECT). After finding the records that meet the conditions, the U lock is used, and then converted to the X lock for updating.

Profile (profiler) tracking results let me know that this is a wrong understanding. Create a new tracking in Profile and select # in Locks. ##Lock:Acquired( Lock), Lock:Acquired (Release the lock) Solve the two events and set in the filter to only track the spid corresponding to the query window for testing (you can execute PRINT @@SPIDobtain), and then execute an update statement, such as UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 3

You can see in the Profile that U is added to each record The lock operation will immediately release the U lock for the records that do not meet the conditions; for the records that meet the conditions, it will eventually be converted into an X lock. As shown below.

##Note that in this tracking result Inside, there is no S lock.

In addition, I did some tests:

By increasing the amount of records for update testing, you will find that the records involved in data scanning have U locks, and are not limited to the page where the update record is located. This shows from another perspective that Scan is terrible in large watches.
When using index Scan, it will also be found through tracking that the index resource being scanned has a U lock. If the update does not involve index changes, it will only The corresponding record has a U to X lock, and the U lock of the index will be released; if it affects the index, the U lock of the index will be converted to an X lock.
Delete operation is similar to update operation

4. USEUPDATE aSET c2 = 'xx' FROM dbo.tb AS a WITH(NOLOCK) WHERE c1 = 3 The locking situation is the same, and U or X locks will not be added because of the NOLOCK prompt

Finally, let’s look back at the deadlock problem in the example:

• For query one, the first update scans all records in the table in sequence. For each record, a U lock is added to determine whether it meets the update conditions. If it meets the update conditions, it is converted into an X lock; if it does not meet the conditions, the U lock is released. When the first update is completed, query one locks a record (the lock is kept because the transaction is not completed), and then waits for the second update

• For query two, each record in the table is scanned in turn (same as the previous update). If the record it updates is scanned before the record updated by query one, then this record will also become an X lock; when continuing and When reaching the zero point of the X lock record of Query 1, U conflicts with X and cannot continue. At this time, Query 2 waits for Query 1 to release the lock. The second update starts to be executed and each record is scanned in turn. There will be no conflict within the same transaction, so it will not conflict with the record it has previously locked. However, when it comes to querying the second locked record, it cannot obtain the U lock, it needs to wait for query two to release the resource. At this time, mutual waiting is formed, which meets the deadlock condition

• #If the record that needs to be updated in query two is after the first updated record in query one, then it will not There will be a deadlock because query two will wait due to a lock conflict when it scans the first updated record of query one. At this time, it does not set a lock on any record that conflicts with the operation of query one. This was the case when I tested it myself without deadlocks.

• Note that the order mentioned here is the order in which data is read, which is not necessarily the same as the storage order. The order of records on the disk is not necessarily the same as the order of INSERT records. This This is also the reason why I did not test the deadlock under the same conditions (in my environment, the order of reading happens to be different from the order of INSERT)

When updating, record the order of reading, which can be tracked through ProfileLock:Acquired (Lock)In terms of events, when a large amount of data is involved, if the server supports it, there will be concurrent reads. This is also a factor to be considered when analyzing deadlock

This article explainsThe relevant knowledge of update lock (U) and exclusive lock (X), more For related content, please pay attention to php Chinese website.

Related recommendations:

SQL Server 2008 Enhancements in handling implicit data type conversions in execution plans

How to implement infinite-level parent-child relationship query in a single sentence in MySQL

How to access SQL Server FileStream with progress



The above is the detailed content of Explain the relevant knowledge of update lock (U) and exclusive lock (X). For more information, please follow other related articles on the PHP Chinese website!