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 13:00:19 GMT
On Tue, Mar 31, 2009 at 01:09:17AM -0700, Life is hard, and then you die wrote:
> On Mon, Mar 30, 2009 at 02:24:51AM -0700, Life is hard, and then you die wrote:
> > 
> > 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
> > 
> > At this point it hangs waiting for a lock held by con2 and eventually
> > times out (often then leading to a java-level deadlock, but that's a
> > different issue and filed in jira):
> [snip]
> 
> As another data-point, if I create an index for the 'version' column
> then it doesn't hang (at least not in this example):
> 
>   CREATE TABLE TEST (c1 VARCHAR(100), version BIGINT)
>   CREATE INDEX TEST_v ON TEST(version)
> 
> In practice, on the full application (which has an index here), I see
> it sometimes wait for a lock and sometimes not.
[snip]

Thinking about it a bit, I came to the conclusion that it could be
because the query planner might sometimes choose the index and
sometimes a table-scan (when the datasets are small and hence a large
percentage of the table might be affected). So I replaced all update
and delete statements with equivalent select statement plus an
updatable result-set (and then using rs.updateXXX(...)); this allowed
me to then add "DERBY-PROPERTIES index=..." to those select statements
to always force the index to be used. And lo and behold, things seem
to work now (I've hammered it quite a bit with my stress test).

I also verified my theory on the simple example above. I replaced the
above UPDATE with 

    String sql = "SELECT version FROM TEST -- DERBY-PROPERTIES index=TEST_v \n " +
                 "WHERE version = 1";
    ResultSet rs = con1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).
                        executeQuery(sql);
    while (rs.next()) {
      rs.updateLong(1, 3);
      rs.updateRow();
    }
    rs.close();

and the "update" proceeded just fine. If I change the DERBY-PROPERTIES
to "index=NULL" then it hangs as before.

So, while the code is a bit uglier, things seem to work. But I'm still
a bit uncomfortable as this seems to be "undocumented" behaviour
(unless I missed it in the docs), and therefore presumably subject to
change without warning.

Btw., any particular reason why DERBY-PROPERTIES isn't allowed on
update and delete statements?


  Cheers,

  Ronald


Mime
View raw message