Multiple deadlocks occuring on simultaneous deletions from temporal table

Question

Post reply

Multiple deadlocks occuring on simultaneous deletions from temporal table

zoggling

SSCrazy

Points: 2243
More actions
June 18, 2021 at 9:02 am

#3897911

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?

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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

zoggling SSCrazy Points: 2243 More actions · Answer 3

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.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 4

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

zoggling SSCrazy Points: 2243 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

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.

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

Is the table clustered on ID?

Have you got any other indexes on the table?

Have you looked at the execution plan?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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.

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 9

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

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.

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

zoggling SSCrazy Points: 2243 More actions · Answer 11

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 years, 4 months ago by zoggling.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 12

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Scott Mildenberger Ten Centuries Points: 1351 More actions · Answer 13

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.