db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Derby row locking semantic
Date Thu, 18 Jan 2007 22:22:31 GMT
>>>>>>>>>>>> Mamta Satoor wrote (2007-01-18 13:54:28):
> 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.

Note the SQL definition says that phantom reads are allowed, not
required. A standards compliant DB may give you a higher isolation
level than you ask for or implement an isolation level stricter than
the SQL standard allows the DB to be.



> 
> 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
> >

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message