On Jan 29, 2010, at 11:33:28 AM, Dag H. Wanvik wrote: > Derby is always transactional, so even with autocommit, when you open > a read-only result set for iterating over it, any read locks it sets > for a row would block another connection trying to modify that row > (Update or delete). If you use repeatable read, the locks will not be > released for the rows you have already visited as long as the > transaction is live, so this would significantly increase the chances > of update connections blocking for as long as the iteration is going > on. By moving to read committed isolation level, you should (AFAIK) > only have one row level read lock set for the row the cursor is > presently positioned at. It would be interesting to know to do the > kind of debugging Bryan suggested to determine why the update > tranactions block, if you could find the time :) > > Dag
I've used read_committed (that was the primary one I was trying as it seemed best suited), but I'll see if I can get back to it and find some time. I'd much prefer to iterate over a resultset, deleting those (transactionally) as I iterate over them, all while doing concurrent inserts. I just want to do it without getting lock timeout exceptions :)
