Hi,
We are using DB2 UDB 8.1 as the backend for our e-commerce application(Websphere commerce server).For quite some time we are experiencing deadlocks in DB2 and we are using the tool db2pe to detect and monitor the deadlock events.
Most of the deadlocks which are happening are related to UPDATE followed by SELECT statements. It means in the report which is generated we see there is a UPDATE statement on some X table and another transaction is using SELECT clause on the same X table and because of which deadlock happens and 2 transaction is rolled backed.
When we saw the report I was stunned by the lock types and lock modes on each of these UPDATE and SELECT statements. On SELECT statements there was Exclusive lock and on UPDATE it was NEXT-KEY SHARE LOCK.
I am not clear on how come UPDATE and SELECT statements can result into deadlock.I am attaching the report.In the report you will find 4 transactions 2 are related to XSOLDTO table which is used in UPDATE clause and 2 ORGENTITY table on which SELECT clause is used.In the SELECT statement XSOLDTO is one of the tables.
If anybody can make be understand how come deadlock happens between UPDATE and SELECT clause and also on how come a select clause can have EXCLUSIVE lock.

If you are really using DB2 LUW 8.1, then you need to upgrade to 8.2 by applying the latest fixpack to 8.1 from the IBM fixpack website (there is no charge for this). Then you need to do an offline reorg of all tables. This will convert all indexes from type 1 and type 2, which improves concurrency of most applications by reducing the Next-key Share Locks.

A select statement does not take an exclusive lock, unless it is select for update. Unfortunately, the information you provided is not detailed enough for me to give any other analysis, but maybe others can see something to help you.

If you upgrade to the latest 8.2 fixpack, you can create a detailed deadlock event monitor, which will provide better information on what is happening during a deadlock (when you run the event monitor formatting report on the deadlock files). Just in case you are not aware, a deadlock is -911 RC=2, whereas a locktimeout is -911 RC=68. You will not be able to get a detailed report on a locktimeout in version 8.2 (version 9.5 has very good locktimeout reporting if you set it up correctly).

I am not sure why you are using version 8, since it is long out of support by IBM.

Hi Marcus,
Thanks for the update.
The SELECT statement which we are using is not having any SELECT FOR UPDATE clause.
It is taking the default isolation level as CS, Will this result into EXCLUSIVE lock.
Did you check the report which i uploaded.
Actually version 8.1 is being used by client and they are in no mood to change it now so we have to live with it.

Thanks
Ravi

I am not sure on what actually NEXT KEY SHARE lock means.Can anybody explain me with a small example.

Yes we are getting error with " -911 RC=2" which is a deadlock.
Still not clear on how come UPDATE and SELECT (without FOR UPDATE CLAUSE) can result into deadlock

You don't need to know that information. If you disagree, then google it. If you are indeed running 8.1 then there is no excuse for not upgrading to 8.2 which can be accomplished by installing the latest 8.1 fixpack, which is about FP 18 (or something close to that).

In DB2, an application cannot update a row if another application has a an active S lock (share lock from a SELECT).

Hi Markus,
Thanks for the information but how can we achieve the following concurrency requirement in DB2

Even if SELECT ( without FOR UPDATE CLAUSE) or UPDATE is happening on table rows as part of one transaction, in another transaction I should be able to either UPDATE or SELECT on the same table rows.
Means SELECT + UPDATE should be possible and UPDATE + SELECT should be possible.
What if I suffic SELECT query with "WITH UR" clause.
Will this avoid deadlocks conditions which we are facing ( Update followed by Select ) or ( select followed by update).

You might avoid a deadlock by changing the select + update to select for update + update. This will casue a lock wait for update + select, but probably not a deadlock. To avoid the locking problem altogether, you could try WITH UR on the select (when you do slelect + Update).

DB2 locking is quite a bit different than Oracle, so what you think should happen, may be different than what happens.

Hi Marcus,
Thanks a lot for providing the valuable information.

We are having 2 trypes of queries.
1) transactional queries
2) report queries

For report queries we can easily suffix WITH UR clause to the SELECT statements and avoid the deadlock issue.Since the reports are not that mission critical we can show the uncommited data which results from WITH UR clause.

Now for transacational queries we want to avoid the lock timeouts as well as deadlocks to maximum extent.Now if we use WITH UR clause there is a possibility of reading uncomitted data and if we use SELECT FOR UPDATE +UPDATE combination then it may result into timeouts.
So what should be the best approach for transactional level queries.Can you please clarify?

Thanks
Ravi

oh,yes,i think the profile var. value is small.change it!

Sorry,I am not able to get what I need to change and where?