db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ronald Rudy <ronchal...@gmail.com>
Subject Re: Derby Statements
Date Fri, 29 Jan 2010 15:09:59 GMT
I've tried a couple of different isolation levels, specifically read committed and repeatable
read since they seemed to be most applicable. 

In my code as it is written today I don't use transactions.  When I've enabled transactions
for the thread iterating over resultsets, with various isolations, it didn't seem to help
- it either blocked out others or never started.  All my inserts are autocommitting. 

There's a fair amount of concurrency with the inserts/deletes (in actuality I rarely use updates
- these are small records used for tracking items awaiting confirmation that are purged when
confirmation is acknowledged from an external source), so this might be as simple as my code
simply burdening Derby with more concurrent inserts/deletes than it can handle (hence the
"lock could not be obtained" exceptions)

The specific exception I'm getting is:

org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested

These are thrown on the threads executing update statements (which are delete or insert SQL
statements).  When I'm using a read_only resultset it continues reading, I just see a small
amount of these errors appear under load.  It doesn't seem to happen when I'm not iterating
however, only while I'm iterating over the read_only resultset and deleting some portion of
those items while doing so.

So there are many threads inserting concurrently, one thread that is iterating over a resultset
and deleting records as it prunes acknowledged items.  It should be noted that due to some
object persistence laying that delete happens using a different connection.  When I just have
the threads inserting, there are no issues.

On Jan 29, 2010, at 9:58:26 AM, Dag H. Wanvik wrote:

> What isolation level are you using? If you just use read committed
> isolation level (default in JDBC/Derby) and a read only result set
> when you iterate, locks should not be kept on the records you have
> already read.
> http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts30291.html
> Dag

View raw message