Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Suggest an Deadlock example

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Mon Jan 02, 2006 3:18 pm    Post subject: Suggest an Deadlock example
Reply with quote

Hi,
Can anybody suggest an Deadlock example.
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Jan 02, 2006 5:59 pm    Post subject: Re: DeadLock
Reply with quote

Two queries trying to update a table same time.

Regards,

Priyesh.
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Mon Jan 02, 2006 7:51 pm    Post subject: Re: DeadLock
Reply with quote

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...
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Tue Jan 10, 2006 3:58 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
radhakrishnan82

Active User


Joined: 31 Mar 2005
Posts: 436
Location: chennai, India

PostPosted: Tue Jan 10, 2006 5:15 pm    Post subject:
Reply with quote

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

check it out:

http://www.ibmmainframes.com/viewtopic.php?t=2091&highlight=deadlock
Back to top
View user's profile Send private message
futuredba

New User


Joined: 08 Jan 2006
Posts: 22
Location: Delhi

PostPosted: Tue Jan 10, 2006 8:05 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Wed Jan 11, 2006 9:13 am    Post subject: Re: Suggest an Deadlock example
Reply with quote

Thanks futuredba...The example fully clarifies deadlocks.....
Back to top
View user's profile Send private message
kaurav

New User


Joined: 19 Oct 2005
Posts: 7

PostPosted: Thu Jan 19, 2006 8:40 pm    Post subject: Re: Suggest an Deadlock example
Reply with quote

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,
Back to top
View user's profile Send private message
anandss6

New User


Joined: 12 Dec 2005
Posts: 7
Location: trivandrum

PostPosted: Tue Mar 14, 2006 4:31 pm    Post subject: how t o solve dead lock
Reply with quote

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.
Back to top
View user's profile Send private message
martin9

Active User


Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland

PostPosted: Tue Mar 14, 2006 4:58 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts Can a select query participate in a d... apvardhini DB2 1 Fri May 29, 2015 12:39 pm
No new posts Related to Deadlock issue sivareddy123 DB2 2 Fri Jan 30, 2015 3:59 pm
No new posts table scan can cause deadlock over RID? dejunzhu DB2 10 Fri Jul 04, 2014 8:36 am
No new posts Creating IDMS Deadlock seahawk789 IDMS/ADSO 1 Fri Apr 11, 2014 5:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us