Re: drop table and locks

Quoting Jonathan Leffler, >Quoting Manuel Monteiro, >> >>We have detected a problem when dropping a table (temporary or not) or an >>index in a database without transactions. > >Which version are you using, and which machine? There used to be some >problems in this area in the 2.10.00 release of ISQL, and maybe in the .03X >releases. I've not seen any similar problems in 4.00 and above. >[text deleted] I'm sorry, I've forgot to mention which versions/products/machines I'm using. DECstation 3100 ( Ultrix 4.2 ) c4gl -V INFORMIX-4GL Version 4.10.UD1 tbstat -V RSAM Version 4.10.UE1 isql -V INFORMIX-SQL Version 4.10.UD1 IBM R6000/530 ( AIX 3.1.5 ) c4gl -V INFORMIX-4GL Version 4.10.UD2 tbstat -V RSAM Version 4.10.UE3 isql -V INFORMIX-SQL Version 4.10.UD2 The problem appears in both configurations. Quoting David Cortesi, >When the program prompted saying that there was a lock, >I was able, using ISQL from a different user id, to select, >insert and update rows in the table. I was also able to >create an index xab on t(a,b) at this time. Select - Ok. Insert 271: Could not insert new row into the table. 113: ISAM error: the file is locked.Update/Delete 601: Cannot delete blob. 113: ISAM error: the file is locked.Create Index 314: Table (mm.t) currently in use. 113: ISAM error: the file is locked. >When the table had been dropped, an attempt to select >produced message -211, the table is not in the database. The error I get is -206 (The specified table (t) is not in the database), not -211. >At this time (while the program was prompting that "there >is a lock in the dropped table") I was able to create a >new table of the same name ("t"). At this point I also can create a table of the same name, but I cannot insert/delete/update rows into it, getting the same error codes. >Also, please make it clearer what you mean by "there is a lock >on the table." Could you be specific about what SQL operation >the other process attempts, and what SQL and ISAM error codes >it receives as a result? When the program prompts "There is a lock on the table", the output from 'tbstat -k' for the corresponding user is: Locks address wtlist owner lklist type tblsnum rowid size 808bf4 0 801f04 0 HDR+S 1000002 20e 0 (*1) 808c74 0 801f04 808f54 HDR+X 10000e0 0 0 (*2) 808f54 0 801f04 808bf4 HDR+IX 1000023 0 0 (*3) (*1) - database (*2) - lock on table t (with partnum=10000e0 in systables) (*3) - lock on table systables (partnum=1000023) To confirm the partnum codes I've ran the following SQL statment: select tabname, hex(partnum) from systables where tabname in ("systables","t"); tabname (expression) systables 0x01000023 t 0x010000E0 2 row(s) retrieved. When you drop table t, the lock remains in the table with partnum 10000e0. When another process creates a new table (either implicitly - ex: opening a scroll cursor, or explicitly), the SQL will probably assign it the same partnum, since this partnum is (apparently) free. If it works this way, the new created table will INHERIT the existing lock. Since this lock belongs to the first process, the current owner of the table is unable to insert/delete/update any rows into it. IMPORTANT NOTE: this problem only seems to appear in databases WITHOUT transactions. If there is missing some info to completly understand this situation, feel free to ask for it. Best regards, MM ------------------------------------------------------------------------------- | Manuel Figueiredo Monteiro | | S.S.F. - Servicos de Software Financeiro, Lda VOICE: +351 (1) 727-0303 | | Estrada da Luz, No 104 - Loja A FAX: +351 (1) 727-0335 | | 1600 Lisboa E-MAIL: mm@ssf.pt | | PORTUGAL | -------------------------------------------------------------------------------