Brett, Mike, thank you for your clear explanations: it makes sense to me now.
Jacopo On Feb 7, 2014, at 8:48 PM, mike matrigali <[email protected]> wrote: > Brett is right, please ignore my other posting. I missed that same key was > being looked at. Derby uses locking to implement sql concurrency which > leads to this behavior. Other products (or instances of products depending > on what storage engine mysql is using), may use versioning > instead which may result in different behavior. I believe the Derby behavior > is within the SQL standard, but is less concurrent than a versioning scheme. > > /mikem > > On 2/7/2014 11:20 AM, Bergquist, Brett wrote: >> I think this is the behavior as described in this document: >> >> http://docs.oracle.com/javadb/10.5.3.0/devguide/rdevconcepts8424.html >> >> See the table entry on read-committed and it looks like the insert statement >> has an exclusive lock on inserted record and as such the select by T2 for >> that specific record will block on the exclusive lock. >> >> -----Original Message----- >> From: Jacopo Cappellato [mailto:[email protected]] >> Sent: Friday, February 07, 2014 12:59 PM >> To: [email protected] >> Subject: Unexpected behavior for concurrent selection of an uncommitted >> record inserted in a different thread >> >> 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 >> >
