Help with SQL SCRIPT Causing Deadlock

Question

Post reply

Help with SQL SCRIPT Causing Deadlock

FTdenali

SSCertifiable

Points: 5362
More actions
November 23, 2010 at 6:55 am

#147727

Here is the script below:-
delete from [DL1]
from
[DL1]
left join [SL1] on
[SL1].[Id] = [SL1id] and
[SL1].[StartTime] = [SL1StartTime]
where
[SL1].[Id] is null
delete from [SFL1]
from
[SFL1]
left join [DL1] on
[DL1].[Id] = [DL1Id] and
[DL1].[StartTime] = [DL1StartTime]
where
[DL1].[Id] is null
delete from [DomainClassification]
from
[DomainClassification]
left join [DL1] on
[DL1].[Id] = [DL1Id] and
[DL1].[StartTime] = [DL1StartTime]
where
[DL1].[Id] is null
delete from [DomainFilterListCategory]
from
[DomainFilterListCategory]
left join [DL1] on
[DL1].[Id] = [DL1Id] and
[DL1].[StartTime] = [DL1StartTime]
where
[DL1].[Id] is null
delete from [FileClassification]
from
[FileClassification]
left join [SFL1] on
[SFL1].[Id] = [SFL1Id] and
[SFL1].[StartTime] = [SFL1StartTime]
where
[SFL1].[Id] is null

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the question?

If it's deadlocking, we need to see what it's deadlocking with and on what resources.

Enable traceflag 1222. That will write the output of the deadlock graph into the error log. Post the deadlock graph here as well as the definitions of the tables and indexes mentioned in the deadlock graph.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

Surely for this to deadlock there needs to be another process for it to deadlock with?

Each of these statements would execute sequentially without locking. You may well get PAGE_IO_LATCH or another type of wait stat depending on the table sizes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

MysteryJimbo (11/23/2010)
Surely for this to deadlock there needs to be another process for it to deadlock with?

Yup. Just as one hand can't clap, one session can't deadlock.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95460 More actions · Answer 4

GilaMonster (11/23/2010)
Just as one hand can't clap, one session can't deadlock.

I like this analogy!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

GilaMonster (11/23/2010)
MysteryJimbo (11/23/2010)
Surely for this to deadlock there needs to be another process for it to deadlock with?
Yup. Just as one hand can't clap, one session can't deadlock.

Rhetorical question! 😀

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 6

Note: Sessions can deadlock themselves, due to parallelism. Rare, but it happens, and troubleshooting it is a real pain.

Not one hand clapping so much as the fingers getting into trouble when you take your eyes off of them.

Eddie Wuerch
MCM: SQL

FTdenali SSCertifiable Points: 5362 More actions · Answer 7

Eddie is right, what i am experiencing lokks exactly like wha Eddie has said, "Session deadlocking themselves"

The reason i say so is because the SPID that gets thrown by SQL is a session ID linked to my current batch execution.

i am currently importing the Database to a test Environment just to see if the same problems will arise.

Bare in mind that my Test environment is running SQL 2008 R2 x64 Enteprise and Windows 2008 R2. And the Database's source server is SQL2005.

I am not sure if the fact that i will be testing on a SQL 2008 R2 server will have any impact on my results since the production db is running on sql2005 on a windows 2003 server OS.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

GilaMonster (11/23/2010)
If it's deadlocking, we need to see what it's deadlocking with and on what resources.
Enable traceflag 1222. That will write the output of the deadlock graph into the error log. Post the deadlock graph here as well as the definitions of the tables and indexes mentioned in the deadlock graph.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Review this link, and the two associated posts:

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Also try here if you are getting the intraquery parallelism deadlock:

http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

Eddie Wuerch (11/23/2010)
Note: Sessions can deadlock themselves, due to parallelism. Rare, but it happens, and troubleshooting it is a real pain.
Not one hand clapping so much as the fingers getting into trouble when you take your eyes off of them.

I believe OPTION (MAXDOP 1) would take care of the problem, then. Yes?

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

FTdenali SSCertifiable Points: 5362 More actions · Answer 11

Hi, I don't know if this helps but FYI i had a look at the Table properties of all the tables that are involved in this query and here are my findings:-

1) All ID columns are defined with [NOT NULL] Constrain - So i guese that means that they cannot be empty.

2)The Tables do not have any Referential integrity, - pr and fr keys do not exist.

3)All ID colums from one table also exist on the other tables as if there are foreign key definition but there is none.

4) The Database is about 100GB so there is quite a lot of data.

The script has been running for 4hrs and i aborted it but even now it's still rolling back.

I have not yet got the deadlock error, but when i do i will paste the results.