"Life is hard, and then you die" <[email protected]> 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.
Dag
