We are using Database server = DB2/SUN64 8.2.4 fixpack 11.

Our J2EE Application will do batch inserts in DB2. recently we are getting Exceptions while we doing batch inserts on table.

net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:58)] ERROR - [BEA][DB2 JDBC Driver][DB2]Abnormal end unit of work condition occurred.
net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:57)] WARN - SQL Error: -911, SQLState: 40001
[net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:58)] ERROR - [BEA][DB2 JDBC Driver][DB2]ROLLBACK DUE TO DEADLOCK/TIMEOUT; REASON 68

We tried a lot to replicate the error in Our Dev environments . but we could not able to do...

It seems issue is with Database configurations.

And what we observed is ....when we query the data from table , at sametime bacth inserts are happeing on that table, we are getting exceptions.

This table has 900K rows have one column with CLOB(50080)
Is there anything todo with Tablespace and Bufferpool configurations?

Can someone pls advise me if u have idea?

Thanks.

Try to make the following changes at Database Configuration Level:

1> change the LOCKTIMEOUT to "60". Currently it can be "-1" in your system.
Lateron you can change it to any value based on your application (OLTP or DSS).

2> Increase the LOCKLIST memory parameter from its default value. If it is 5000, increase it by atleast 4-5 times.

Now check the behavior. I am assuming you don't want to use "WITH UR" in the SELECT statements in your application. If you could have used "WITH UR", then the lock-escalation comes down drastically. But it all depends on your business requirement whether you are fine with reading UNCOMMITED records.

Thanks.
Jayanta Datta
New Delhi, India

The OP already has locktimeout set to something other than -1. Hence he would not have gotten the RC = 68 (this is a timeout).

Increasing the locklist probably will not help since it only takes one lock to cause this to occur.

His best bet is to commit the transaction as soon as possible.

Andy

Thank you Andy and Jayanta for your Quick response.
we have LOCKTIMEOUT as 30. And we are commiting the bacth transactions size of 100.
And we are getting this message only when we Query the data from the Application, and inserts also happens at same time.

And also what we observed recently is while taking of that table dump, error popup again.

And we are setting Isolation level as UR while our application querying Table.

i'm wondering Isolation UR also cause locks on Table?

It shouldn't. There may be various side effects that cause the timeout, e.g. lookups related to the referential integrity constraints, or triggers firing upon an insert. You probably should study the lock snapshot to determine what causes the problem.

You could also try setting the DB2_SKIPINSERTED registry variable to ON (you will need to deactivate and re-activate the database for the change to take effect).

Who is getting the lock timeout, the application or the batch transactions?

Andy

Andy, batch transactions is getting timeout

That is what I guessed. You need to have the application commit/rollback after the read, or any locks will stay there and the batch process wants to uses locks that have to wait for the read locks.

Andy

But my application Querying data with UR isolation level.

The locks used for the read are incompatible with the locks used by the batch process. Hence the batch process waits. Just commit/rollback after the application read.

Andy

Andy, I could not understand. you mean Uncommitted Read isolation level also hold locks.

if so is there any DB register which can be turned on/off?
Bcaz i could not replicate the same issue in Our Dev/UAT environments.it is happening only in Prod.

Use the snapshot monitor to see what locks the application is using.

Andy

Rodex,

Are you running this application in many threads together? Did you try to see the Diag.log to see any other information regarding this -911.

Can you increase the LOCKTIMEOUT from 30 to 60 or some other higher value to see whats going wrong.



Regards,
Jayanta
New Delhi, India

We tried to replicate in Dev environement...but no luck...
our Database server is DB2/SUN64 8.2.4 fixpack 11.

has DB2 LOCKSIZE at tablespace level?

LOCKLIST is at Database Level. Next time whenever you run the application in Prod, try to gather some Snapshot on all applications. Then in those SNAPSHOTs, search for the "SELECT and INSERT" statements. You will get to see some more details, on how many Locks Hold, whether there was any EXCLUSIVE locks etc. Till we get some more information on what's happening inside, it will be tough for others to help you much on this problem.