Sql-server – Deadlock killing update statement when collided with select

sql server

In my recent issues.. I have noticed a dead lock killing an update statement rather than a select command which was locked in it.

The SELECT's isolation level was at the database default, which is read committed snapshot isolation.

The SELECT is part of a transaction in our ETL.

Best Answer

Yes, SELECTs need locks, and they can win deadlocks if they're involved in a transaction that's done a lot of work leading up to that point.

To see it, go into a database with RCSI turned on, and create a couple of tables - only one of them will have data in it:

CREATE TABLE dbo.Table1 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
CREATE TABLE dbo.Table2 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
/* Only Table1 will have data: */
INSERT INTO dbo.Table1 (Things)
  SELECT 'Meaningless Stuff'
  FROM sys.all_columns
GO

Then start a transaction that does a lot of work on the table with data:

/* WINDOW #1: Start a transaction that does a lot of work */
BEGIN TRAN
UPDATE dbo.Table1 
  SET Things = 'Different Stuff';
GO

Now start a NEW TAB in SSMS so that you're in a different session - we're going to call this Window #2 - and run:

/* WINDOW #2: Start a transaction that does 
just a little work on an empty table: */
BEGIN TRAN
ALTER TABLE dbo.Table2 ADD NewCol VARCHAR(10);
GO

Both transactions in the two different windows are working on two different tables. So far, so good. Now continuing in Window #2, run an update on Window #1's table:

/* WINDOW #2: Everything's cool. Now try an update that gets blocked: */
UPDATE dbo.Table1 SET Things = 'Even more different stuff';

That update just hangs - he's blocked. Don't cancel him - leave him running. He's blocked by the Window #1 transaction. In RCSI, writers still block each other when they're trying to work on the same rows.

Switch back over to Window #1, and run:

/* WINDOW #1: Run a select */
SELECT * FROM dbo.Table2;

And count to 5. Within 5 seconds, SQL Server wakes up the deadlock monitor, who looks around, finds that these two queries are blocking each other, and...the select wins! The select returns data, and the update's window says:

Msg 1205, Level 13, State 56, Line 9
Transaction (Process ID 54) was deadlocked on lock resources 
with another process and has been chosen as the deadlock victim. 
Rerun the transaction.

Why? Because SQL Server by default chooses the victim by whichever transaction is the easiest to roll back. In our case, Window #1 had done a lot of work already, updating thousands of rows, whereas Window #2 had done just a tiny amount of work.

This is a contrived example in order to teach the lesson quickly - your scenario probably doesn't involve an ALTER TABLE command. Still, the same theory applies.

Related Question