sysprocplan locking problem

I have some insert/update triggers that call a stored procedure to maintain certain data in a table. This has been in place for almost a month now and on 3 different occassions, a process will "hang" and lock the sysprocplan table in exclusive mode. Of course this keeps others from executing the stored procedure as we sit in a deadlock situation. Does anyone know what is causing this and how to fix it? I'm running IDS 7.30.UC3. If you need additional information, let me know.

Bill, Try running update statistics on all of your stored procedures; Greg "Weaver, Bill" wrote: > I have some insert/update triggers that call a stored procedure to maintain > certain data in a table. This has been in place for almost a month now and > on 3 different occassions, a process will "hang" and lock the sysprocplan > table in exclusive mode. Of course this keeps others from executing the > stored procedure as we sit in a deadlock situation. > > Does anyone know what is causing this and how to fix it? I'm running IDS > 7.30.UC3. If you need additional information, let me know.

"Weaver, Bill" wrote: > I have some insert/update triggers that call a stored procedure to maintain > certain data in a table. This has been in place for almost a month now and > on 3 different occassions, a process will "hang" and lock the sysprocplan > table in exclusive mode. Of course this keeps others from executing the > stored procedure as we sit in a deadlock situation. > > Does anyone know what is causing this and how to fix it? I'm running IDS > 7.30.UC3. If you need additional information, let me know. I had exactly the same problem on the same release. I now do update statistics on all procedures every night after I update statistics on tables. See note below from Informix Tech Support. From: Basem Ghatasheh [basemg@informix.com] Sent: Wednesday, May 26, 1999 4:07 PM To: McAllister, Doug Subject: Re: Case #845400 Ho Doug, This is what I found: Bug: 68096 7.20.UC2 LOCKING STRATEGY FOR RE-OPTIMIZATION OF STORED PROCEDURES WITHIN A TRANSACTION HOLDS NEEDS CHANGING Description: The locking strategy of a stored procedure within a transaction during re-optimization holds the lock on sysprocplan until the commit is executed. Examination of the locking strategy needs to take place to consider releasing the lock after re-optimizatio completes and not waiting for the commit/ transaction to complete Stored procedure reoptimisation takes place whenever the major version number of one of the tables it refers to, changes. If the stored proc. execution (reoptimisation) happens to be within a transaction, then the transaction acquires an update lock on the SYSPROCPLAN entry of the stored procedure, modifies the query plan, and holds the update lock until it commits. While the transaction is progress, other users trying to execute the same stored procedure could run into locking conflict on the SYSPROCPLAN resulting in -211. The current work around to this problem is to have all the stored procedure query plans up-to-date by periodically running "update statistics" on stored procedures. The periodicity of running "update statistics" on stored procedures depends upon the frequency with which base tables change, either due to DDLs or due to running "update statistics" on the tables themselves. Another suggestion was to have all the developers ,who were issuing DDLs, move to another database instead of working on the production system. Thanks, Basem