Unnecessary Locks -- Help!
Posted in 1996
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