Re: Unnecessary Locks -- Help! - MUST BE A BUG

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: ... > > case 2: ... > > 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? > Before I provide additional findings, I must point it out that we are running ANSI database with OnLine 7.2 running Solaris 2.5.1. Here are the new findings. 1. I re-test the above two cases with all possible isolation levels. The results are the same: "key value lock" when there is a foreign key and no error on case 2 when there is no foreign key. This does not make sense. Even with dirty read, there will be key value lock when a foreign key is defined. It just does not make sense. 2. Checked out the following statement from ANSI SQL92: "The schema definitions that are implicitly read on behalf of excuting an SQL-statements are implementation-dependent." I guess this means that it is up to Informix to determine how to implement the foreign key constraint and display a meaningless message -- how can you get rid of a key value lock error by dropping a foreign key? I used to believe that repeatable read locks all rows that are read explicitly in a program. But Informix's interpretation is to lock implicit read with exclusive lock regardless the isolation level. Is anyone there who believes this is not a bug? Chao