question

Jonathan Kehayias avatar image
Jonathan Kehayias asked

Reproducible Deadlock Using Cascading Constraints

I am looking for a reproducible deadlock that occurs from the use of Cascading Constraints. I can find references online for the specific problem, but none of them has a reproducible version of the deadlock.

I am not looking for questions about why I would want to do this, or that I should use trace flags to get a deadlock graph. I got all that, I want to see the specifics of the locking that lead to this specific type of deadlock. If you can provide the specifics that allow me to write a reproduction of this deadlock, that's a good enough answer.

I can already reproduce a number of other types of deadlocks like serializable isolation and bookmarklookup deadlocks I just can't figure out a reproduction for a Cascading Constraint deadlock.

sql-server-2008sql-server-2005deadlockforeign-keyconstraint
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
SQL Kiwi avatar image
SQL Kiwi answered
Hey Jonathan, USE tempdb; GO CREATE TABLE dbo.Parent ( parent_id INTEGER NOT NULL PRIMARY KEY, ); GO CREATE TABLE dbo.Child1 ( parent_id INTEGER NOT NULL PRIMARY KEY REFERENCES dbo.Parent ON UPDATE CASCADE ); GO CREATE TABLE dbo.Child2 ( parent_id INTEGER NOT NULL PRIMARY KEY REFERENCES dbo.Child1 ON UPDATE CASCADE ); GO INSERT dbo.Parent VALUES (1); INSERT dbo.Parent VALUES (-1); INSERT dbo.Child1 (parent_id) VALUES (1); INSERT dbo.Child2 (parent_id) VALUES (1); GO -- Run in connection 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET NOCOUNT ON; WHILE 1 = 1 BEGIN UPDATE dbo.Parent SET parent_id = 0 - parent_id; END; GO -- Run in connection 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET NOCOUNT ON; WHILE 1 = 1 BEGIN UPDATE dbo.Child1 SET parent_id = 0 - parent_id; END; GO
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jonathan Kehayias avatar image Jonathan Kehayias commented ·
I just got my account on here fixed so I could login again and what do I find? A total gem! Thanks Paul, that does it perfectly.
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - It's good to have you here sir :)
0 Likes 0 ·
SQL Kiwi avatar image SQL Kiwi commented ·
Just until my cookie expires Matt - grrr login issues - but thanks very much!
0 Likes 0 ·
user-1392 avatar image
user-1392 answered

It sounds like you're deleting from a table with a 'cascade on delete' foreign key relationship to another table or tables.

So you need two threads whose cascades cause a deadlock. The easiest way is probably to arrange a lock on two objects in the opposite order from each thread. So for example do an ordered delete of the same data in each thread, but in the opposite order in each.

I haven't tried this, so this answer comes with the usual caveats.

Dave.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.