db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Guidance/Help/Book/References?
Date Sat, 23 Jun 2012 12:35:19 GMT
> 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

Mime
View raw message