On Mon, Mar 30, 2009 at 06:51:03PM -0700, Life is hard, and then you die wrote: > > On Tue, Mar 31, 2009 at 02:38:32AM +0200, Dag H. Wanvik wrote: > > > "Life is hard, and then you die" <[email protected]> writes: > > > > > I'm trying to use Derby in a setup where I ensure no two transactions > > > will touch the same rows. However, despite setting the transaction > > > isolation level to READ_UNCOMMITTED, I'm still get undesirable row > > > locking causing my app to lock up. Here's a simple example of what I'm > > > doing (pardon the java/sql mixing - I can supply a complete java > > > example if desired): > > > > > > CREATE TABLE TEST (c1 VARCHAR(100), version BIGINT) > > > > > > con1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); > > > con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); > > > > > > con1.setAutoCommit(false) > > > con2.setAutoCommit(false) > > > > > > con1: INSERT INTO TEST VALUES('val1', 1) > > > con2: INSERT INTO TEST VALUES('val2', 2) > > > > > > con1: UPDATE TEST SET version = 3 WHERE version = 1 > > > > con1 will need to read the row ('val2', 2) here in order to evaluate > > the predicate WHERE version = 1 (even if only in order to discard it > > from consideration). Since con2 has not yet committed, it still holds > > the exclusive write lock on the row, so con1 hangs. > > Thanks for your answer. But since the isolation-level is read-uncommitted > I don't see why it needs to lock the row unless it is actually going > to update it. Just reading the row does not require a lock, as the > following query will run quite happily just before the update: > > con1: SELECT * FROM TEST WHERE version = 1
Btw., I also looked for a solution involving running a query to list all the desired rows and then updating those individually, but since the row_number() function can't be used the where clause I couldn't figure out any way to do so (using an updatable ResultSet also didn't work, as that runs into the same locking issue as the above UPDATE). Cheers, Ronald
