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
