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 12»»

Deadlock prediction Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 4:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 4:09 AM
Points: 37, Visits: 142
Hi,
Do we have any specific mechanism or tools that can predict the dead lock that's will be going to occur in database.

Thanks
Post #1559861
Posted Wednesday, April 9, 2014 5:07 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 @ 2:51 PM
Points: 42,756, Visits: 35,850
No.

Detecting that they have occurred is easy. Predicting that they may would require massive, in-depth analysis of every piece of code, how it runs, how often it runs and even then would likely to be little more than an educated guess.



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 #1559874
Posted Wednesday, April 9, 2014 5:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,623, Visits: 28,007
Prediction? No. But following coding best practices to write queries such that they access objects in the same order, that they don't have major performance issues, that they use indexes in place, that you have the right indexes so that performance is high, all this can prevent deadlocks. Remember, at their core, deadlocks are performance problems. Tuning your system is one of the best things you can do to prevent deadlocks.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1559895
Posted Thursday, April 10, 2014 8:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 4:09 AM
Points: 37, Visits: 142
Thanks
Post #1560697
Posted Thursday, April 10, 2014 10:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:34 PM
Points: 215, Visits: 838
Grant Fritchey (4/9/2014)
Prediction? No. But following coding best practices to write queries such that they access objects in the same order


How does this come about in real life? I ask because I've never seen anywhere do it. How does it get coordinated over a group of programmers?

Is it because they are all using the same set of web service APIs to pull things in a certain order, and the database developers have some kind of documentation on the ordering... or something else?

Most of the places I've worked, things just became so large, you couldn't organize something like this without serious redevelopment and no place or tools to start. I can't imagine how others do it and sustain it.

Post #1560705
Posted Friday, April 11, 2014 1:30 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 @ 2:51 PM
Points: 42,756, Visits: 35,850
Cody K (4/10/2014)
How does it get coordinated over a group of programmers?


You put it in the coding standard, along with all your other good, required coding practices and require people to follow it.



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 #1560734
Posted Friday, April 11, 2014 2:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:34 PM
Points: 215, Visits: 838
GilaMonster (4/11/2014)
You put it in the coding standard, along with all your other good, required coding practices and require people to follow it.


So you have a document with your 1000-odd tables in it, and a list of categories. Need Table A? Get it in the order of Table B, C, D, then A. Need Table E? Get it in the order of Table C D E.

Are you serious?
Post #1560748
Posted Friday, April 11, 2014 2:26 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 @ 2:51 PM
Points: 42,756, Visits: 35,850
Huh???

If you need Table A, you access table A. If you need table E, then access table E. Why would you ever access tables B, C and D if all you want is Table A?



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 #1560755
Posted Friday, April 11, 2014 3:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,623, Visits: 28,007
Cody K (4/10/2014)
Grant Fritchey (4/9/2014)
Prediction? No. But following coding best practices to write queries such that they access objects in the same order


How does this come about in real life? I ask because I've never seen anywhere do it. How does it get coordinated over a group of programmers?

Is it because they are all using the same set of web service APIs to pull things in a certain order, and the database developers have some kind of documentation on the ordering... or something else?

Most of the places I've worked, things just became so large, you couldn't organize something like this without serious redevelopment and no place or tools to start. I can't imagine how others do it and sustain it.



I've seen it work and I've seen it break down. The main thing that makes it work in real life is something that certain programmers hate, stored procedures. A common access point to the data makes a huge difference. But, you have to have communication and coordination across teams. When that goes out the window, this isn't the only problem you'll see. Also, generated code from ORM tools can be especially painful. Solutions go back to communication and coordination as well as using stored procedures where appropriate.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1560787
Posted Friday, April 11, 2014 4:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 4:09 AM
Points: 37, Visits: 142
Actually we have 1000 or more procs and it is not an easy task to debug all and find out where person has not used proper coding guidelines

I was thinking if we collect all running database objects and their related objects and check whether related database objects are moving from idle to running and what are things that are common on both objects which can block each other? Like below

Keep all running objects on table and related object on another table
1. When running object move to idle, remove it all entries from both the table
2. When related objects move from idle to running, check what are things which can block on both running objects.
It will have generic solution which we can work for all objects.. just thinking what would be your feedback?
Post #1560798
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse