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 Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 6:24 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
There is some deadlock appeared in the database, can any one tell me how to come out of deadlock situations.
Rgards,
Naveen
Post #970325
Posted Tuesday, August 17, 2010 6:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 PM
Points: 225, Visits: 466
Naveen..
This is a very open questin.... to prevent this happening again.. you need to have understanding of your app and then you need to understand why it happened...

Below are some good links which you can refer to start your investigation...

http://msdn.microsoft.com/en-us/library/ms178104.aspx
http://www.sqlservercentral.com/Forums/Topic514877-146-1.aspx#bm515674

and here you need to provide as much as info you've to get to the point answer... as there can be 100s of reason for this to happen.... so post all the info you've or can gather... and lets see what we can make out of that....

Tell me if this helps....

Rohit
Post #970331
Posted Tuesday, August 17, 2010 6:37 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
how to clear the deadlocks, is it required to restart the sql server services or is anyother method.
Post #970342
Posted Tuesday, August 17, 2010 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 PM
Points: 225, Visits: 466
See deadlock is automatically handeled by sql server we don't need to do anything except do our investigation to find out the root cause and taking preventive measures so that it never happens again....
Post #970350
Posted Tuesday, August 17, 2010 6:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
Here's a very crud way for solving the problem once in a while.

exec sp_who2

Look at the blocked by column. That will list the spid that is most likely blocking everyone else.

then run KILL <put spid here>.

Then rerun sp_who2 again to see if the lock is resolved. Sometimes you need to do this 2-3 times.


This stops the immediate problem but it doesn't prevent it from happenning again.

Also keep in mind that all the transations that were running need to be restarted.
Post #970351
Posted Tuesday, August 17, 2010 6:41 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 @ 10:05 AM
Points: 42,829, Visits: 35,961
SQL automatically resolves deadlocks as soon as it detects them. The error message that one gets is a notification that SQL detected the deadlock, resolved it by killing one session, and that session was yours.


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 #970354
Posted Tuesday, August 17, 2010 6:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 PM
Points: 225, Visits: 466
Although u need to be very cautious doing this on a production box... and not at all recommended unless you're facing some serious perfomance issues and you need to be aware of the impact as well....
Post #970356
Posted Tuesday, August 17, 2010 6:45 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 @ 10:05 AM
Points: 42,829, Visits: 35,961
Ninja's_RGR'us (8/17/2010)
Here's a very crud way for solving the problem once in a while.

exec sp_who2

Look at the blocked by column. That will list the spid that is most likely blocking everyone else.

then run KILL <put spid here>.

Then rerun sp_who2 again to see if the lock is resolved. Sometimes you need to do this 2-3 times.


For blocking. Not for deadlocks.



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 #970362
Posted Tuesday, August 17, 2010 6:53 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, February 3, 2011 3:00 AM
Points: 164, Visits: 656
Hi,

you need find out, if any blocks occurred in your database

use master
select * from sysprocesses where open_tran<>0

if any uncommitted transactions occurred in your database. blocks are created. Check it and fix
it.

With Regards
Balaji.G

Post #970372
Posted Monday, August 15, 2011 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:41 AM
Points: 4, Visits: 44
Is this sql server 2005? Then run a profiler and capture the deadlock graph. Deadlock can occur because of a lot of reasons and the deadlock graph will give you a good idea where to start.

Also, just an observation. Most of the times while resolving deadlocks,we tend to look for X locks only. But please do examine the S locks also and let us just remember that a deadlock occurs on one (or more) resource. That resource can be a table itself, a row, a page, an index anything.
Even a simple select and then and update statement may lead to a deadlock.
So best idea would be to get hold of the deadlock graph first and then only can this problem be solved
Post #1160221
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse