Multiple deadlocks occuring on simultaneous deletions from temporal table

  • We have a stored procedure which is attempting to delete 1-2 rows at a time from a temporal table overnight. The stored procedure is executed several times simultaneously (around 10-15 threads) by parallel processes, and so attempts may be made to delete 30 rows simultaneously by different SPIDs, for example.

    DELETE FROM dbo.Table WHERE ID IN (12);

    DELETE FROM dbo.Table WHERE ID IN (102);

    DELETE FROM dbo.Table WHERE ID IN (4);

    DELETE FROM dbo.Table WHERE ID IN (7, 9);

    Etc.

    With the other tables in our database, this works without issue. With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.

    We have tried using WITH (ROWLOCK),  as well as adding a non-clustered index on the column by which rows are being selected for deletion. These make no difference.

    The default isolation level in use is Read Committed Snapshot Isolation (RCSI).

    Can anyone please advise what is going on here? Is it due to the underlying row versioning/history table process in the temporal table? How can we delete efficiently from a temporal table without seeing deadlocks?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Hmmm it's hard to be certain without testing.   I've had issues with inserts of multiple rows with the same system datetime into System Versioned tables.  It's the "simultaneous" part it seems to not like.   The first request succeeds but the rest fail.  Suppose it's possible to enumerate your parallel processes so there's process #1, ..., process #30.  Instead of issuing a single DELETE try adding a different WAITFOR DELAY for each (enumerated) process.  So parallel process #1 waits for 0.1 seconds, process #2 waits for 0.2 seconds, etc...  The value for the delay in the WAITFOR DELAY must be a constant or a variable so it's DECLARED as @dt.

    process 1

    declare @process_enum       int=1;
    declare @dt datetime=timefromparts(0,0,0,@process_enum,3);

    waitfor delay @dt;
    DELETE FROM dbo.Table WHERE ID IN (12);

    process 2

    declare @process_enum       int=2;
    declare @dt datetime=timefromparts(0,0,0,@process_enum,3);

    waitfor delay @dt;
    DELETE FROM dbo.Table WHERE ID IN (102);

    I'd be curious to know how this works out 🙂

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • We attempted delaying each individual SPID by a random number of whole seconds, between 0 and 9. Even this didn't resolve the issue, with deadlocks continuing to occur as before.

  • Have you been able to review the xml_deadlock_report? This will show you the resource involved in the deadlock which might help point you in the right direction.

    https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock

  • Yes we did that - the deadlocks were occurring between two SPIDs running the delete statement on the temporal table. The locked resource was the temporal table itself, with "IX" and "SIX" lock modes listed in the resource list of the deadlock XML.

  • Instead of having multiple connections trying to delete one or two rows at a time from all over hell's little half acre in the table, remember that it's a TEMPORAL table.  Pick a number of rows you want to delete (like 10,000 or so) and find the DATE/TIME in the temp table that is represents the 10,000th row in date order from the beginning of the file.  Then tell just one job to do a delete for the rows before or equal to that date.

    [Edited] to Change to date/time instead of ID.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is the table clustered on ID?

    Have you got any other indexes on the table?

    Have you looked at the execution plan?

  • Jonathan AC Roberts wrote:

    Is the table clustered on ID?

    Have you got any other indexes on the table?

    Have you looked at the execution plan?

    Oh lordy... I posted before having enough coffee.  This is a temporal table and it needs to be deleted by date.  Your post reminded me of that and so thank you.  I've also updated my post above.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • zoggling wrote:

    DELETE FROM dbo.Table WHERE ID IN (12);

    ...

    With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.

    We have tried ... adding a non-clustered index on the column by which rows are being selected for deletion. These make no difference.

    The default isolation level in use is Read Committed Snapshot Isolation (RCSI).

    You will definitely need a (UNIQUE nonclustered) index on ID (alone, don't include columns), or the DELETE would have to scan the table to find the ID(s) -- remember, unless you "tell" SQL the ID is unique, it doesn't know it is, so even after it finds the ID it will keep searching rows for other ID matches.  That would indeed almost inevitably lead to deadlocks every time.

    Another possibility to try is to set ALLOW_PAGE_LOCKS to OFF.  I won't go into any details of how you might tell if page locking might be affecting you here.  OFF might have no effect, it might help things, it might hurt other things.  It will prevent a REORG of the index, but you can -- and should -- always turn it back on before doing any index maintenance, including a REBUILD.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    zoggling wrote:

    DELETE FROM dbo.Table WHERE ID IN (12); ... With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.

    We have tried ... adding a non-clustered index on the column by which rows are being selected for deletion. These make no difference.

    The default isolation level in use is Read Committed Snapshot Isolation (RCSI).

    You will definitely need a (UNIQUE nonclustered) index on ID (alone, don't include columns), or the DELETE would have to scan the table to find the ID(s) -- remember, unless you "tell" SQL the ID is unique, it doesn't know it is, so even after it finds the ID it will keep searching rows for other ID matches.  That would indeed almost inevitably lead to deadlocks every time.

    Another possibility to try is to set ALLOW_PAGE_LOCKS to OFF.  I won't go into any details of how you might tell if page locking might be affecting you here.  OFF might have no effect, it might help things, it might hurt other things.  It will prevent a REORG of the index, but you can -- and should -- always turn it back on before doing any index maintenance, including a REBUILD.

    I'm thinking they should be deleting by any ID???  Shouldn't they be deleting by date and time???

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you for your responses - some good suggestions there. We'll test these out and come back to you.

    We had tried clustered indexes on ID previously, but they were not unique indexes, so we will try that.

    Unfortunately we ideally need to delete by ID if we can, but we may have to resort to using the date columns if that's what temporal tables require.

    • This reply was modified 3 weeks, 2 days ago by  zoggling.
  • Jeff Moden wrote:

    ScottPletcher wrote:

    zoggling wrote:

    DELETE FROM dbo.Table WHERE ID IN (12); ... With the temporal table, deadlocks are guaranteed every time. Only one of the deletions will succeed; the rest will fail.

    We have tried ... adding a non-clustered index on the column by which rows are being selected for deletion. These make no difference.

    The default isolation level in use is Read Committed Snapshot Isolation (RCSI).

    You will definitely need a (UNIQUE nonclustered) index on ID (alone, don't include columns), or the DELETE would have to scan the table to find the ID(s) -- remember, unless you "tell" SQL the ID is unique, it doesn't know it is, so even after it finds the ID it will keep searching rows for other ID matches.  That would indeed almost inevitably lead to deadlocks every time.

    Another possibility to try is to set ALLOW_PAGE_LOCKS to OFF.  I won't go into any details of how you might tell if page locking might be affecting you here.  OFF might have no effect, it might help things, it might hurt other things.  It will prevent a REORG of the index, but you can -- and should -- always turn it back on before doing any index maintenance, including a REBUILD.

    I'm thinking they should be deleting by any ID???  Shouldn't they be deleting by date and time???

    In a theoretical world, perhaps.  But if you adjust the default control setting, I think you can delete by any column/method you want to.  I'm sure Celko would have a fit about violating theory -- and somehow, as always, linked it to punched cards -- but one has to do what your app / system / company needs you to do.  If the process needs redesigned, do that later if you can, of course.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • You can delete from temporal tables by ID just fine, you do not have to use the datetime columns.  Several years ago we had an issue that sounds similar but I could be remembering incorrectly.  At one time there was a MS page that recommended 2 indexes on the temporal start/stop datetime columns, I can't find it now.  One of them wasn't a regular index, it may have been a column store index......  We found that we had deadlock issues if we created both of the recommended indexes.  Removing one of them and the issue went away.  I thought I had posted about the issue here but can't find it.  This was about 3 years ago maybe so my memory may be a little off but thought I would throw it out in case it helps.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply