Deadlock on update in the same table on Azure with pagelock

Question

I have a table that shows events on the Azure DB. Events are coming very frequently. They are adding and then they are updating.

The update queries look like this:

SET [DeactivationEventId] = @EventId, [Deactivated] = 1, [Type] = @DeactivationType
WHERE [DeactivationEventId] IS NULL
AND [ItemId] = @ItemId
AND [Id] <> @EventId

The graph looks like this:

  <victim-list>
    <victimProcess id="process1752f0bbc28" />
  </victim-list>
  <process-list>
    <process id="process1752f0bbc28" taskpriority="0" logused="888" waitresource="PAGE: 6:1:667 " waittime="4144" ownerId="7884216" transactionname="user_transaction" lasttranstarted="2020-12-14T01:39:04.073" XDES="0x1753ad74428" lockMode="S" schedulerid="2" kpid="64600" status="suspended" spid="83" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-14T01:39:04.073" lastbatchcompleted="2020-12-14T01:39:04.073" lastattention="1900-01-01T00:00:00.073" clientapp="Core .Net SqlClient Data Provider" hostname="RD00155D55DC34" hostpid="8768" loginname="westbrookb" isolationlevel="read uncommitted (1)" xactid="7884216" currentdb="6" currentdbname="Rythmos" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xb5d19baa56c298af" queryplanhash="0xa0741009000fbf64" line="1" stmtstart="102" stmtend="558" sqlhandle="0x020000007676e21a17f941df30c67e9f52cb3adb4dbf4e800000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@EventId int,@DeactivationType int,@ItemIdbigint)UPDATE [dbo].[Events] WITH (ROWLOCK)
SET [DeactivationEventId] = @EventId, [Deactivated] = 1, [Type] = @DeactivationType
WHERE [DeactivationEventId] IS NULL
AND [ItemId] = @ItemId
AND [Id] <> @EventId</inputbuf>
    </process>
    <process id="process17537256ca8" taskpriority="0" logused="1528" waitresource="PAGE: 6:1:667 " waittime="4141" ownerId="7884219" transactionname="user_transaction" lasttranstarted="2020-12-14T01:39:04.083" XDES="0x17541f1c428" lockMode="S" schedulerid="3" kpid="45516" status="suspended" spid="85" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-14T01:39:04.087" lastbatchcompleted="2020-12-14T01:39:04.080" lastattention="1900-01-01T00:00:00.080" clientapp="Core .Net SqlClient Data Provider" hostname="RD0003FF811932" hostpid="8012" loginname="westbrookb" isolationlevel="read uncommitted (1)" xactid="7884219" currentdb="6" currentdbname="Rythmos" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xb5d19baa56c298af" queryplanhash="0xa0741009000fbf64" line="1" stmtstart="102" stmtend="558" sqlhandle="0x020000007676e21a17f941df30c67e9f52cb3adb4dbf4e800000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@EventId int,@DeactivationType int,@ItemIdbigint)UPDATE [dbo].[Events] WITH (ROWLOCK)
SET [DeactivationEventId] = @EventId, [Deactivated] = 1, [Type] = @DeactivationType
WHERE [DeactivationEventId] IS NULL
AND [ItemId] = @ItemId
AND [Id] <> @EventId</inputbuf>
    </process>
  </process-list>
  <resource-list>
    <**pagelock** fileid="1" pageid="667" dbid="6" subresource="FULL" objectname="065e5285-8d87-4534-93bb-20d8770ae630.dbo.WirepasDeviceEvents" id="lock174acf59300" mode="IX" associatedObjectId="72057594085638144">
      <owner-list>
        <owner id="process17537256ca8" mode="IX" />
        <owner id="process17537256ca8" mode="S" requestType="convert" />
      </owner-list>
      <waiter-list>
        <waiter id="process1752f0bbc28" mode="S" requestType="convert" />
      </waiter-list>
    </pagelock>
    <**pagelock** fileid="1" pageid="667" dbid="6" subresource="FULL" objectname="065e5285-8d87-4534-93bb-20d8770ae630.dbo.WirepasDeviceEvents" id="lock174acf59300" mode="IX" associatedObjectId="72057594085638144">
      <owner-list>
        <owner id="process1752f0bbc28" mode="IX" />
        <owner id="process1752f0bbc28" mode="S" requestType="convert" />
      </owner-list>
      <waiter-list>
        <waiter id="process17537256ca8" mode="S" requestType="convert" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

I have four not-clustered indexes on this table. This one is for avoiding this deadlock:

(
    [DeactivationEventId] ASC,
    [ItemId] ASC,
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

There a lot of alerts inserting and then updating in milliseconds: **2020-12-14 01:38:00.180

2020-12-14 01:38:00.203**

My question. If I separate it by two queries:

select Id 
from [dbo].[Events] WITH (NoLock)
WHERE [DeactivationEventId] IS NULL
AND [ItemId] = @ItemId
AND [Id] <> @EventId

SET [DeactivationEventId] = @EventId, [Deactivated] = 1, [Type] = @DeactivationType
WHERE Id = @Id

To avoid page lock. Here is the exec plan:

What do you think?

EDITED.

Separating the request did help for now. I am not getting deadlock now.

Answer 1

In the graph, you can see that the two parties to the deadlock graph are the same query plan (have same hash, so likely same plan). Your rewrite is doing a seek to the specific row you want to update and then doing a "wide" update plan to update each index. It will take locks on each b-tree/heap/etc as it touches things in the plan for each row. I'm assuming you are using auto-commit transactions since you haven't mentioned anything.

You happen to avoid the deadlock in your rewrite by moving the select part (which can scan many rows potentially, but I can't see the plan to be sure) out of the query that takes the U lock needed for the update. (S locks are compatible with other S locks but not with U locks, so concurrent queries trying to take a U lock when another instance of that query has an S lock can cause deadlocks).

Other ways to avoid the deadlock would include adding an UPDLOCK hint on the original, larger update query. This would take U locks instead of S locks for each row that was read (even if it was not going to be updated). The pattern you have picked is likely great for your application, but if you get deadlocks even in the single row case for the same query executing concurrently (or similar queries against the same table that each try to do singleton point seeks), there are cases where different query plans can take locks in different order for indexes in the same table. For example, if query 1 takes locks on index1 and then the clustered index and query 2 takes a lock on the clustered index and then index 1, these could deadlock as well. This pattern can be fixed with the UPDLOCK approach.