Re: sysprocplan Locking?

Storm Software (storm@aztec.co.za) wrote: : Under what conditions might sysprocplan get locked? : We have a problem on one program which is used by man users and : we tracked the concurrency problem to a lock on sysprocplan. : Where do we start looking? You don't say what version of the engine you're using, but the following problem was supposed to be fixed in 7.10. The most likely cause, if you're using 5.0x, is that a stored procedure which either creates or accesses a temp table is being executed inside a transaction. Procedures are automatically reoptimized any time an object in their dependency tree has changed since the last time it was reoptimized. Of course, a temp table would not have existed the last time the procedure was optimized, so it will be reoptimized every time it is executed. This results in the update of sysprocplan, which contains the optimized query plan. If the execution of the procedure is contained inside a transaction, the relevant rows in sysprocplan will be locked for the duration of the transaction. Because the procedure is reoptimized upon every invocation, it became clear that there was no value in updating sysprocplan at execution time, so this step (updating sysprocplan, not reoptimizing the procedure) was omitted beginning with 7.10. ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own. Eventually all things merge into one...and a river runs through it. -N.Maclean