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
