Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of mikemapp1@gmail.com
 designates 209.85.219.50 as permitted sender)
Message-ID: <52F53892.80907@gmail.com>
Date: Fri, 07 Feb 2014 11:48:34 -0800
From: mike matrigali <mikemapp1@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Unexpected behavior for concurrent selection of an uncommitted
 record inserted in a different thread
References: <82AAE9C8-94AD-408F-A8A1-CCAE368CBFE5@gmail.com>
 <97EB699F861AD841B5908C7CA9C9565602BB081CF52E@VSERVER1.canoga.com>
In-Reply-To: 
 <97EB699F861AD841B5908C7CA9C9565602BB081CF52E@VSERVER1.canoga.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:jacopo.cappellato@gmail.com]
> Sent: Friday, February 07, 2014 12:59 PM
> To: derby-user@db.apache.org
> 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
>