3

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.

Transaction 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

Transaction 2:

BEGIN TRAN

UPDATE HeapTable
SET FirstName = 'Pierre'
WHERE FirstName = 'Michael';

ROLLBACK TRAN

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

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.

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.

Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?

Jens
  • 3,249
  • 2
  • 25
  • 42
  • Should this DBA question be on DBA.SE? – ajeh Oct 14 '16 at 14:05
  • @ajeh always found the line between the two to be somewhat blurry. I'll post it there if you think it'll serve more purpose there. – Jens Oct 14 '16 at 14:11
  • It's perfectly fine here. No worries. –  Oct 14 '16 at 14:18
  • 1
    Over on DBA.SE you get more attention from the professional DBAs, where's here more of the developers hang out. It's a matter of your personal preference ultimately, but I believe over there you could get more attention from those who look at these issues on a daily basis. – ajeh Oct 14 '16 at 15:32

1 Answers1

2

Can someone explain me why the second transaction would require an update lock on the same RID although updating a different record?

This can be rephrased as, how Update statement acquires locks on table that needs to be updated,when no indexes are present..

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..

This U lock strategy is to ensure ,no other incompatible lock will be taken on same row

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • So if I understand this correctly, the second transaction will try to get an Update lock on all records in a page, just trying to find the record it actually needs? – Jens Oct 14 '16 at 14:17
  • yes,When there are no indexes,it has to scan each record to find out – TheGameiswar Oct 14 '16 at 14:25
  • Thanks for clearing this up for me and deeping my knowledge about this. You're fantastic! – Jens Oct 14 '16 at 14:26