Deadlock or timeout on executeBatch()

[Javauser]

Hi there

we are getting the following db2 error on executeBatch() method that
inserts n rows on a table (where n is between 50 and 200).

[IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been
rolled back because of a deadlock or timeout. Reason code "2".
SQLSTATE=40001
errorCode : 911

Problem raises just when two or more people try to insert , at the
same time, on the table..
with one user there is no problem and the insert batch is perfectly
done by db2.

The batch java code is very simple:

connection = ds.getConnection("bla","bla");
connection.setAutoCommit(false);
myStatement = connection .createStatement();
for(int i=0;i<inSqlCommand1.size();i++) {
myStatement.addBatch((String)inSqlCommand1.get(i)) ;
}
updateCounts = myStatement.executeBatch();
if(Ok) connection.commit();
else connection.rollback();

Any hints?

tnx
M.

[Eric Herber]

If you are working with a DB2 V8.1 system, there is
a deadlock event monitor automatically generated
when you create a database.

The event monitor is called DB2DETAILDEADLOCK and is
activated by default.

You might flush the event monitor and analyze your
deadlock situation with the db2evmon tool.

Example for Unix based systems:

- cd <INSTANCE_HOME>/<INSTANCE>/NODE0000/SQL0000#/db2event/db2detaildeadlock
- db2 'flush event monitor db2detaillock'
- db2evmon -d <dbname> db2detaildeadlock

This might give you an idea what is causing the deadlock situation.

HTH.
Best regards

Eric

Javauser wrote:

Hi there

we are getting the following db2 error on executeBatch() method that
inserts n rows on a table (where n is between 50 and 200).

[IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been
rolled back because of a deadlock or timeout. Reason code "2".
SQLSTATE=40001
errorCode : 911

Problem raises just when two or more people try to insert , at the
same time, on the table..
with one user there is no problem and the insert batch is perfectly
done by db2.

The batch java code is very simple:

connection = ds.getConnection("bla","bla");
connection.setAutoCommit(false);
myStatement = connection .createStatement();
for(int i=0;i<inSqlCommand1.size();i++) {
myStatement.addBatch((String)inSqlCommand1.get(i)) ;
}
updateCounts = myStatement.executeBatch();
if(Ok) connection.commit();
else connection.rollback();

Any hints?

tnx
M.

--
IT-Consulting Herber
Mobile: [phone]
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[Javauser]

Hi Eric and thanks for your help..
unluckily we are using 7.2.3 version of Db2.

Anyway i've tried to snapshot a lock monitor and i have obtained this
log's snippet..

Database Lock Snapshot

Database name = BANKTEST

Database path = D:\DB2\NODE0000\SQL00017\

Input database alias = BANKTEST

Locks held = 2036

Applications currently connected = 4

Agents currently waiting on locks = 2

Snapshot timestamp = 01-09-2004
11:20:27.745603

Application handle = 17

Application ID =
0A0A0A68.D109.040109082850

Sequence number = 0001

Application name = java.exe

Authorization ID = DB2ADMIN

Application status = Lock-wait

Status change time = Not Collected

Application code page = 1252

Locks held = 563

Total wait time (ms) = Not Collected

List Of Locks

Lock Object Name = 0

Node number lock is held at = 0

Object Type = Internal V Lock

Tablespace Name =

Table Schema =

Table Name =

Mode = S

Status = Granted

Lock Escalation = NO

Lock Object Name = 293378

Node number lock is held at = 0

Object Type = Row

Tablespace Name = USERSPACE1

Table Schema = BANKTEST

Table Name = ROAMS

Mode = W

Status = Granted

Lock Escalation = NO
Application handle = 25

Application ID =
0A0A0A68.180A.040109101223

Sequence number = 0001

Application name = java.exe

Authorization ID = DB2ADMIN

Application status = UOW Waiting

Status change time = Not Collected

Application code page = 1252

Locks held = 0

Total wait time (ms) = Not Collected

Application handle = 26

Application ID =
0A0A0A68.190A.040109101224

Sequence number = 0001

Application name = java.exe

Authorization ID = DB2ADMIN

Application status = UOW Executing

Status change time = Not Collected

Application code page = 1252

Locks held = 910

Total wait time (ms) = Not Collected

List Of Locks

Lock Object Name = 0

Node number lock is held at = 0

Object Type = Internal V Lock

Tablespace Name =

Table Schema =

Table Name =

Mode = S

Status = Granted

Lock Escalation = NO

Lock Object Name = 333057

Node number lock is held at = 0

Object Type = Row

Tablespace Name = USERSPACE1

Table Schema = BANKTEST

Table Name = WEARS

Mode = W

Status = Granted

Lock Escalation = NO


i have noticed that problem is related to autocommit..if i turn the
autocommit to true everything works fine.

Any hints would be appreciated.

M.

[Mark A]

"Javauser" <ke******@mailinator.com> wrote in message
news:68**************************@posting.google.c om...

Hi Eric and thanks for your help..
unluckily we are using 7.2.3 version of Db2.

Anyway i've tried to snapshot a lock monitor and i have obtained this
log's snippet..

If autocommit is turned off, you should do explicit commits in the
application to release the locks. You might also check to see that your are
using CS isolation level.

[Javauser]

> If autocommit is turned off, you should do explicit commits in the

application to release the locks. You might also check to see that your are
using CS isolation level.

Hi and thanks for reply..
if u watch the snippet above , at the bottom i do commits on
connection.

anyway i created an event monitor for deadlock and i found that is
caused by an update..sorry..i believed transaction was formed just by
inserts.

So..i do n inserts and 1 update on table (where id is primary key)

Application 1 does:
PREPARE
EXECUTE insert into table (id,id_pres,something) values (1,30,'test')
EXECUTE insert into table (id,id_pres,something) values (2,30,'test')
EXECUTE insert into table (id,id_pres,something) values (3,30,'test')
EXECUTE insert into table (id,id_pres,something) values (4,30,'test')
update table set something='testupdate' where id_pres=30 -->causes
deadlock

Application 2 at the same time does:
PREPARE
EXECUTE insert into table (id,id_pres,something) values (5,40,'test')
EXECUTE insert into table (id,id_pres,something) values (6,40,'test')
EXECUTE insert into table (id,id_pres,something) values (7,40,'test')
EXECUTE insert into table (id,id_pres,something) values (8,40,'test')
update table set something='testupdate' where id_pres=40 -->causes
deadlock

then a deadlock agent raises an exception (SQLSTATE=40001,errorCode :
911)
on one of two application and the other one can complete the
transaction.

Can u explain me why there's a deadlock on update?

many thanks..

M.

[Gert van der Kooij]

In article <68**************************@posting.google.com >,
Javauser (ke******@mailinator.com) says...

If autocommit is turned off, you should do explicit commits in the
application to release the locks. You might also check to see that your are
using CS isolation level.

Hi and thanks for reply..
if u watch the snippet above , at the bottom i do commits on
connection.

anyway i created an event monitor for deadlock and i found that is
caused by an update..sorry..i believed transaction was formed just by
inserts.

So..i do n inserts and 1 update on table (where id is primary key)

Application 1 does:
PREPARE
EXECUTE insert into table (id,id_pres,something) values (1,30,'test')
EXECUTE insert into table (id,id_pres,something) values (2,30,'test')
EXECUTE insert into table (id,id_pres,something) values (3,30,'test')
EXECUTE insert into table (id,id_pres,something) values (4,30,'test')
update table set something='testupdate' where id_pres=30 -->causes
deadlock

Application 2 at the same time does:
PREPARE
EXECUTE insert into table (id,id_pres,something) values (5,40,'test')
EXECUTE insert into table (id,id_pres,something) values (6,40,'test')
EXECUTE insert into table (id,id_pres,something) values (7,40,'test')
EXECUTE insert into table (id,id_pres,something) values (8,40,'test')
update table set something='testupdate' where id_pres=40 -->causes
deadlock

then a deadlock agent raises an exception (SQLSTATE=40001,errorCode :
911)
on one of two application and the other one can complete the
transaction.

Can u explain me why there's a deadlock on update?

many thanks..

M.

After the inserts Application 1 is holding exclusive locks on id 1 -
4 and application 2 is holding exclusive locks on id 5 - 8.
A1 needs to scan the full table to update rows with id_pres = 30 and
is waiting on A2. Then A2 needs to scan the full table and is waiting
on A1 et voila !

One way to prevent this could be to create a type2 index on id_pres.
This is default if the table is created on DB2 V8. You might need to
recreate your indexes if you migrated from V7. If you're not on V8
you need to commit before updating, no type2 index is available prior
to V8.

Hope this helps.

Kind regards, Gert

[Javauser]

> After the inserts Application 1 is holding exclusive locks on id 1 -

4 and application 2 is holding exclusive locks on id 5 - 8.
A1 needs to scan the full table to update rows with id_pres = 30 and
is waiting on A2. Then A2 needs to scan the full table and is waiting
on A1 et voila !

One way to prevent this could be to create a type2 index on id_pres.
This is default if the table is created on DB2 V8. You might need to
recreate your indexes if you migrated from V7. If you're not on V8
you need to commit before updating, no type2 index is available prior
to V8.

Hope this helps.

Kind regards, Gert

Hi Gert..
thank u very much for your clear answer!
unluckily we have to stay with the V7 ..
is there any fixpack out there that can fix the problem for our db version?
In case there's not any i will commit before update like u said..

thanks again
M.

[Javauser]

>Application 1 does:

EXECUTE insert into table (id,id_pres,something) values (1,30,'test')
EXECUTE insert into table (id,id_pres,something) values (2,30,'test')
EXECUTE insert into table (id,id_pres,something) values (3,30,'test')
EXECUTE insert into table (id,id_pres,something) values (4,30,'test')
update table set something='testupdate' where id_pres=30 -->causes
deadlock
Application 2 at the same time does:
EXECUTE insert into table (id,id_pres,something) values (5,40,'test')
EXECUTE insert into table (id,id_pres,something) values (6,40,'test')
EXECUTE insert into table (id,id_pres,something) values (7,40,'test')
EXECUTE insert into table (id,id_pres,something) values (8,40,'test')
update table set something='testupdate' where id_pres=40 -->causes
deadlock

This is default if the table is created on DB2 V8. You might need to
recreate your indexes if you migrated from V7. If you're not on V8
you need to commit before updating, no type2 index is available prior

I'm committing before updating like u said but it still does not work!
autocommit is set to true of course and i removed the executeBatch()
method....
now i just run with executeUpdate() n 'inserts' and than executeUpdate() m
'updates' on the same connection..
but damn..deadlock raises in one of the updates.

i'm talking about n=800 and m=10 for 4 concurrent applications on the same
table.
with a lower number of inserts (about 400) and less concurrent applications
it works..

now the isolation level is by default set to READ_COMMITTED..
i've tried to change it to READ_UNCOMMITTED but it still does not work..

i can't believe it..

any suggestion would be appreciated..

Tnx
M.

[Mike]

"Javauser" <ke******@mailinator.com> wrote in message

now the isolation level is by default set to READ_COMMITTED..
i've tried to change it to READ_UNCOMMITTED but it still does not work..

i can't believe it..

any suggestion would be appreciated..

Tnx
M.

I do not have your DDL for the table and indexes, nor the
exact SQL, but it sounds simple to discover what's really
happening.
If you really want to see what's happening
then do a manual test. Use GET SNAPSHOT FOR LOCKS along with
the CLP and do not commit. By that I mean
to use at least two shell windows (or command-prompts
depending on your platform), and execute (for a test)
the update commands manually WITHOUT A COMMIT purely
to see for yourself what are the locks!

Use commands like these in separate windows at the same time,
on a database in which nothing much else is happening:

db2 connect to ....
db2 +c "update table set something='testupdate' where id_pres=40"
db2 get snapshot for locks on...

After the end of the test: db2 rollback.

Now compare the locks from both connections, in the different
shells/prompts. You may find that the ROW locks from the update are not
what you expect. You may find that in addition to a "U"
row locks (for the update), there might other "next key" locks.
If you find these, consider reading about DB2_RR_TO_RS=YES
registry variable , only on DB2 v7.x. This may help.
However the basic idea is to prove what's happening with the
locking by taking a real example and proving things.
Good luck.