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