Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Deadlock In SQL Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 5:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1403933
Posted Tuesday, January 8, 2013 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 208, Visits: 761
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?
Post #1404330
Posted Tuesday, January 8, 2013 9:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1404350
Posted Tuesday, January 8, 2013 10:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 208, Visits: 761
I am Refreshing this Database from Production to Test (Everything is same except One day old data)
Post #1404362
Posted Tuesday, January 8, 2013 10:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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.
Post #1404376
Posted Tuesday, January 8, 2013 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 208, Visits: 761
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....
Post #1404380
Posted Tuesday, January 8, 2013 10:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1404381
Posted Tuesday, January 8, 2013 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 208, Visits: 761
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

Post #1404406
Posted Tuesday, January 8, 2013 12:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1404416
Posted Tuesday, January 8, 2013 12:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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.
Post #1404419
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse