[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

deadlocks

We have experienced deadlocks a couple of times with one of our third party applications which used sql 2000.
I've checked with the vendor and I've been told that the applications handles error 1222 by adding an entry to the applications log and dropping the transaction in which the error occurred.

I've also check the sql settings regarding lock time-outs and the timeout is set to -1.

questions:
is it possible to permanently set the lock time out to a different value? it defaults back to -1 when I logoff ?

What is a good time_out value, does it have to correspond with any setting on the application?

I'm I understanding this correctly that the deadlock is a result of lock which never timed out and by having the lock time_out set the locks should not deteriorate into a deadlock?
0
macareli
Asked:
macareli
2 Solutions
 
muzzy2003Commented:
No, you can't permanently set it, it defaults to -1 when a connection is opened. You can set it after the connection is opened with SET LOCK_TIMEOUT n, where n is the time in milliseconds.

Only a poorly written application would either (a) hold a lock for long enough, or (b) execute updates in such an order and with such a level of locking optimism/pessimism to be regularly generating deadlocks. If your third party application does this, then really it's up to the third party to fix it. They could set the lock timeout to a level they feel is appropriate, but really they need to rethink how their application works. I hope you didn't pay too much money for it ...
0
 
LowfatspreadCommented:
agree ....

a deadlock occurs when the Database has mutliple transactions all waiting to access the same resources...

e.g.

user A or transaction 1   holds a lock on resource B and wants to update Resource A

user B or transaction 2  holds a lock on resource d and resouce C and wants to update resource B

user C or transaction 3 holds a lock on resource A and wants to update resource C


thats a deadlock none of the transactions can proceed , so the DBMS eventually recognises the problem and start rolling back the transactions
until at least one of them can complete.....

its usually down to Poor design , or not enforcing a consistent "update" order when processing through result sets.

 
0
 
macareliAuthor Commented:
thanks.  so in our case this is a trading program and both times the same two applications seems to be involved.
I'm going by what I see on the results from sp_who2.  

If setting a lock timeout will not help in our case but rather it's something that needs to be addressed by the third party, what is the best way to gather the info I need to present the problem to them considering this is not a daily ocurrance.  
0
 
muzzy2003Commented:
The output from sp_who or sp_who2, as well as from sp_lock, would be a good place to start. You have to keep on your toes to look for the lock condition. You could possibly write a stored procedure to keep an eye on the output from sp_lock, looking for a non-zero in the blk column which would then trigger a complete dump from sp_who2 and sp_lock to a log file of some sort.
0
 
arbertCommented:
I would also run profiler and gather the SQL that happens to be executing at the time.....Lock information and process information doesn't really amount to much if you don't know what was executing....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now