Re: Preventing deadlocks
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/06/04
- Next message: Brandon Slezak: "Locking and Transactions /w Linked Servers"
- Previous message: mohaaron_at_gmail.com: "DTS copy all objects failed with invalid column name"
- In reply to: Maury Markowitz: "Preventing deadlocks"
- Next in thread: Maury Markowitz: "Re: Preventing deadlocks"
- Reply: Maury Markowitz: "Re: Preventing deadlocks"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 6 Dec 2004 17:15:47 -0500
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
- Next message: Brandon Slezak: "Locking and Transactions /w Linked Servers"
- Previous message: mohaaron_at_gmail.com: "DTS copy all objects failed with invalid column name"
- In reply to: Maury Markowitz: "Preventing deadlocks"
- Next in thread: Maury Markowitz: "Re: Preventing deadlocks"
- Reply: Maury Markowitz: "Re: Preventing deadlocks"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
