Deadlocks even with SELECT FOR UPDATE

pike

DB2 UDB 8.1 FP7

We are getting intermittent deadlocks (911 RC 2) even when U row-lock
has been secured.

The transaction is as follows:
1) Select current application number value from table. To guarantee
uniqueness of the application number SELECT FOR UPDATE statement is
used.
2) Calculate new application number value.
3) Store new application number value back to database with UPDATE
statement.

Sample output from db2detaildeadlock event monitor:

Appl Id (e.g.): GA140148.D3B0.0D9EA9132135
Connect timestamp: 04/19/2006 15:21:37.161111
Appl Id of connection holding the lock: GA140148.D363.0D1E29132129
Lock wait start time: 04/19/2006 15:54:26.048986
Deadlock detection time: 04/19/2006 15:54:34.236609
Text : UPDATE applnr_sequence SET applnr = ? WHERE oproc_id = ? AND
subm_year = ?

Appl Id: GA140148.D363.0D1E29132129
Connect timestamp: 04/19/2006 15:21:29.787229
Appl Id of connection holding the lock: GA140148.D3B0.0D9EA9132135
Lock wait start time: 04/19/2006 15:54:25.786746
Deadlock detection time: 04/19/2006 15:54:40.416793
Text : SELECT oproc_id, subm_year, applnr, low, high FROM
applnr_sequence WHERE oproc_id = ? AND SUBM_YEAR = ? FOR UPDATE OF
applnr

I thought a SELECT FOR UPDATE, followed immediately by an UPDATE would
stop any transactions from sneaking in and grabbing a lock. Can someone
tell me what I'm missing?

Thank you.

Apr 20 '06 #1

Knut Stolze

pike wrote:

DB2 UDB 8.1 FP7

We are getting intermittent deadlocks (911 RC 2) even when U row-lock
has been secured.

The transaction is as follows:
1) Select current application number value from table. To guarantee
uniqueness of the application number SELECT FOR UPDATE statement is
used.
2) Calculate new application number value.
3) Store new application number value back to database with UPDATE
statement.

How is the calculation in step 2 done? If it's not too complicated or can
be wrapped in a UDF, then you could simply condense all three steps into a
single one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Apr 20 '06 #2

Serge Rielau

Knut Stolze wrote:

pike wrote:

DB2 UDB 8.1 FP7

We are getting intermittent deadlocks (911 RC 2) even when U row-lock
has been secured.

The transaction is as follows:
1) Select current application number value from table. To guarantee
uniqueness of the application number SELECT FOR UPDATE statement is
used.
2) Calculate new application number value.
3) Store new application number value back to database with UPDATE
statement.

How is the calculation in step 2 done? If it's not too complicated or can
be wrapped in a UDF, then you could simply condense all three steps into a
single one.

DB2 V8.1.4 for LUW and higher:
SELECT applnum FROM NEW TABLE(UPDATE T SET applnum = <blahexpr> WHERE
<blahpred>)

BTW, are you aware of APPLICATION_ID():
http://publib.boulder.ibm.com/infoce...n/r0011856.htm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 20 '06 #3

Knut Stolze

Serge Rielau wrote:

BTW, are you aware of APPLICATION_ID():

http://publib.boulder.ibm.com/infoce...n/r0011856.htm

Speaking of which, the application ID is valid for 99 years. This is not
yet in the docs but will be with Viper.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Apr 20 '06 #4

pike

Thanks Knut. Unfortunately, the logic in the calculation is found
'outside' the database so a searched UPDATE, for example, is not
possible.
Going back to my original point, based on the premise that the UPDATE
immediately follows the SELECT FOR UPDATE, it looks like the SELECT FOR
UPDATE is acquiring NS locks only. Could this be the case?

652) Deadlocked Connection ...
Deadlock ID: 19
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: GA140148.D3B0.0D9EA9132135
Appl Id of connection holding the lock: GA140148.D363.0D1E29132129
Lock wait start time: 04/19/2006 15:54:26.048986
Deadlock detection time: 04/19/2006 15:54:34.236609
Table of lock waited on : APPLNR_SEQUENCE
Type of lock: Row
Mode of lock: NS - Share (and Next Key Share) --- This
connection should at least have a U(pdate) lock as a result of the
preceding SELECT FOR UPDATE
Mode application requested on lock: X - Exclusive
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 15
Creator : NULLID
Package : SYSSN300
Cursor : SQL_CURSN300C15
Cursor was blocking: FALSE
Text : UPDATE applnr_sequence SET applnr = ? WHERE oproc_id = ?
AND subm_year = ?

665) Deadlocked Connection ...
Deadlock ID: 19
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: GA140148.D363.0D1E29132129
Appl Id of connection holding the lock: GA140148.D3B0.0D9EA9132135
Lock wait start time: 04/19/2006 15:54:25.786746
Deadlock detection time: 04/19/2006 15:54:40.416793
Table of lock waited on : APPLNR_SEQUENCE
Type of lock: Row
Mode of lock: U - Update --- Assuming all
previous associated units of work have been committed/rolled back, why
does this connection have a U lock already?
Mode application requested on lock: U - Update
Deadlocked Statement:
Type : Dynamic
Operation: Fetch
Section : 4
Creator : NULLID
Package : SYSSN300
Cursor : SQL_CURSN300C4
Cursor was blocking: FALSE
Text : SELECT oproc_id, subm_year, applnr, low, high FROM
applnr_sequence WHERE oproc_id = ? AND SUBM_YEAR = ? FOR UPDATE OF
applnr

Apr 24 '06 #5