if possible you should post a reproducible case directly against derby.
Derby does use locking for its concurrency control, which
will have different behavior than versioning if those other products
do that. The expected behavior for read committed for what you describe
would be for derby to use the index to get directly to key 123 and not
ever see key 122 so not lock it. Seems like either T1 is locking more
than is described, T2 is not using a plan that uses an index, or
something else.
I tried what you describe in the most simple case and derby acts as
expected for a very simple case of a table with only 1 row in it. So
there is something else going on not in your description. For instance
maybe whatever else T1 is doing after the insert is the issue.
It uses the index and returns 0 rows when looking for the key not in
table and blocks when looking for non-committed insert when looking for
key that is in uncommitted transaction.
ij> create table foo (a int not null primary key);
0 rows inserted/updated/deleted
ij> autocommit off;
ij> insert into foo values (1);
1 row inserted/updated/deleted
ij(CONNECTION1)> connect 'jdbc:derby:_db/wombat' as local2;
ij(LOCAL2)> select * from foo where a = 2;
A
-----------
0 rows selected
ij(LOCAL2)> select * from foo where a = 1;
A
-----------
**** hanging on lock held by first uncommitted transaction *****
Sometimes this problem is with the query plan chosen by the select in
T2. If it does not choose to use the index for some reason, then you
will do a table scan and block.
here is a start at info on debugging locking behavior in derby:
http://wiki.apache.org/db-derby/LockDebugging
On 2/7/2014 9:59 AM, Jacopo Cappellato wrote:
Hi all!
While I was writing some unit tests for the Apache OFBiz project (that by
default runs on Derby) I noticed a behavior of Derby that I didn't expect and I
would love to get your opinion.
Here is my use case:
* Derby 10.10.1.1
* there are two concurrent transactions T1 and T2
* isolation level is "Read Committed"
* in transaction T1 a record with primary key 123 is inserted in a table; then
other long running tasks are executed (i.e. the transaction is not immediately
committed)
* in the meantime T2 attempts to select from the same table the record with
primary key 123
Behavior: T2 blocks on the select statement waiting for transaction T1 to
release the write lock; this can cause a lock wait timeout
Expected behavior: since T1 is not committed, T2 should not be able to select
the record; I was expecting that the select statement in T2 would return an
empty result set rather than blocking waiting for the lock held by T1 to be
released; in fact this is what we get with MySQL and Postgres.
What do you think?
Thanks,
Jacopo Cappellato
