db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Life is hard, and then you die" <ron...@innovation.ch>
Subject Re: disabling locking
Date Tue, 31 Mar 2009 01:51:03 GMT

  Hi Dag,

> "Life is hard, and then you die" <ronald@innovation.ch> 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


Mime
View raw message