March 12, 2014 at 7:00 pm
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!
March 12, 2014 at 7:43 pm
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
March 12, 2014 at 7:44 pm
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; Theyll drag you down to their level and beat you with experience
March 13, 2014 at 6:37 am
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[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 6:55 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply