Suggest an Deadlock example

nikyojin · New User Joined: 05 Oct 2005 Posts: 94

Hi,
Can anybody suggest an Deadlock example.

priyesh.agrawal · Posted: Mon Jan 02, 2006 5:59 pm

Two queries trying to update a table same time.

Regards,

Priyesh.

nikyojin · New User Joined: 05 Oct 2005 Posts: 94

Hi Priyesh,
Is such an instance possible. When we say 2 queries trying to upate the same table at the same time ..a minor fraction of a time difference can result in the 1st or 2nd query gaining Exclusive Lock on the table which clearly inhibits the next query from gaining access. This can surely lead to a timeout situation.
Most of the instances I have faced Sqlcode -911 has been due to Timeout.Can u be more clear with the example...I really would like to know...Thanks for the response...

ragshere · New User Joined: 20 Dec 2004 Posts: 70

Hi,
The example given by Priyesh also results deadlock if both queries are running parellelly and trying to update rows in same page.
In this scenario you can get a doubt about how both witll get exclusive lock ..But if the locksize is row for the table and/or we are using cursor stability isolation level ,it is possible.

The Description of -911 is

The current unit of work has been rolled back due to
Deadlock or timeout.

So -911 results not only because of TIMEOUT but also for deadlock.

Thanks
Rags

radhakrishnan82 · Posted: Tue Jan 10, 2006 5:15 pm

A very good example of deadlock can be described thru this url:

check it out:

www.ibmmainframes.com/viewtopic.php?t=2091&highlight=deadlock

futuredba · New User Joined: 08 Jan 2006 Posts: 22 Location: Delhi

Hi nikyojin,

I would like to give u this example. Suppose a transaction is having X lock over table T1, after sometime it needs to fetch some data from table T2. But some other tranx is having X lock over table T2. now in this situation, 1st tranx will wait for T2 to be released by 2nd tranx. however, before releasing T2 by 2nd tranx, if it try to fetch some data from T1 which is already locked by 1st tranx, it will wait and... a deadlock will arrive....

nikyojin · New User Joined: 05 Oct 2005 Posts: 94

Thanks futuredba...The example fully clarifies deadlocks.....

kaurav · New User Joined: 19 Oct 2005 Posts: 7

The Deadlocks explained here involves two transactions. Another form of deadlock can also occur involving only one transaction. Consider this situation where one transaction obtains an exclusive lock on a table. Suppose the transaction needs another lock which is incompatible with the lock already obtained for the table and waits for the release of the exclusive lock which is held by the same transaction. This results in a deadlock.

Pls correct me if Iam wrong.

Thanks,

anandss6 · New User Joined: 12 Dec 2005 Posts: 7 Location: trivandrum

Hi,
If dead lock occurs how to resolve it? How can we know which transaction is using same table? can u please give me solution for the same.

martin9 · Posted: Tue Mar 14, 2006 4:58 pm

hy s.anand,

make the time, when a table is in a locking state,
as small as possible, and release it immediatley after,
thru COMMIT, SYNCPOINT whatever...

if there are two transactions which block each other,
then you have to redesign this... just updating the same
table by two different transactions at the same time,
is anyway not possible, is always serial.

martin9