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 02:02:05 GMT
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" <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

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


Mime
View raw message