By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Deadlock due to table level exclusive lock

P: n/a
Please correct me if I am wrong
1. no 2 processes can have exclusive lock on same object (by object,
same row or same table)
2. on deadlock incident between 2 processes only one of them will be
cancelled by db2 to let the other finish.

Now, can there be an instance where 2 processes have 'EXCLUSIVE LOCK'
on same table, and are waiting on the same 'EXCLUSIVE LOCK' held by
the other agent to be relinquished. They both are cancelled by db2
using the 911 sqlstate.
It has been continuously happening to me, and it perplexes me.

This happenes because I use the 'lock table <table_namein exclusive
mode' statement in a stored procedure, and I call that SP in three
applications at the same instance. Can this happen? I also got event
monitor output, and am analysing the same.

People write my 'problem ticket' off, saying this is a normal deadlock
issue. But I don't think so. Please help.
Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
People write my 'problem ticket' off, saying this is a normal deadlock
issue. But I don't think so. Please help.
People? If you are talking about support, are you aware that you can
escalate to the duty manager if you don't like the answer you're getting?

What you describe should not happen. It is more likely that your
information is incomplete, than that there is a defect.. But on enever
knows.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #2

P: n/a
Arun Srinivasan wrote:
Please correct me if I am wrong
1. no 2 processes can have exclusive lock on same object (by object,
same row or same table)
2. on deadlock incident between 2 processes only one of them will be
cancelled by db2 to let the other finish.

Now, can there be an instance where 2 processes have 'EXCLUSIVE LOCK'
on same table, and are waiting on the same 'EXCLUSIVE LOCK' held by
the other agent to be relinquished. They both are cancelled by db2
using the 911 sqlstate.
It has been continuously happening to me, and it perplexes me.

This happenes because I use the 'lock table <table_namein exclusive
mode' statement in a stored procedure, and I call that SP in three
applications at the same instance. Can this happen? I also got event
monitor output, and am analysing the same.
From this brief description we should be able to answer your question?
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Jun 27 '08 #3

P: n/a
Ian
Arun Srinivasan wrote:
Please correct me if I am wrong
1. no 2 processes can have exclusive lock on same object (by object,
same row or same table)
2. on deadlock incident between 2 processes only one of them will be
cancelled by db2 to let the other finish.

Now, can there be an instance where 2 processes have 'EXCLUSIVE LOCK'
on same table, and are waiting on the same 'EXCLUSIVE LOCK' held by
the other agent to be relinquished. They both are cancelled by db2
using the 911 sqlstate.
It has been continuously happening to me, and it perplexes me.
What reason code is DB2 returning to each agent?

I don't think it's possible to get SQL0911N RC=2 (deadlock) from both
agents.

However, if you are getting SQL0911N RC=68 (lock timeout) from one
agent and SQL0911N RC=2 (deadlock) from the other agent, this could
happen - theoretically - if LOCKTIMEOUT is set to the same value as
DLCHKTIME. I think it would be highly unlikely (both agents would
have to request the other's locks within microseconds of each other).

Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.