Re: Unnecessary Locks -- Help!

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