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

Stefan Weideneder wrote: > > Stefan Weideneder wrote: > > > > Chao Y. Din wrote: > > > > > > We have experienced locking problems from a new program we developed. > > > > 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 > I am really disappointed to see my second post on the subject disappeared on the news group. It is probably problems on our computer. Here is my post again. I looked up the ANSI SQL92 standard and found the following statement (page 566). "The schema definitions that are implicitly read on behalf of executing an SQL-statement are implementation-dependent." I used to believe that all rows explicitly referenced in a program will be locked, if repeatable read is used. But ANSI standard states that a vendor can do whatever they want to do for implicitly referenced rows. What can I say. I subsequently re-test the two cases with all possible isolation levels. Before I provide additional test results, I must point it out that the database is ANSI mode (maybe this is the problem) and is running OnLine 7.20UC2 on Solaris 2.5.1. 1) All isolation levels return "key value lock" for test case 1 regardless whether table A has a foreign key or not. 2) All isolation levels return "key value lock" for test case 2, as long as there is a foreign key. Even dirty read returns "key value lock". 3) All isolation levels work for test case 2, as long as there is no foreign key. Even repeatable read works. In other words, DROP ALL FOREIGN KEYS CAN SOLVING A "KEY VALUE LOCK" PROBLEM WHEN DIRTY READ IS USED. I really cannot believe this is not a bug. I also checked the lock generated in the tests. Unfortunately, I don't see any lock (shared and/or exclusive) on the referenced row (it makes sense though, because "col" is null). In other words, even if a shared lock is used for the referenced table, OnLine still returns "key value lock" for test case 2. Chao