Re: Unnecessary Locks -- Help!

Stefan Weideneder wrote: > > Chao Y. Din wrote: > > > > We have experienced locking problems from a new program we developed. > > We subsequently developed the following test cases. > > > > All test cases are using the same table A with key -- char(11), col -- > > char(2), and all sqls are using repeatable read. > > > > case 1: > > from one telnet session: > > insert into A (key, col) values ("A00000B0001", null);> > row inserted > > > > from another telnet session: > > insert into A (key, col) values ("A00000B0002", null);> > key value lock > > > > case 2: > > from one telnet session: > > insert into A (key, col) values ("A00000B0001", null);> > row inserted > > > > from another telnet session: > > insert into A (key, col) values ("A00001B0001", null);> > row inserted > > > > So, OnLine works as it should be. > > > > But here comes the problem. We defined a foreign key from col of table > > A to table B. Now even test case 2 got key value lock regardless > > whatever values col is using. > > > > Can anyone explain it? Is it because repeatable read locks the > > referenced table? Is there a way to avoid the locking yet still > > maintain repeatable read? > > > > Chao > > Hi, > > it looks like a problem, maybe a bug. It's because there is no need > to lock the referenced row in eXclusive mode. A simple Shared lock > would be enough to ensure, that no other process could manipulate > the referenced row. > I'm so sorry but i agree to Billy's words. > > Bye, Ciao, Servus > > Stefan. > > stefan@weideneder.de Hi, I've tried the example today with an multiple index on a single column. Yes, it works. There's just a shared lock on the referenced row and an eXclusive lock on the inserted rows. Version OnLine DS 7.12U?? on HP-UX 9.x Bye Stefan.