macareli
asked on
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?
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?
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...