Sql-server – INSERT/UPDATE stored proc deadlocking itself

deadlockinsertsql serversql server 2014update

I have a table (Database2.dbo.OrganizerDataDependencyChange) that contains information about when a row in certain other tables was last changed. On each of these tables I have a trigger that calls a stored procedure (Database2.dbo.SaveOrganizerDataDependencyChange) that simply updates the Database2.dbo.OrganizerDataDependencyChange with the time that the trigger fired. These triggers are fired from either Database1 or Database2, so they often are cross-database calls.

I'm getting read-write deadlocks on the Database1.dbo.OrganizerDataDependencyChange table when two different rows on the table are being updated and I don't understand why. There's only one index on the table, and it's the clustered index that completely covers the query so no lookup deadlock is possible, and though there are two statements in the proc, I specifically re-wrote it to what I understand is the best way to avoid concurrency issues, with a WHERE NOT EXISTS instead of using IF/ELSE logic, so it shouldn't be coming at this index from different angles, right? Since it's fired in triggers and cross-database I'm having some difficulty reproducing the issue, I can of course reproduce blocking but that should be fine and is what I expect.

Can someone help me understand what's going on here? I could probably fix it with a NOLOCK hint or maybe an applock but I still wouldn't understand why it was happening.

Here's the table:

CREATE TABLE [dbo].[OrganizerDataDependencyChange](
[TableID] [INT] NOT NULL,
[Database] [VARCHAR](150) NOT NULL,
[Updated] [DATETIME] NULL
)
CREATE CLUSTERED INDEX [CX_OrganizerDataDependencyChange_TableID_DB] ON [dbo].[OrganizerDataDependencyChange]
(
[TableID] ASC,
[Database] ASC
)

And here's the stored procedure:

CREATE PROCEDURE [dbo].[SaveOrganizerDataDependencyChange]
(
@TableID int,
@Database varchar(150)
)
AS
BEGIN

SET NOCOUNT ON;

    DECLARE @rowcount INT;

INSERT INTO dbo.OrganizerDataDependencyChange
        ( TableID, [Database], Updated )
SELECT TOP 1
    TableID = @TableID,
    [Database] = ISNULL(@Database, ''),
    Updated = GETDATE()
FROM dbo.OrganizerDataDependencyChange
WHERE NOT EXISTS
(
    SELECT
        1
    FROM dbo.OrganizerDataDependencyChange
    WHERE TableID = @TableID
    AND [Database] = @Database
)

SET @rowcount = @@ROWCOUNT  --How many rows were inserted?

UPDATE dbo.OrganizerDataDependencyChange
SET Updated = GETDATE()
WHERE @rowcount = 0 --Only run this if no rows were inserted
AND TableID = @TableID
AND [Database] = ISNULL(@Database, '')

END

And finally a couple of the deadlock graphs I'm getting out of XE that shows the ridiculous nested triggers:

Deadlock 1

Deadlock 2

MSSQL2014 Standard, if that makes a difference. Isolation level is the default Read Committed. If there's anything else I can share to shed more light please let me know!

Best Answer

I think you will find this pattern better:

BEGIN TRANSACTION;

UPDATE dbo.OrganizerDataDependencyChange WITH (HOLDLOCK)
  SET Updated = GETDATE()
  WHERE TableID = @TableID
  AND [Database] = ISNULL(@Database, '');

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO dbo.OrganizerDataDependencyChange
      ( TableID, [Database], Updated )
  VALUES(@TableID, ISNULL(@Database, ''), GETDATE());
END

COMMIT TRANSACTION;

Stop thinking about having to "check" if there is a row to update, and then updating it, which can lead to two full scans. Just try to update it. If no rows are updated, no harm, no foul - you can simply perform an insert (I talk about this a bit here).

Related Question