>>>>>>>>>>>> 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 <[EMAIL PROTECTED]> 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
signature.asc
Description: Digital signature
