Hi,
What locking level was chosen when the table was created?
Is user_logon_id unique/primary key?
Is there lock escalation to table locks?
Try getting a lock snapshot to identify whats happening.

Cheers,
Thomas


-----Original Message-----
From: yjchong
[mailto:db2udbdba-ezmlmshield-x63830027.[Email address protected]
Sent: 28 February 2005 03:58
To: LazyDBA Discussion
Subject: Deadlock during concurrent update


Dear all,

I have run a series of load test with 80 users in a web application. Some
user can't login to the application with
the deadlock error :

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

I found that in the java application code:

strQuery = "UPDATE USER_LOGIN SET my_timestamp_type_column = CURRENT
TIMESTAMP, my_int_column = 0 WHERE USER_LOGIN_ID = ?";
....
prepareStatement(strQuery);
myStmt.setString(1, strUserId);
...
iUpdated = myStmt.executeUpdate(); //Deadlock error occured during
concurrent test.


Any constructive solution for that problem?

Many thanks.

Rgds,
John


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


***************************************************************
The information contained in this e-mail and any files transmitted
with it is confidential and may be subject to legal professional
privilege. It is intended solely for the use of the addressee(s).
If you are not the intended recipient of this e-mail, please note
that any review, dissemination, disclosure, alteration, printing,
copying or transmission of this e-mail and/or any file transmitted
with it, is prohibited and may be unlawful.
If you have received this e-mail by mistake, please promptly
inform the sender by reply e-mail and delete the material.
Whilst this e-mail message has been swept for the presence of
computer viruses, eircom does not, except as required by law,
represent, warrant and/or guarantee that the integrity
of this communication has been maintained nor that
the communication is free of errors, viruses, interception or
interference.
***************************************************************


DB2 & UDB email list listserv db2-l LazyDBA home page