Deadlocking in stored proc calls (sql 2008)

Question

Deadlocking in stored proc calls (sql 2008)

Barkingdog

SSCoach

Points: 18670
More actions
September 19, 2010 at 3:22 pm

#240652

The deadlock revolves around 3 tables (A, B, and C), shown simplified below.
------------------------------------------------------------------------
Table “A”:
CREATE TABLE [dbo].[A](
[A_ID] [bigint] NOT NULL,
[A_DeleteID] [bigint] NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[A_ID] ASC
)
)
------------------------------------------------------------------------
------------------------------------------------------------------------
Table “B”
CREATE TABLE [dbo].(
[B_ID] [bigint] NOT NULL,
[B_DeleteID] [bigint] NULL,
[FK_A_ID] [bigint] NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[B_ID] ASC
)
)
(FK_A_ID) in table B is a foreign key; PK = A_ID in table A
------------------------------------------------------------------------
------------------------------------------------------------------------
Temporary table “C” (created by stored proc call)
CREATE TABLE [dbo].[C](
[C_ID] [bigint] NOT NULL,
[A_DeleteID] [bigint] NULL,
[B_DeleteID] [bigint] NULL,
)
------------------------------------------------------------------------
Two developers invoke the same stored proc and a deadlock (usually) results. From the xml output of sql Profiler, the deadlock occurs on the PK of table B (B_ID). Here are the “offending” statements causing the deadlock.
1. Delete A join C on (A.A_DeleteID = C.A_DeleteID)
2. Delete B join C on (B.B_DeleteID = C.B_DeleteID)
I think that the DELETE in statement 1) puts a lock on A_ID (the primary key) and also causes a scan of the foreign key FK_A_ID in table B. At the same time, the Delete in statement 2 is attempting to delete records from table B, which contain FK references to A. A deadlock seems likely. (BTW: our developers have reported that if we drop the foreign key relationship between tables A and B, then the same code hardly deadlocks. Of course, we want to keep the relationships intact.) I think this is the essence of the issue.
What can be done to eliminate or minimize deadlocks in this case?
TIA,
Barkingdog

Viewing 4 posts - 1 through 3 (of 3 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Barking, if you'll look in the other thread you posted regarding transactions, you'll notice a discussion, and explaination/workaround, to this.

EDIT: Allow me to extrapolate slightly since this part wasn't covered there. There are row, page, and table locks. There's no index lock.

At a guess, and you can try to test this with the sp_lock system procedure (you'll need to know the object id's from sysobjects), you're trying to work off data from the same page in memory, and that's got the locks.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

thecoding SSC Veteran Points: 211 More actions · Answer 2

Two developers invoke the same stored proc and a deadlock (usually) results. From the xml output of sql Profiler, the deadlock occurs on the PK of table B (B_ID). Here are the “offending” statements causing the deadlock.
1. Delete A join C on (A.A_DeleteID = C.A_DeleteID)
2. Delete B join C on (B.B_DeleteID = C.B_DeleteID)

Could you please, post the stored procedure up here?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

I don't know if it will help your deadlock situation but you're asking for a world of hurt with how you're doing the deletes. If you look in Books Online, you won't find even one example of a delete using your format of...

Delete A join C on (A.A_DeleteID = C.A_DeleteID)

Doing such a delete usually works just fine. The killer happens when it doesn't and it's not a predictable thing except that if parallelism occurs and the indexes are just right, you end up with a "Halloween" effect which will pin the CPU's of your server to the wall for a couple of hours on something that should take only a second or two to complete.

The correct form for a joined delete (in your terms) is as follows...

DELETE TableA FROM TableA AS a JOIN TableC AS c ON a.A_DeleteID = c.A_DeleteID

... or ...

DELETE a FROM TableA AS a JOIN TableC AS c ON a.A_DeleteID = c.A_DeleteID

If you join to do a delete, the target of the deletion MUST be in the FROM clause to prevent the "Halloween" effect.

Please Google for the "SQL Halloween Effect" for detailed information (it affects Updates, as well) but it takes so long because the "read cursor" and the "write cursor" behind the scenes get out of sync with each and other causes each to go crazy with loops. Full blown cartesian joins take a while to resolve even at system or machine levels. Sometimes it's so bad that it'll throw in a sproc recompile for each row deleted.

Please remember that even though usage isn't so common, both DELETE and UPDATE have 2 FROM clauses and you should not mix one with the other.

--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)