911 deadlock

[Nigel Robbins]

Hi There,

I'm getting a deadlock when I have two clients running the following statement.
DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid

There is a compound index on file_uid / obj_uid.

The isolation level is UR and I have set DB2_RR_TO_RS=YES.

Any thoughts why I'm getting the deadlock ?

The output from my lock event monitor is below.

Thanks,
Nigel

--------------------------------------------------------------------------
EVENT LOG HEADER
Event Monitor name: EVMON1
Server Product ID: SQL08015
Version of event monitor data: 7
Byte order: BIG ENDIAN
Number of nodes in db2 instance: 1
Codepage of database: 819
Territory code of database: 1
Server instance name: db2inst2
--------------------------------------------------------------------------

--------------------------------------------------------------------------
Database Name: DMUDB80
Database Path: /home/db2inst2/db2inst2/NODE0000/SQL00002/
First connection timestamp: 07-27-2004 17:08:18.483578
Event Monitor Start time: 07-30-2004 06:52:35.826984
--------------------------------------------------------------------------

3) Connection Header Event ...
Appl Handle: 79
Appl Id: *LOCAL.db2inst2.04E3F0101636
Appl Seq number: 0011
DRDA AS Correlation Token: *LOCAL.db2inst2.04E3F0101636
Program Name : db2bp
Authorization Id: DB2INST2
Execution Id : db2inst2
Codepage Id: 819
Territory code: 1
Client Process Id: 20030
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-30-2004 06:16:36.423529

4) Connection Header Event ...
Appl Handle: 83
Appl Id: *LOCAL.db2inst2.0A1910105029
Appl Seq number: 0202
DRDA AS Correlation Token: *LOCAL.db2inst2.0A1910105029
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 1
Client Process Id: 41360
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-30-2004 06:50:29.994348

5) Connection Header Event ...
Appl Handle: 17
Appl Id: *LOCAL.db2inst2.05B8D7210823
Appl Seq number: 0001
DRDA AS Correlation Token: *LOCAL.db2inst2.05B8D7210823
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 1
Client Process Id: 23436
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-27-2004 17:08:23.262895

6) Connection Header Event ...
Appl Handle: 84
Appl Id: *LOCAL.db2inst2.0997F0110132
Appl Seq number: 0001
DRDA AS Correlation Token: *LOCAL.db2inst2.0997F0110132
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 0
Client Process Id: 39294
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-30-2004 07:01:32.357407

7) Connection Header Event ...
Appl Handle: 85
Appl Id: *LOCAL.db2inst2.0708B0110135
Appl Seq number: 0001
DRDA AS Correlation Token: *LOCAL.db2inst2.0708B0110135
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 0
Client Process Id: 28810
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-30-2004 07:01:35.710186

8) Deadlock Event ...
Deadlock ID: 2
Number of applications deadlocked: 2
Deadlock detection time: 07-30-2004 07:05:08.259603
Rolled back Appl participant no: 2
Rolled back Appl Id: *LOCAL.db2inst2.0997F0110132
Rolled back Appl seq number: : 0010

9) Deadlocked Connection ...
Deadlock ID: 2
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: *LOCAL.db2inst2.0997F0110132
Appl Seq number: 0016
Appl Id of connection holding the lock: *LOCAL.db2inst2.0708B0110135
Seq. no. of connection holding the lock: 0013
Lock wait start time: 07-30-2004 07:05:02.567912
Lock Name : 0x0002008B000036110000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 07-30-2004 07:05:08.259603
Table of lock waited on : FILE_OS_DETAILS
Schema of lock waited on : INTERMEDIATE
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: S - Share
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 13841
Application Handle: 84

10) Deadlocked Connection ...
Deadlock ID: 2
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: *LOCAL.db2inst2.0708B0110135
Appl Seq number: 0013
Appl Id of connection holding the lock: *LOCAL.db2inst2.0997F0110132
Seq. no. of connection holding the lock: 0016
Lock wait start time: 07-30-2004 07:05:02.571378
Lock Name : 0x0002008B000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 2
Hold Count : 0
Current Mode : IX - Intent Exclusive
Requesting lock as part of escalation: TRUE
Deadlock detection time: 07-30-2004 07:05:08.259603
Table of lock waited on : FILE_OS_DETAILS
Schema of lock waited on : INTERMEDIATE
Tablespace of lock waited on : USERSPACE1
Type of lock: Table
Mode of lock: IX - Intent Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 139
Application Handle: 85

--------------------------------------------------------------------------
Database Name: DMUDB80
Database Path: /home/db2inst2/db2inst2/NODE0000/SQL00002/
First connection timestamp: 07-30-2004 10:52:11.388320
Event Monitor Start time: 07-31-2004 10:00:35.568804
--------------------------------------------------------------------------

13) Connection Header Event ...
Appl Handle: 31
Appl Id: *LOCAL.db2inst2.0740D1135955
Appl Seq number: 0003
DRDA AS Correlation Token: *LOCAL.db2inst2.0740D1135955
Program Name : db2bp
Authorization Id: DB2INST2
Execution Id : db2inst2
Codepage Id: 819
Territory code: 1
Client Process Id: 29708
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-31-2004 09:59:55.391579

14) Connection Header Event ...
Appl Handle: 29
Appl Id: *LOCAL.db2inst2.06C511135825
Appl Seq number: 0005
DRDA AS Correlation Token: *LOCAL.db2inst2.06C511135825
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 1
Client Process Id: 27728
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-31-2004 09:58:25.280550

15) Connection Header Event ...
Appl Handle: 30
Appl Id: *LOCAL.db2inst2.09B651135834
Appl Seq number: 0001
DRDA AS Correlation Token: *LOCAL.db2inst2.09B651135834
Program Name : dmappsrv
Authorization Id: DMSYS
Execution Id : dmsys
Codepage Id: 819
Territory code: 1
Client Process Id: 39780
Client Database Alias: DMUDB80
Client Product Id: SQL08015
Client Platform: AIX
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 07-31-2004 09:58:34.785527

16) Deadlock Event ...
Deadlock ID: 2
Number of applications deadlocked: 2
Deadlock detection time: 07-31-2004 10:01:54.206437
Rolled back Appl participant no: 2
Rolled back Appl Id: *LOCAL.db2inst2.09B651135834
Rolled back Appl seq number: : 0010

17) Deadlocked Connection ...
Deadlock ID: 2
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: *LOCAL.db2inst2.09B651135834
Appl Seq number: 0016
Appl Id of connection holding the lock: *LOCAL.db2inst2.06C511135825
Seq. no. of connection holding the lock: 0019
Lock wait start time: 07-31-2004 10:01:47.639570
Lock Name : 0x0002008B0000060C0000000052
Lock Attributes : 0x00000010
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 07-31-2004 10:01:54.206437
Table of lock waited on : FILE_OS_DETAILS
Schema of lock waited on : INTERMEDIATE
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: S - Share
Node lock occured on: 0
Lock object name: 1548
Application Handle: 30

18) Deadlocked Connection ...
Deadlock ID: 2
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: *LOCAL.db2inst2.06C511135825
Appl Seq number: 0019
Appl Id of connection holding the lock: *LOCAL.db2inst2.09B651135834
Seq. no. of connection holding the lock: 0016
Lock wait start time: 07-31-2004 10:01:47.661616
Lock Name : 0x0002008B00000C070000000052
Lock Attributes : 0x00000010
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 07-31-2004 10:01:54.206437
Table of lock waited on : FILE_OS_DETAILS
Schema of lock waited on : INTERMEDIATE
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: S - Share
Node lock occured on: 0
Lock object name: 3079
Application Handle: 29

[Mark A]

"Nigel Robbins" <ni***********@bigfoot.com> wrote in message
news:fb**************************@posting.google.c om...

Hi There,

I'm getting a deadlock when I have two clients running the following statement. DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid

There is a compound index on file_uid / obj_uid.

The isolation level is UR and I have set DB2_RR_TO_RS=YES.

Any thoughts why I'm getting the deadlock ?

The output from my lock event monitor is below.

Thanks,
Nigel

DB2 version 8 improves concurrency problems with next key locking by using
"type 2" indexes. Doing a commit as soon as possible after the delete might
help your situation. If not, there are other options below:

In version 7, you might try the following as a single unit of work:

SELECT uid FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid
for update;
DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid;

Another possibility is the following sequence:

declare cursor c1 as SELECT uid FROM intermediate.file_os_details WHERE
file_uid = ? AND obj_uid for update;
open cursor c1;
fetch c1 into ....;
delete where current of cursor c1;
(if the index on file_uid / obj_uid is not unique, you will have to loop
through the cursor using fetch and delete all the rows)
close cursor c1;

(the above syntax may not be exactly correct, but you should get the idea).

[Nigel Robbins]

Hi Mark,

Thanks for your input.

I've decided to change the code to trap the deadlock / lock timeout
and retry instead of changing individual SQL statements.

But it looks as though DB2 is locking the whole table instead of the
rows it needs to.
Do you know if there any way to prevent this ?
Note that locklist is set such that lock escalation is not occurring.

Thanks,
Nigel

"Mark A" <no****@nowhere.com> wrote in message news:<Q2****************@news.uswest.net>...

"Nigel Robbins" <ni***********@bigfoot.com> wrote in message
news:fb**************************@posting.google.c om...

Hi There,

I'm getting a deadlock when I have two clients running the following

statement.

DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid

There is a compound index on file_uid / obj_uid.

The isolation level is UR and I have set DB2_RR_TO_RS=YES.

Any thoughts why I'm getting the deadlock ?

The output from my lock event monitor is below.

Thanks,
Nigel

DB2 version 8 improves concurrency problems with next key locking by using
"type 2" indexes. Doing a commit as soon as possible after the delete might
help your situation. If not, there are other options below:

In version 7, you might try the following as a single unit of work:

SELECT uid FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid
for update;
DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid;

Another possibility is the following sequence:

declare cursor c1 as SELECT uid FROM intermediate.file_os_details WHERE
file_uid = ? AND obj_uid for update;
open cursor c1;
fetch c1 into ....;
delete where current of cursor c1;
(if the index on file_uid / obj_uid is not unique, you will have to loop
through the cursor using fetch and delete all the rows)
close cursor c1;

(the above syntax may not be exactly correct, but you should get the idea).

[Mark A]

"Nigel Robbins" <ni***********@bigfoot.com> wrote in message
news:fb**************************@posting.google.c om...

Hi Mark,

Thanks for your input.

I've decided to change the code to trap the deadlock / lock timeout
and retry instead of changing individual SQL statements.

But it looks as though DB2 is locking the whole table instead of the
rows it needs to.
Do you know if there any way to prevent this ?
Note that locklist is set such that lock escalation is not occurring.

Thanks,
Nigel

I am not sure you can trap a deadlock (reason code 2) and retry, but you can
trap a lock timeout (reason code 68). The locktimeout database parm
determines how many seconds DB2 will wait before returning the -911 (reason
code 68). I think the default is infinity. Most DBA's set this to about 30
seconds.

It sounds like you may have lock escalation from row level to table level.
DB2 will start escalating row locks to table locks if the locklist memory
gets filled up, or more than x percent of the locklist memory is used by one
application (where x = maxlocks parameter). Most installations should
dramatically increase the default locklist size. You can experiment with the
maxlocks, and a value of 60 (percent) or more will definitely discourage
lock escalation to the table level.

Keep in mind that lock escalation to table level actually helps performance
for some jobs (even though it decreases concurrency) since DB2 will spend
less time taking locks for each individual row. This is more noticeable in
utility jobs and large table scans.