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.
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
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?
I know how to fix this (e.g. with an index). I'm not looking for a fix, I'm actually looking for an explanation to why 2 Updates handling different records in a heap would want to lock the same RID. I am using read committed isolation. There are no nonclustered indexes on the table.