Sql-server – UPDATE heap table -> Deadlocks on RID

deadlocksql server

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?

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.

Best Answer

Without an index on FirstName, SQL Server has to check every row to see if it qualifies for the UPDATE.

It takes an update U lock when reading each row to prevent a common deadlock scenario. It could take a shared S lock, but that would still be blocked by the exclusive X lock held by the first transaction.

Related Question