Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2009-12-09 : 12:14:19
|
| Here are a few Deadlock questions. Any feedback to any of these would be great:1. A long-running update statement can block a select that is on the same table. If the Select statment waits long enough, SQL Server will terminate the Select statement. Why does SQL server refer to the terminated statement as a 'Deadlock Victim' when technically speaking this is not a true Deadlock.2. Is there a way to specify how long SQL Server will wait before it decides to terminate a process in a Deadlock or Blocking situation?3. I received a message that a process was terminated because of a Deadlock or Blocking yet this never got written to the Server Log. Any idea how that could be?4. What are your ideas or strategies for preventing Deadlocks or Blocks that lead to terminations. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-09 : 12:23:50
|
| 1. Select statements can be involved in deadlocks unless you are using the READ_COMMITTED_SNAPSHOT isolation level.2. No, as deadlocks are different than blocking. Deadlocks can not be resolved so one of them has to be killed.3. You have to enable the 1222 trace flag in order for the deadlock information to be written to the log.4. I use READ_COMMITTED_SNAPSHOT as a starting point. This at least prevents deadlocks from happening on writes/reads.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-12-15 : 10:47:36
|
| Appreciate that, Tara.For #3 and #4, helpful advice.For #1 and #2, I am still having some difficulty understanding things and I guess I didn’t really explain the situation that well.Here is what I did: I created 4 separate queries in 4 separate sessions. Sessions A, C and D was a simple update statement on Table1. Session B was a simple select statement on Table1. Each of these queries on their own would take about 3 minutes as they involved scans on Table1, a 20-million row table.I executed the 4 queries one after the other. Then I notice that Session A is blocking B and Session B is blocking C and D. If I understand correctly, this is Blocking rather than a Deadlock? After about 6 min. (no other queries are executed in the meantime), SQL Server terminates session C with:Msg 1205, Level 13, State 52, Line 1Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.I think the main solution here is to add the indexes so that the queries won’t take so long in the first place. But if anyone can shed some light on this: If this is truly not a deadlock then why does SQL Server refer to the terminated session as a Deadlock Victim? And is there any type of setting to allow SQL Server to wait longer or indefinitely rather than terminate a session when only blocking is involved rather than deadlock?The only other possibility is that can a Blocking situation somehow eventually escalate to a Deadlock? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
A few Deadlock Questions