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?
macareliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.