Re: Automatic deadlock detection

Thanks for the replies. I'd initially believed the WAITRCD was at 30s but it
was in fact 60s as Dieter pointed out.

Charles - I think you've got it, many thanks. Tested with longer WAITRCD and
received the same as you - one session receives SQL0913 early, the other
waits until WAITRCD *or* succeeds if I rollback / commit the other session.

So there is deadlock detection and it is being performed. Does anyone know
the interval at which it's performed, and whether this is configurable? Have
had a look around but no luck.


Some other info ...
- The situation is absolutely a deadlock situation. In isolation level RS:

T1: select * from aschema.atable where id = 1; -- gets a READ record lock to
prevent nonrepeatable reads
T2: select * from aschema.atable where id = 1; -- gets a READ record lock to
prevent nonrepeatable reads

T1: update aschema.atable set acolumn = acolumn where id = 1; -- tries to
get UPDATE record lock; blocked by READ record lock; waits for WAITRCD time
for lock
T2: update aschema.atable set acolumn = acolumn where id = 1; -- tries to
get UPDATE record lock; blocked by READ record lock; waits for WAITRCD time
for lock

This is a deadlock (and despite my earlier accusations, iseries db2 is
detecting this).

- 911 & 913 ... yep 911 doesn't exist on iseries. That's ok, the only
difference between them is that 911 means the dbms has rolled back the
transaction, and 913 means that the dbms has not and the application has the
choice of rolling back or doing some other recovery. The 913 infocenter
error description (
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzala/rzalaml.htm#messages__SQL0913)
is maybe a tiny bit misleading, it doesn't mention being thrown due to a
deadlock, but it seems it is.




On 13 January 2011 08:09, <dieter.bender@xxxxxxxxxxxx> wrote:

Your scenario doesn't fit the definition of a deadlock I don't
think...doesn't a deadlock require more than one resource?

lock escalation needs only one ressource for a deadlock:

transaction 1 reads a record with select into with isolation level
uncommited read
transaction 2 reads the same record with the same isolation
now we have the situation, that none of both could update the record!!!

@ wait file 30: the grass isn't as good as it used to be, but they are
still
smoking !!!

60 seconds for a record lock is by fare too much, a record is locked for
milliseconds, or as long as a silly programmer enabled a user to lock a
record, while the programm is waiting for the next key stroke of the enter
key from the user. or forever...
waiting for a record longer than for disappearing of a tablelock, what
situation could someone have in mind for this nonsens???

D*B

Though I tried it with two tables, and still only get the SQL 913

error...

Perhaps you have to have WAITRCD = *NOMAX, for the system to kick out
the deadlock error?

Charles



On Tue, Jan 11, 2011 at 7:32 PM, Jonathon Creenaune
<jonathon@xxxxxxxxxxxxxx> wrote:

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.

--
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.

--
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.