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.getConnectio n("bla","bla" );
connection.setA utoCommit(false );
myStatement = connection .createStatemen t();
for(int i=0;i<inSqlComm and1.size();i++ ) {
myStatement.add Batch((String)i nSqlCommand1.ge t(i));
}
updateCounts = myStatement.exe cuteBatch();
if(Ok) connection.comm it();
else connection.roll back();

Any hints?

tnx
M.

[Eric Herber]

Re: Deadlock or timeout on executeBatch()

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 DB2DETAILDEADLO CK 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_HOM E>/<INSTANCE>/NODE0000/SQL0000#/db2event/db2detaildeadlo ck
- db2 'flush event monitor db2detaillock'
- db2evmon -d <dbname> db2detaildeadlo ck

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

HTH.
Best regards

Eric

Javauser wrote:
[color=blue]
> 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.getConnectio n("bla","bla" );
> connection.setA utoCommit(false );
> myStatement = connection .createStatemen t();
> for(int i=0;i<inSqlComm and1.size();i++ ) {
> myStatement.add Batch((String)i nSqlCommand1.ge t(i));
> }
> updateCounts = myStatement.exe cuteBatch();
> if(Ok) connection.comm it();
> else connection.roll back();
>
> Any hints?
>
> tnx
> M.[/color]

--
IT-Consulting Herber
Mobile: +49 177 2276895
*************** *************** *************** **
Download the IFMX Database-Monitor for free at:

http://www.herber-consulting.de/BusyBee

*************** *************** *************** **

[Javauser]

Re: Deadlock or timeout on executeBatch()

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

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

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

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]

Re: Deadlock or timeout on executeBatch()

"Javauser" <keystore@maili nator.com> wrote in message
news:685da023.0 401120137.573ee 0ec@posting.goo gle.com...[color=blue]
> 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..
>
>[/color]
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]

Re: Deadlock or timeout on executeBatch()

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

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,som ething) values (1,30,'test')
EXECUTE insert into table (id,id_pres,som ething) values (2,30,'test')
EXECUTE insert into table (id,id_pres,som ething) values (3,30,'test')
EXECUTE insert into table (id,id_pres,som ething) values (4,30,'test')
update table set something='test update' where id_pres=30 -->causes
deadlock

Application 2 at the same time does:
PREPARE
EXECUTE insert into table (id,id_pres,som ething) values (5,40,'test')
EXECUTE insert into table (id,id_pres,som ething) values (6,40,'test')
EXECUTE insert into table (id,id_pres,som ething) values (7,40,'test')
EXECUTE insert into table (id,id_pres,som ething) values (8,40,'test')
update table set something='test update' 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]

Re: Deadlock or timeout on executeBatch()

In article <685da023.04011 21026.18cc821b@ posting.google. com>,
Javauser (keystore@maili nator.com) says...[color=blue][color=green]
> > 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.[/color]
>
> 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,som ething) values (1,30,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (2,30,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (3,30,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (4,30,'test')
> update table set something='test update' where id_pres=30 -->causes
> deadlock
>
> Application 2 at the same time does:
> PREPARE
> EXECUTE insert into table (id,id_pres,som ething) values (5,40,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (6,40,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (7,40,'test')
> EXECUTE insert into table (id,id_pres,som ething) values (8,40,'test')
> update table set something='test update' 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.
>[/color]

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]

Re: Deadlock or timeout on executeBatch()

> After the inserts Application 1 is holding exclusive locks on id 1 -[color=blue]
> 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[/color]

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]

Re: Deadlock or timeout on executeBatch()

>Application 1 does:[color=blue]
>EXECUTE insert into table (id,id_pres,som ething) values (1,30,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (2,30,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (3,30,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (4,30,'test')
> update table set something='test update' where id_pres=30 -->causes
>deadlock
>Application 2 at the same time does:
>EXECUTE insert into table (id,id_pres,som ething) values (5,40,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (6,40,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (7,40,'test')
>EXECUTE insert into table (id,id_pres,som ething) values (8,40,'test')
> update table set something='test update' where id_pres=40 -->causes
>deadlock[color=green]
> > 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[/color][/color]

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_UNCOMMITTE D but it still does not work..

i can't believe it..

any suggestion would be appreciated..

Tnx
M.

[Mike]

Re: Deadlock or timeout on executeBatch()

"Javauser" <keystore@maili nator.com> wrote in message[color=blue]
> now the isolation level is by default set to READ_COMMITTED. .
> i've tried to change it to READ_UNCOMMITTE D but it still does not work..
>
> i can't believe it..
>
> any suggestion would be appreciated..
>
> Tnx
> M.[/color]

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='test update' 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=YE S
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.