Different lock behaviour when using `IN`

Question

I am having difficulty understanding the lock behaviour difference in the following examples, which cause me to have to resolve a deadlock.

I use (updlock,holdlock) for the common scenario of "select first, update/delete later". It might be relevant that in this specific case what is going to happen "later" is delete.

First, let me set up a case where everything works fine. As a "control panel query", let's create a very simple table, add some rows, and also prepare a lock selection query based on ashman786's post:

/*
create table color(id int primary key,descr nvarchar(50))

truncate table color
insert color(id,descr) values (0,'red'),(1,'green'),(2,'blue')
select * from color
*/
SELECT  L.request_session_id AS SPID, 
        O.Name AS LockedObjectName, 
        L.resource_type+isnull(': '+L.resource_description,'') AS LockedResource, 
        L.request_mode AS LockType 
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

For test 1, copy-paste each the following in a new session, each modifies one pk value:

select @@SPID, @@trancount
--52 is my spid
begin tran
    select * from color with (updlock,holdlock) where id=1
    delete color where id=1
commit
rollback


select @@SPID, @@trancount
--56 is my spid
begin tran
    select * from color with (updlock,holdlock) where id=2
    delete color where id=2
commit
rollback

Running the first line shows the SPID, which we conveniently write down in the following line.

If we create & insert the color table, then run the begin tran and select on both tabs, then the lock query shows:

+------+------------------+---------------------+----------+
| SPID | LockedObjectName | LockedResource      | LockType |
+------+------------------+---------------------+----------+
| 52   | color            | PAGE: 1:704         | IU       |
| 52   | color            | KEY: (8194443284a0) | U        |
| 56   | color            | PAGE: 1:704         | IU       |
| 56   | color            | KEY: (61a06abd401c) | U        |
+------+------------------+---------------------+----------+

Seems fine. Now run the deletes on both sides without committing, and we get a similar result:

+------+------------------+---------------------+----------+
| SPID | LockedObjectName | LockedResource      | LockType |
+------+------------------+---------------------+----------+
| 52   | color            | PAGE: 1:704         | IX       |
| 52   | color            | KEY: (8194443284a0) | X        |
| 56   | color            | PAGE: 1:704         | IX       |
| 56   | color            | KEY: (61a06abd401c) | X        |
+------+------------------+---------------------+----------+

Following this, committing works. A dataflow variation of one session doing both select and delete also works fine.

For test 2, instead of using the pk value as literal, we will use a temp table:

select @@SPID, @@trancount
--60 is my spid
begin tran
    drop table if exists #t
    select * into #t from color with (updlock,holdlock) where id=1
    delete color where id in (select id from #t)
commit

select @@SPID, @@trancount
-- 54 is my spid
begin tran
    drop table if exists #t
    select * into #t from color with (updlock,holdlock) where id=2
    delete color where id in (select id from #t)
commit

Running begin tran and select into on both sessions results in exactly the same lock results as before: 2xU Key locks, 2xIU Page locks. However, as soon as one of the delete statements is run (@60), the picture changes:

+------+------------------+---------------------+----------+
| SPID | LockedObjectName | LockedResource      | LockType |
+------+------------------+---------------------+----------+
| 54   | color            | PAGE: 1:704         | IU       |
| 54   | color            | KEY: (61a06abd401c) | U        |
| 60   | color            | PAGE: 1:704         | IX       |
| 60   | color            | KEY: (8194443284a0) | X        |
| 60   | color            | KEY: (61a06abd401c) | U        |
+------+------------------+---------------------+----------+

Changing its own U/IU to X/IX is exactly what happened previously, but this time, 60 has also a U lock on the other session's key! Of course, when the delete of the other session happens, a deadlock is formed for this reason. This seems to happen regardless of holdlock by the way.

So...why this difference? Will I have to make do without the temp table in order to avoid deadlocks, or is there some workaround?

Answer 1

The execution plan for

delete color where id=2

is very simple

It just has a single "Clustered Index Delete" operator with an equality seek predicate on id to tell it the row to delete.

The locks taken out in this case are an IX lock on the table, an IX lock on the page containing the row, and finally an X lock on the key of the row to be deleted. No U locks are taken at all.

When using the temp table the execution plan looks as follows.

It reads each row from color in turn and acquires a U lock on it. If the semi join operator finds that there was a match for that row in the temp table the U lock is converted to an X lock and the row is deleted. Otherwise the U lock is released.

If the execution plan was driven by the temp table instead then it could avoid reading and locking unneeded rows in color.

One way of doing this would be to write the query as

DELETE color
FROM   color WITH (forceseek)
WHERE  id IN (SELECT #t.id
              FROM   #t) 

The execution plan now reads the one row in #t - checks whether it exists in color (taking a U lock on just that row) and then deletes it.

As there is no constraint on #t ensuring that id is unique it removes duplicates first rather than potentially attempting to delete the same row multiple times.