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. 4 9578
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Fredrik M?ller |
last post by:
Hi All,
I have read about deadlocks here on Google and I was surprised to read
that an update and a select on the same table could get into a
deadlock because of the table's index. The update...
|
by: WangKhar |
last post by:
Gents,
I have come into a system that uses a secondary table to generate (for
want of a better word) Identities.
eg
create table myidents
( name sysname not null, ident int not null)
|
by: Hasan |
last post by:
Hi
I'm having a problem with deadlocks in a table in SQL server when
trying to update it through Biztalk 2004. There is no problem when I
use the same Biztalk solution to update a similar dummy...
|
by: Matt White |
last post by:
We've found deadlocks in the trace file that were not captured by our
Powerbuilder application. Some deadlocks are trapped or, at least,
reported to the user as a db error, and others are...
|
by: Leaf |
last post by:
Greetings,
I've been reading with interest the threads here on deadlocking, as I'm
finding my formerly happy app in a production environment suddenly
deadlocking left and right. It started...
|
by: Marcus |
last post by:
Hello all,
I am trying to figure out when it is appropriate to use shared and
exclusive locks with InnoDB. Using shared locks (lock in share mode), I
can easily create a scenario with 2 clients...
|
by: Ben |
last post by:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending...
|
by: PeterC |
last post by:
We're getting numerous deadlocks in a multi-user system where users are coming in and updating their own data.
In our troubleshooting/traces, the deadlocks seem to be coming from lock contention...
|
by: bobdurie |
last post by:
Hi,
We have a Java application that runs against a variety of backends
including Oracle and MSSql 2000 and 2005. Our application has a
handful of active tables that are constantly being...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |