ansaurus

Question

Can I be a deadlock victim if I'm not executing a query within a transaction?

Answer 1

A:

Just because you're not in a transaction doesn't mean you're not holding locks.

John Saunders 2010-01-13 07:32:54

I can see why you posted this, the title is poorly worded. However, he means that while in a transaction, he is not actually running a query. (He is in a transaction, but is not doing SQL stuff.) At least that is how I read it.

Vaccano 2010-01-13 07:39:01

As I said, what matters is not that he's in a transaction or not - what matters is the locks he holds, and the locks he may be waiting for. Depending on the details of the deadlock detection algorithm, it seems to me he could be considered part of the deadlock if he's holding a lock and "not doing anything" for too long.

John Saunders 2010-01-13 11:46:28

In terms of detection algorithm itself I tried to find anything that states lock hold time is part of the detection algorithms decision process. Is there any first hand experience or reference for this I can follow? Most of my information comes from: http://msdn.microsoft.com/en-us/library/ms178104.aspxIt states the deadlock detection process runs at 5 seconds intervals but thats about all I was able to find.

Einstein 2010-01-14 09:38:32

It's not a question of "lock hold time". If one thread is holding a lock for a period of time, then another thread may be blocked waiting for that lock. That second thread may be holding a lock that a third thread is waiting for. Again, it depends on the details of the deadlock detcection code, which I do not know. Some such algorithms will consider "C waits on B waits on A" to be a chain worth breaking. Others will require an actual cycle: C->B->C.

John Saunders 2010-01-14 15:27:58

Answer 2

+2 A:

NO

For a deadlock to occur all the participants in the deadlock chain must be waiting for a resource (a lock). If your connection is idle it means it doesn't execute a request, which implies it cannot be waiting.

As for other conditions that can kill your session I can think of at least three:

administrative operations that use WITH ROLLBACK_IMMEDIATE
a mirroring failover
intentional KILL <yourspid>, perhaps as a joke by your friendly DBA

Remus Rusanu 2010-01-13 07:37:42

Gotta love those friendly DBAs...

Rory 2010-01-13 07:55:52

Are you sure he has to be _waiting_? What if he's blocking another thread, and sitting on a WAITFOR?

John Saunders 2010-01-13 11:48:04

@John: Yes, it *must* be waiting for a resource. If T1 waits for resource A owned by T2 and T2 sits in WAITFOR then there *will be progress* when T2 resumes. For the deadlock chain to close T2 has to be waiting a resource, otherwise the deadlock detection will not see a complete circle and will not intervene. Also the full circle must be inside SQL, if T2 waits on a application semaphore owned by T1 then the circle completes outside of SQL and will again not be detected.

Remus Rusanu 2010-01-13 16:30:47

Thank you for your input. The sad thing is the reason I bring this up seems likely to be the result of a friendly DBA :(

Einstein 2010-01-14 08:13:26

@Remus: do you have a reference for this? I want to make sure it's not enough for the deadlock algorithm to see no progress if T2 is in the WAITFOR too long.

John Saunders 2010-01-14 15:29:39

@John: see http://msdn.microsoft.com/en-us/library/ms178104.aspx: "each task might acquire or wait to acquire a variety of resources" and then the list of resources follows. WAITFOR is not a resource (it cannot be owned by another task).

Remus Rusanu 2010-01-14 16:38:18

@Remus: that proves my point. One task may already hold a lock, then call WAITFOR. It's not _waiting_ for any resource - it just already holds a resource, and isn't giving it up.

John Saunders 2010-01-14 20:01:47

The deadlock graph *must* have a cycle, which means all threads involved must be waiting to *acquire* a resource, otherwise no cycle can form. If one thread *has* a resource and just sits idle (or in a `waitfor delay ...`, which is same as idle) then there is no deadlock: as soon as this thread resumes, it can eventually commit or rollback and *release* resources, freeing other threads. For a deadlock, the engine must detect that there *cannot* be any possible progress, ie the resource waiting list forms a cycle.

Remus Rusanu 2010-01-14 20:08:51

Answer 3

A:

Transactions may time out, is that what is happening.

As you have at least 1 (or more) update locks taken out and make be some read and table scan locks, you may be killed to help free up deadlocks created by other transactions. The deadlock recovery code in SQL Server is unlikely to be totally bug free and it is not normal to keep a transaction open for a long time on SQL Server. However I would not expect that to happen often.

Some system when they detach deadlock type problem, just start killing “long lived” transactions that have not done match work so as to free up locks. Just because you are not part of the deadlock loop, does not stop the system picking on you.

To understand what is going on in your case, you will have to use the Sql Server Profiler to collect all the locking and deadlock related events, as well as event about aborted connection and transactions etc. Good lack this will time some time and a good level of understanding of the profiler events you are looking at...

The detail of this sort of things are different between database vendors and versions of their database. However as it is considered bad design by most database vendors to have a transaction open for a long time, doing so tends to lead to problems and hit code paths that have not had the most testing effort.

Ian Ringrose 2010-01-13 08:09:28

This is the type of thing I'm looking for.. Can I ask what lead you to say processes not directly involved in a deadlock can be killed to resolve one? Is there offhand a reference or specific experience?

Einstein 2010-01-14 09:00:35

Answer 4

A:

Possible problems:

SQL Server only has a finite number of locks. It is possible to run out of locks.
Other resources are finite (e.g. memory, tempdb). Holding on to these resources could cause those resources to run out.
Transaction logs - the logical transaction logs cannot be freed for re-use if a transaction is open. The result could be a log that fills up. This problem could stop your process because it would halt the entire instance.

To consider:

CASCADE: DELETE may only have one table in the command, but the a CASCADE relationship may touch other tables.
Triggers: Triggers on the modified table may affect other tables.
DELETE and UPDATE commands may use the FROM clause which touch other tables. I've never seen this, but I would not rule it out.

Darryl Peterson 2010-01-13 13:19:07

ansaurus

tags:

views:

answers:

Can I be a deadlock victim if I'm not executing a query within a transaction?

related questions