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 disabling locking
Date Mon, 30 Mar 2009 09:24:51 GMT

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):

XID |TYPE  |MODE|LOCKCOUNT|LOCKNAME  |STATE|TABLETYPE |INDEXNAME |TABLENAME|
----------------------------------------------------------------------------
*** The following row is the victim ***
210 |ROW   |U   |0        |(1,14)    |WAIT |T         |NULL      |TEST     |
*** The above row is the victim ***
210 |TABLE |IX  |3        |Tablelock |GRANT|T         |NULL      |TEST     |
212 |TABLE |IX  |1        |Tablelock |GRANT|T         |NULL      |TEST     |
212 |ROW   |X   |1        |(1,14)    |GRANT|T         |NULL      |TEST     |
210 |ROW   |X   |1        |(1,13)    |GRANT|T         |NULL      |TEST     |
210 |ROW   |X   |1        |(1,13)    |GRANT|T         |NULL      |TEST     |
----------------------------------------------------------------------------

I'm not sure exactly why it's trying to lock a row that it won't
update, though.

What I really want is for Derby not to do any locking whatsoever,
since I will ensure at the application level that no two
(simultaneous) transactions will attempt to modify the same row. I.e.
I just want ACD, no Isolation. Is there any way to achieve this with
Derby?

I noticed a ISOLATION_NOLOCK in TransactionController, the name of
which sounds like what I'd like, but it's for internal use only :-(


  Cheers,

  Ronald


Mime
View raw message