db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Derby row locking semantic
Date Thu, 18 Jan 2007 21:54:28 GMT
Hi,

I might be showing my ignorance here but will probably learn in the process.
I looked at
http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts15366.html and
found under Table 3 that for TRANSACTION_READ_COMMITTED, it is possible to
see phantom reads. I think what Alex is encountering is a phantom read and
not a dirty read (as per the examples in Table 2) and hence I agree with
Alex that CLIENT_B should not block in the following scenario.

thanks,
Mamta


On 1/18/07, Alex Boisvert <boisvert@intalio.com> wrote:
>
> Hi,
>
> I stumbled upon an interesting locking behavior in Derby 10.2.2.0yesterday and thought
I'd post to the list to get a better understanding of
> what's happening under the covers.   Assume a table with two indexes:
>
> create table MY_TABLE (
>     ID bigint,
>     FOO varchar(255),
>     BAR varchar(255),
>     primary key (ID)
> );
>
> create index IDX_FOO on MY_TABLE (FOO);
> create index IDX_BAR on MY_TABLE (BAR);
>
> With isolation level READ_COMMITTED, here's a simple concurrent scenario:
>
> CLIENT_A: start transaction
> CLIENT_A: insert into MY_TABLE values (1, 'FOO-1', 'BAR');
>           ===> OK (1 row inserted)
>
> CLIENT_B: start transaction
> CLIENT_B: insert into MY_TABLE values (2, 'FOO-2', 'BAR');
>           ===> OK (1 row inserted)
> CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2'
>           ===> OK (1 row matched)
> CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2' and BAR = 'BAR'
>           ===> OK (1 row matched)
> CLIENT_B: select * from MY_TABLE where BAR = 'BAR' and FOO = 'FOO-2'
>           ===> OK (1 row matched)
> CLIENT_B: select * from MY_TABLE where BAR = 'BAR'
>           ===> BLOCKS!
>
> at that point CLIENT_B blocks until CLIENT_A commits or rollback.   And if
> CLIENT_A also selects BAR = 'BAR' we get into a deadlock situation.
>
> What I don't understand is why CLIENT_B blocks to obtain a read lock on a
> row that has not been committed by CLIENT_A.  Would anyone be able to
> explain the underlying mechanism at work here?
>
> regards,
> alex
>

Mime
View raw message