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

Deadlocks - SQL Server Monitors for Deadlocks Right? Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2014 7:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:58 PM
Points: 84, Visits: 164
Hi,

Can anyone please explain this to me; SQL Server has as dedicated thread that monitors for deadlocks and if one is found arbitrarily rolls back one of the transactions right? So my question is if this thread is constantly running, monitoring for deadlocks, why do they still occur?

Thanks!
Post #1550492
Posted Wednesday, March 12, 2014 7:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:59 PM
Points: 1,194, Visits: 2,222
Deadlock occurs when 2 or more processes block one another permanently and unless deadlock situation has been resolved no process can go ahead and complete it's task.
Monitoring is just monitoring, it's not a prevention of any DB situation\occurrence.

--
SQLBuddy
Post #1550505
Posted Wednesday, March 12, 2014 7:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,952, Visits: 7,170
SQL server makes you aware the deadlock occurred, but it's not causing the deadlocks, your code is causing the deadlocks (whether that be TSQL, application code, etc.

Deadlocks typically occur because two or more processes are attempting to lock/update the same resource - (Reference: http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/).

If you want to see more detailed information on what caused the deadlock and which one was the deadlock victim, you can have them written to the error log - you can do this by turning on the proper trace flag:
DBCC TRACEON(-1, 1204, 3605)



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1550506
Posted Thursday, March 13, 2014 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
You can gather information on deadlocks on SQL Server 2008 and better without ever setting a trace flag. Just use the system_health extended event session that is running automatically on your system. It captures deadlock graphs. For a lot more information about deadlocks, I'd suggest this article by Gail Shaw.

----------------------------------------------------
"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 Query Performance Tuning
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 #1550661
Posted Thursday, March 13, 2014 6: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 @ 4:08 PM
Points: 40,177, Visits: 36,580
MyDoggieJessie (3/12/2014)
If you want to see more detailed information on what caused the deadlock and which one was the deadlock victim, you can have them written to the error log - you can do this by turning on the proper trace flag:
DBCC TRACEON(-1, 1204, 3605)



3605 isn't needed. That traceflag just causes some commands like DBCC PAGE to write their output into the error log.
1204 is for SQL 2000 and below, it doesn't give as much information as the newer traceflag does.

For deadlock monitoring on SQL 2005 and above:
DBCC TRACEON(1222,-1)

Or the system health event session on SQL 2008 and above.



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 #1550672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse