db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mike matrigali <mikema...@gmail.com>
Subject Re: Unexpected behavior for concurrent selection of an uncommitted record inserted in a different thread
Date Fri, 07 Feb 2014 19:36:37 GMT
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;

0 rows selected
ij(LOCAL2)> select * from foo where a = 1;
**** 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:

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
> Here is my use case:
> * Derby
> * 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
> 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

View raw message