Another Informix Bug? Online

in <dtrouttCoMBx7.E3F@netcom.com> dtroutt@netcom.com (David Troutt) writes: > Has anybody else seen this one? We are running Informix-OnLine V5.01 under > Solaris 2.2. > > A user creates a temp table and inserts into it. Later, the user drops the > temp table, but remains in his process (either ISQL or a 4gl program). > Another user now logs into the same database and tries to create a temp > table. This user either hangs (if he has SET LOCK MODE TO WAIT), or > generates the following error: > > SQL statement error number -261. > Cannot create file for table <tablename>. > SYSTEM error number -113. > ISAM error: the file is locked.> > We are pretty sure that the "drop table" statement does not release the > lock on the temp table, and when the 2nd user tries to create a temp table, > OnLine tries to use the same internal table name. It then sees the > leftover lock on the table, and either generates the error or makes the 2nd > process wait until the lock disappears. The lock, however, will not > release until the 1st process exits completely. > > I'd like to know if there is any way to fix this other than making sure we > never drop temp tables. > > Thanks in advance. There are several possible solutions. I think I saw something a while back about making the temp table name unique (using the pid or some such to name it). I tested this on my own machine and had no problems at all, even creating the same tablename on the same database as the same user! It worked as another user too, both before and after the first user dropped his. What I did was: in isql ran "create temp table asdf (asd char(1)) with no log" as user cwakins Then in another session did the same thing. No err msg. Then I logged in as another userid and ran the same statement. OK. Then I dropped the first database and ran the creation statements again, as myself then as another user. Still no err msg. This all on a DEC 3000/400S alpha box using INFORMIX-OnLine Version 5.00.UD1 and INFORMIX-SQL Version 4.10.UE2 maybe that will help. Good luck! __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | | Reynolds Metals Co. "Climb High, Cave Deep!" | | Alloys Plant (MS #59) | | 3509 East 2nd St Internet: cwakins@leia.rmc.com | | Muscle Shoals, Al Telephone: (205) 386-6783 | | USA 35661-1256 Facsimile: (205) 386-6831 | |________________________________________________________________|