Derby is used heavily in my project and its tables are frequently accessed
concurrently by multiple threads. Some threads update
one or several tables, while other threads perform run select statements
against those. I’ve written to this group several times
whenever errors occurred, but some of those message have either been ignored or
contained references to fairly short
explanations on the website. So, the problems continue and it is difficult to
find the proper solution.
...
I must resolve all these errors ASAP and I would like to ask some guidance as
to how to do it most properly.
Avoiding deadlocks can be very challenging.
I have often found that a very crude mechanism can be used.
In my application, I modified my code to use Derby's LOCK TABLE statement.
For each transaction, I identified which table(s) it was accessing.
At the start of the transaction, my code issued a series of
LOCK TABLE statements:
LOCK TABLE t1 IN EXCLUSIVE MODE;
LOCK TABLE t2 IN EXCLUSIVE MODE;
I did this even for read-only transactions (and used EXCLUSIVE mode
for them, although I certainly could have used SHARE mode).
I thus single-threaded all my Derby accesses, and avoided all deadlocks.
I encapsulated this logic into a common subroutine to avoid
cluttering up the rest of the code; I just have to pass that
subroutine the correct set of tables for this particular
transaction, and the JDBC connection to use.
I ensured that I always locked the tables in the same order (to
avoid deadlocks).
Although this meant that my transaction was somewhat less concurrent
than it might have been:
a) I never had to deal with unexpected deadlocks in the middle of a transaction
b) I found that performance was just fine for my application
If deadlocks are an ongoing persistent problem, you might consider this
technique.
thanks,
bryan
