Locking gone crazy ??

Fellow netters, I have just spent a hectic couple of days with my colleagues here trying to find the reason for some really strange behaviour in our Online engine. I hope that what we found is somehow a mistake or a misunderstanding or just a bad dream. Please bear with me as I try to explain ! We access data in many ways from our database, one such way is by use of declare cursor and repetitive fetches so that an array is returned to the calling program. Recently we have noticed that when an outer join is used for the cursor and a record is locked exclusively (tbstat -k) the fetch on this record returns a status of 246 (lock collision), great ! but we then retry the fetch and it reads the record every time and now the rowid is listed twice in the output of tbstat -k the first time with lock type HDR+X (exclusive) and then again with HDR+S (shared). The tblsnum has at this juncture changed and why this happens is a mystery to me. The consequences of such a locking strategy are too horrible to contemplate. Can you please look the test code below and confirm for me that this is really happening or better still tell me its just a bad dream. BTW the join does not necessarily have to be outer. 1. Create database locktest with log mode ansi 2. Run test.sql. 3. Activate the lock using lock.sql in dbaccess or isql 4. View the lock with tbstat -k. 5. Step/Debug through the 4GL program and see how it handles the first and subsequent fetches. 6. Step 4 again. test.sql ======== create table a ( ak1 decimal (8,0) not null, ak2 decimal (8,0) not null,a3 char(16) not null ) lock mode row ; grant all on a to public ; insert into a values(1,1,'a11') ; insert into a values(1,2,'a12') ; insert into a values(1,3,'a13') ; insert into a values(1,4,'a14') ; insert into a values(2,1,'a21') ; create table b ( bk1 decimal (8,0) not null, bk2 decimal (8,0) not null,b3 char(16) not null ) lock mode row ; grant all on b to public ; insert into b values(1,1,'b11') ; insert into b values(1,2,'b12') ; insert into b values(1,3,'b13') ; insert into b values(1,4,'b14') ; insert into b values(2,1,'b21') ;commit work ; lock.sql ======== update a set ak1 = ak1 where ak1 = 1 and ak2 = 1 ; The following 4GL source was written by a colleague of mine so I hope that I have typed it properly. We used it to test on a system with Online 4.1 and it worked properly (i.e. the exclusive lock remained and all fetches got a lock collision ) however, the same logic written in other languages ( c and our own xsde-l ) and tested on Online 5.0, Online 5.01 and Online 6.0 produce the error described above. 4gl source ========== DATABASE locktest DEFINE bk1 LIKE b.bk1 DEFINE bk2 LIKE b.bk2 DEFINE b3 LIKE b.b3 DEFINE ak1 LIKE b.ak1 DEFINE ak2 LIKE b.ak2 DEFINE a3 LIKE b.a3 MAIN DECLARE lalala CURSOR FOR SELECT * FROM a, b WHERE a.ak1 = 1 AND a.ak2 = 1 AND a.ak1 = b.bk1 AND a.ak2 = b.bk2 OPEN lalala WHILE TRUE FETCH lalala INTO ak1, ak2, a3, bk1, bk2, b3 IF STATUS = 0 THEN EXIT WHILE END WHILE DISPLAY "Ready" AT 5,5 SLEEP 4 END MAIN I hope I have made the problem/feature clear enough and hope also that somebody out there can explain this feature or tell me how to get around the problem. Tom Hogarty Standard Disclaimers Apply SNI Paderborn Germany Tel +494-52518-11602 +494-5254-65553($HOME) mail <bumidev.pad@sni.de>