Re: -113 ISAM error: - the file is locked

In article <4qa001$m1q@corolla.eng.sc.rolm.com>, Dan Jatnieks <daniel@siemensrolm.com> writes > >We recently encountered this problem, but it was contention for a lock >on a _system_ table while executing a stored procedure. We hit locks >on both sysprocauth and sysprocplan tables. > There is a known bug under some versions of Informix where the sysprocplan table gets an exclusive lock taken on it when a stored procedure executes. Also one other sysproc... table is affected, I can't remember which one though, sysprocauth sounds about right. >To note: > >o System tables have page level locking. We're not supposed to change > this. Correct. >o It seems to be related to two users trying to run the same stored > procedure at the same time -- why are system tables being locked > for this??? According to Informix it is to prevent any one else > from _modifying_ these system tables while the stored procedure > is running. > Correct, what would happen otherwise if a) someone dropped the procedure whilst you were executing it?? Or even worse b) dropped and recreated the procedure with new code whilst you were executing it? If you insert several rows into a table and have an insert trigger on the table which calls a stored procedure than b) above would be very bad. For some rows the old version is used and for other rows the new version is used, all within the same transaction!!! >o It seems that exclusive locks are being placed on these tables > rather then shared locks. Why??? > Informix Bug - 7.12/7.13 or 7.2 are the latest versions depending upon you platform.. >o This seems to imply that any stored procedures that have rows in > these system tables falling on the same disk page cannot be executed > at the same time by different users!!! Not very good concurrency > I'd say. > >o So, our only choice is to set lock mode to wait. This in effect > serializes execution of stored procedures in the Informix Server > if they have entries on the same disk page for these tables. > Sounds sensible until you upgrade, but I'd have thought you would have that set anyway. >This is running Online 7.10 UC1 on a UnixWare 2.0x platform. > >Anyone know any more about this issue?? > >dan. -- David Williams