Re: Automatic deadlock detection

... I don't know what's the condition for DB2 LUW, for db2/400 you have to have in mind:
- default for the record wait is 60
- default for a table wait is 0 (they must have smoked something good)
- lock level serializable doesn't work, if RPG (or COBOL) record levele access is used concurrent
- you should avoid escalation of the record lock from share read to no shared update (be carefull with isolation level uncommitted read)
- avoid select into
- best strategy would be deadlock prevention (order your ressources and get it in a strict sequenze)

D*B

--------------------------------------------------
From: "Jonathon Creenaune" <jonathon@xxxxxxxxxxxxxx>
Sent: Wednesday, January 12, 2011 1:32 AM
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Cc: "Paul Grycuk" <paul.grycuk@xxxxxxxxx>
Subject: Automatic deadlock detection

Does DB2 for iseries support deadlock detection? I can't get it to work. I'm
manually running the following sql commands in different transactions:


T1: set transaction isolation level RS;
T1: select * from aschema.atable where id = 1;

T2: set transaction isolation level RS;
T2: select * from aschema.atable where id = 1;

T1: update aschema.atable set acolumn = acolumn where id = 1; -- this
blocks, which is correct
T2: update aschema.atable set acolumn = acolumn where id = 1; -- this
blocks, which is correct


On DB2 LUW, after 10 seconds I get sql 911 reason 2, indicating that a
deadlock has been detected and one of the transactions has been rolled back.
The other one then proceeds.

On DB2 for iseries, after 30 seconds (the table's record lock wait time) I
get sql 913, indicating that the record lock wait time has been reached. No
deadlock is detected. I'm wondering if there's something I need to configure
to enable deadlock detection.

I'm running the tests on a v6r1 machine through jt400 6.1.

Any help is much appreciated,

Jonathon
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.