UPDATE堆表-RID上的死锁 [英] UPDATE heap table - Deadlock on RID
问题描述
我正在设置一个测试案例，以证明某种僵局情况，并且需要对正在发生的事情有一些了解。
我有一个堆表，方便地称为HeapTable。该表由2个交易类似地更新。
I'm setting up a test case to prove a certain deadlock scenario and require some insight on what is going on. I have a heap table, conventiently called HeapTable. This table is updated by 2 transactions simulateously.
事务1：
BEGIN TRAN
UPDATE HeapTable
SET FirstName = 'Dylan'
WHERE FirstName = 'Ovidiu';
WAITFOR DELAY '00:00:15';
UPDATE HeapTable
SET FirstName = 'Bob'
WHERE FirstName = 'Thierry';
ROLLBACK TRANSACTION
交易2：
BEGIN TRAN
UPDATE HeapTable
SET FirstName = 'Pierre'
WHERE FirstName = 'Michael';
ROLLBACK TRAN
我先解除交易1，然后紧接着进行交易2.如预期的那样，事务1将要求一些互斥锁以及一些意图互斥锁。事务2将进入并请求在同一RID上进行更新锁定：
I fire off transaction 1 first, closely followed by transaction 2. As expected transaction 1 will claim some exclusive locks, together with some intent exclusive ones. Transaction 2 will come in and request an Update lock on the same RID:
spid dbid ObjId IndId Type Resource Mode Status
55 5 711673583 0 RID 1:24336:10 X GRANT
57 5 711673583 0 RID 1:24336:10 U WAIT
我很惊讶地看到第二笔交易要求对同一RID进行更新锁定，因为我认为这指向单个记录&。这两个更新语句处理不同的数据。
I was kind of surprised to see the second transaction ask for an Update lock on the same RID, since I thought this pointed to a single record & both update statements handle different data. I was somehow expecting a conflict on page level instead.
当事务1的第二次更新在事务2中启动时，我将被视为死锁受害者，导致事务2的回滚&事务1的完成。
When the second update of transaction 1 kicks in transaction 2 will be seen as deadlock victim resulting in a rollback of transaction 2 & completion of transaction 1.
有人可以解释一下为什么第二个事务尽管更新了不同的记录但需要对同一RID进行更新锁定吗？ b
Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?
推荐答案
有人可以解释一下为什么第二笔交易需要更新同一条记录的更新锁吗？ p>
Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?
这可以改写为：当不存在索引时，Update语句如何获取需要更新的表上的锁。
This can be rephrased as, how Update statement acquires locks on table that needs to be updated,when no indexes are present..
SQL对页进行意图排他锁，然后在读取行之前尝试对页的行进行U锁，如果它与将要更新的值匹配，该锁将转换为X锁。
SQL takes an intent Exclusive lock on Page and then tries to take U lock on the rows of the page before reading it,if it matches with the value that is going to be updated,this lock will be converted to X lock..
此U锁策略是确保同一行上不会再使用其他不兼容的锁
This U lock strategy is to ensure ,no other incompatible lock will be taken on same row
请参见下面的链接，Kalen Delaney以获得有关同一内容的详细信息
Please see below link by Kalen Delaney for indepth details on same
http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update- locks.aspx
这篇关于UPDATE堆表-RID上的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
