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 08:09:17 GMT
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.

Dumping the lock table (SYSCS_DIAG.LOCK_TABLE) just before the update,
the only difference between the indexed and non-indexed versions I see
is the lock count on the table-lock:

  Locks (no index):
    XID, TYPE,  MODE, TABLENAME, LOCKNAME,  STATE, TABLETYPE, LOCKCOUNT, INDEXNAME
    164, ROW,   X,    TEST,      (1,8),     GRANT, T,         1,         null
    162, ROW,   X,    TEST,      (1,7),     GRANT, T,         1,         null
    162, TABLE, IX,   TEST,      Tablelock, GRANT, T,         1,         null
    164, TABLE, IX,   TEST,      Tablelock, GRANT, T,         1,         null

  Locks (with index):
    XID, TYPE,  MODE, TABLENAME, LOCKNAME,  STATE, TABLETYPE, LOCKCOUNT, INDEXNAME
    167, ROW,   X,    TEST,      (1,8),     GRANT, T,         1,         null
    165, ROW,   X,    TEST,      (1,7),     GRANT, T,         1,         null
    165, TABLE, IX,   TEST,      Tablelock, GRANT, T,         2,         null
    167, TABLE, IX,   TEST,      Tablelock, GRANT, T,         2,         null

Btw., can anybody explain what the intent-lock on the table is
actually doing here (i.e. why there is one)?


  Cheers,

  Ronald


Mime
View raw message