SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock In SQL


Deadlock In SQL

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221865 Visits: 46282
rocky_498 (1/7/2013)
The Store Procedure "MyStoreProcedure" has 19 Pages hard to post here ( I hope you understand). Some time this SP work fine and Some time i am getting Dead Lock Error.....


Then I suggest you start by rewriting that procedure and simplifying it (or splitting it up). A several page stored proc is a good indication that there's a design problem.

Once you've simplified and broken the procedure down, if you're still having deadlocks post the new 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


rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1417
I will do that.
Quick Question. I am running same procedure in different Server and its working fine with no Problem. I ran this SP 10 time no deadlock problem.

Could be Server Issue? or still deadlock in que? or something... ( I am just gussing)... What do you think?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221865 Visits: 46282
rocky_498 (1/8/2013)
Could be Server Issue?


No

or still deadlock in que?


No.

Deadlocks occur when 2 pieces of code want the resources that the other is using. It's not a problem of one piece of code, rather of 2 (or more). If this proc is deadlocking on one server and not on the other, then the other server probably isn't running the code that this proc is deadlocking with.

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


rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1417
I am Refreshing this Database from Production to Test (Everything is same except One day old data)
lnardozi 61862
lnardozi 61862
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 617
I'd be willing to bet a dollar it has something to do with those two "NOT IN"s in the query.If you change those to left joins I bet the problem is greatly mitigated, though it won't go away completely.

Deadlocks happen when a depends on b and b depends on a. that usually happens when a query takes a long time to complete. Making the query go fast makes it happen less often because by the time b depends on a, a no longer depends on b.
rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1417
Awesome.... You are right the problem with two (Not IN) If i remove both logic SP Works fine with No problem. The Problem with One Not IN i think that one is taking to long. Thank you all of you guys to help me to solve this issue.

Thank You....
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221865 Visits: 46282
rocky_498 (1/8/2013)
I am Refreshing this Database from Production to Test (Everything is same except One day old data)


So test has exactly the same load, exactly the same queries executing as production? I highly doubt that.

Deadlocks occur when two pieces of executing code want resources that the other one owns. If your stuff deadlocks on prod and runs fine on test, then that's because the queries that are executing on production are not executing on test.

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


rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1417
Good Question...

I am dam sure about this ALL SPS/INDEX/VIEW/TRIGG/ and so on are same (Prod/Test)
If i ran Same SP without any changes on Prod giving me Error and On test No Problem.
I change or comment out one Not Null Syntax on Production and its working fine....
To be honest i have no clue what's going on :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221865 Visits: 46282
rocky_498 (1/8/2013)
I am dam sure about this ALL SPS/INDEX/VIEW/TRIGG/ and so on are same (Prod/Test)


I am not talking about the *definition* of the queries. To get a deadlock on two different servers, they have to have exactly the same queries *running* at the same time. That means that unless Test has as many connections as the production server does, running exactly the same queries, exactly the same data columns and activity, you won't see the deadlock on Test.

Deadlock require 2 or more queries running simultaneously, not just for the same pieces of code to be sitting in the database.

If i ran Same SP without any changes on Prod giving me Error and On test No Problem.


Because on Test, the procedures that it is deadlocking with are not executing at the same time (which they are on production)

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


lnardozi 61862
lnardozi 61862
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 617
GilaMonster is exactly right - removing the NOT INs is not a long term resolution as the deadlocks will still occur, just not as frequently. Expressing them as left joins though is a MUCH faster solution which will minimize the deadlocks. It won't get rid of them completely though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search