Preventing deadlocks

I've noticed a couple of very nasty deadlocks on our new server lately, and
I'd like some advice on how to "ignore" them. What's happening is that
someone's machine and/or Access dies into the VB debugger in the middle of a
SELECT, and the process locks. This pretty much stops all access to the
server, and ends backups as well!
I don't see any way to prevent these problems on the user machine, so how to
I ignore them on the server? Is there some timeout I've forgot to set when we
set up the machine?
Also, when I attempt to kill the process in Manager it seems to either not
work, or take a very long time. Is this normal?
If it takes a long time to kill the process, it's probably doing a rollback.
"Maury Markowitz" wrote:

> I've noticed a couple of very nasty deadlocks on our new server lately, and
> I'd like some advice on how to "ignore" them. What's happening is that
> someone's machine and/or Access dies into the VB debugger in the middle of a
> SELECT, and the process locks. This pretty much stops all access to the
> server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so how to
> I ignore them on the server? Is there some timeout I've forgot to set when we
> set up the machine?
> Also, when I attempt to kill the process in Manager it seems to either not
> work, or take a very long time. Is this normal?
|||Maury Markowitz wrote:
> I've noticed a couple of very nasty deadlocks on our new server
> lately, and I'd like some advice on how to "ignore" them. What's
> happening is that someone's machine and/or Access dies into the VB
> debugger in the middle of a SELECT, and the process locks. This
> pretty much stops all access to the server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so
> how to I ignore them on the server? Is there some timeout I've forgot
> to set when we set up the machine?
> Also, when I attempt to kill the process in Manager it seems to
> either not work, or take a very long time. Is this normal?
A deadlock does not kill a connection. It only raises an error. The
server doesn't really do anything other than select the deadlock victim
and raise the error. The rest is up to the application. If you have an
application that is encountering a deadlock and locking up, then the
connection is likely remaining connected to the server. However, the
transaction is automatically rolled back by SQL Server, so it's not
clear what is causing your issue.
Are you sure you are having deadlock problems and not blocking issues?
I'm starting to think from your post that you may have confused the
terms. A deadlock is when two or more spids are requesting access to
objects that are locked by the other spids. In effect, they would all
block each other forever. So, SQL Server terminates one of the
transactions (the deadlock victim). In a blocking situation, one spid
requests a lock on a resource that is locked by another spid. Unless you
specify a lock timeout in your code, the first spid will wait forever
for the resource.
So which situation is yours?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:
> A deadlock does not kill a connection. It only raises an error.
That's the problem. The deadlock is occuring because one application died
out on the network and is holding its query open. Then when someone else
issues the same query, it gets stuck. At least I think that's what's
happening.

> connection is likely remaining connected to the server. However, the
> transaction is automatically rolled back by SQL Server, so it's not
> clear what is causing your issue.
Hmmm.

> Are you sure you are having deadlock problems and not blocking issues?
You're right, I'm having blocking issues.

> specify a lock timeout in your code, the first spid will wait forever
> for the resource.
This sounds like the solution. Is this something that can be set globally in
Access? We set up the system to have 60 second timeouts, but this error
appears to occur when the Access app in question has "crashed" into the VB
Debugger.
Maury
|||Maury Markowitz wrote:
> "David Gugick" wrote:
> That's the problem. The deadlock is occuring because one application
> died out on the network and is holding its query open. Then when
> someone else issues the same query, it gets stuck. At least I think
> that's what's happening.
>
That's a blocking issue, not a deadlocking issue as you now know. SQL
Server will eventually release those locks, but it can take some time
until it clears up the connection. Why is the application dying in the
first place?
There is a lock timeout and query timeout that you should be able to set
from your Access code. The lock timeout determines how long a process
will wait on a locked resource before the query is automatically
cancelled. I believe you'll still need to issue a rollback if that
happens.
I don't know enough about Access to help more than that.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:
> That's a blocking issue, not a deadlocking issue as you now know. SQL
> Server will eventually release those locks, but it can take some time
> until it clears up the connection. Why is the application dying in the
> first place?
For different reasons each time, typically due to edge cases that

> There is a lock timeout and query timeout that you should be able to set
> from your Access code.
Ok, I'm going to poke about and see if I can find this. The locks in
question are lasting overnight though, so I don't think it's quite that
simple.
Is there some setting on the server side I can set for this? None of our
queries take more than 30 seconds (my self-imposed limit) so setting this to
5 minutes or something would be entirely reasonable.
|||Maury Markowitz wrote:
> "David Gugick" wrote:
> For different reasons each time, typically due to edge cases that
>
> Ok, I'm going to poke about and see if I can find this. The locks in
> question are lasting overnight though, so I don't think it's quite
> that simple.
> Is there some setting on the server side I can set for this? None of
> our queries take more than 30 seconds (my self-imposed limit) so
> setting this to 5 minutes or something would be entirely reasonable.
If the locks are remaining overnight, it could be because the
application is not throwing an exception and dying, but is locked up
with its connection active on the server. Is this a possibility? I think
I meant setting the lock timeout for the clients who are being locked
out of the system, not the long running process. For that, a query
timeout could help depending on the situation. At least if you use a
lock timeout when this process seems to lock other users out, they 'll
get a nice message saying that they should call support because the
system is having issues and then you can quickly address the problem. It
doesn't prevent the problem, which is either SQL based or application
based.
David Gugick
Imceda Software
www.imceda.com