An application deployed on several machines - accesses the same DB Table. It reads the MIN row and then deletes that row.
When this happens concurrently, we get a -913 error from DB2 signifying deadlock.
Have tried following options already 1. locks on row. 2. re-try mechanism in application code, after the deadlock occurs.
Nothing seems to work.
Any ideas / references / solution?
TY
Check the reason code associated with your SQL0913N to determine if the problem is actually a deadlock (reason code 2) or just a lock timeout (>2).
If the problem really is a deadlock, you can capture detailed trace information about deadlocks by activating a DB2 event monitor for deadlocks. If you're not already capturing the specific statements generated on your behalf by Hibernate, you may also want to define and activate a SQL statement event monitor to capture as much detail as possible.
The isolation level that Hibernate is using can have a big impact on concurrency. Typically, applications encounter less locking when they can perform dirty reads via Uncommitted Read isolation, but that approach is not ideal because it exposes uncommitted data and undermines the ACID properties of DB2. If you've already enabled dirty reads, it's possible that it's contributing to your specific problem because the rows that have uncommitted changes are visible from other connections instead of being locked.
The design of your application (multi-threaded access to what is effectively a single work queue) may not be ideal and could possibly benefit from refactoring. The dining philosophers problem offers a variety of solution patterns to reduce contention. Depending on the specifics of your application, you may be able to change the way rows are processed, such as setting a status flag early on, which will help other threads understand that that particular row is already being processed by another thread and can be skipped. It's also possible that a minor adjustment to your transaction boundaries, resulting in more frequent commits, could alleviate the problem.
One of the more noteworthy improvements in DB2 9.7 (released in June 2009) is an enhancement to Cursor Stability isolation that provides access to the currently committed version of a locked row.