Hi Dag,
> "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
Cheers,
Ronald
