UPDATE with SELECT leads to deadlock-CodePudding

I have a very simple update statement within one job step:

UPDATE [Table]
SET 
[Flag] = 1
WHERE [ID] = (SELECT MAX([ID]) FROM [Table] WHERE [Name] = 'DEV')

Normally there are no issues with this code, but sometimes it ends up with the deadlock.

Is it in general possible, that such stand-alone piece of code leads to a deadlock?

Table schema:

CREATE TABLE [Table]
(
  [ID]   [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](100) NOT NULL,
  [Flag] [bit] NULL,
  CONSTRAINT [Table_ID] PRIMARY KEY CLUSTERED 
)

CodePudding user response：

The deadlock cause is quite obvious: there is no index on Name, so it's going to scan the whole table for the subquery. There is also no UPDLOCK hint on it, so that is also going to make deadlocks more likely.

Create an index on Name

CREATE NONCLUSTERED INDEX IX_Name ON [Table] (Name) INCLUDE (ID);

And make sure you use UPDLOCK on the subquery

UPDATE [Table]
SET Flag = 1
WHERE ID = (
    SELECT MAX(ID)
    FROM [Table] t2 WITH (UPDLOCK)
    WHERE t2.Name = 'DEV')

This query is much more efficiently written without a self-join, like this:

UPDATE t
SET Flag = 1
FROM (
    SELECT TOP (1)
      *
    FROM [Table] t
    WHERE t.Name = 'DEV'
    ORDER BY ID DESC
) t;

Even though the optimizer can often transform into this version, it's better to just write it like this anyway.

This version does not need a UPDLOCK, it will be added automatically. You still need the above index though.

db<>fiddle