Question & Answer
Question
You are designing an application against Informix Dynamic Server (IDS) that calls a stored procedure during a transaction. You are concerned that a stored procedure recompile during the transaction will lock the record in sysprocplan for the entire transaction, and other calls to the stored procedure will fail because of the lock.
Cause
The object dependency list for a stored procedure is checked just prior to procedure execution. If the check detects any changes in the objects after the last procedure compile, the stored procedure is recompiled. Procedure compile places a lock on the sysprocplan table. Execution of the procedure from another session can fail because of the lock, with error:
211: Cannot read system catalog (sysprocplan).
and an accompanying ISAM error showing the failure is because of a lock.
Answer
IDS was modified in fixpaks 10.00.xC7 and 11.10.xC2 to minimize the effect of sysprocplan locking during a transaction. A child transaction is spawned from the parent transaction for the purpose of updating sysprocplan. The child transaction commits and releases locks on sysprocplan when the sysprocplan update completes.
You can confirm the transaction behavior by examining the logical log records using the onlog utility. You will see nested BEGIN and COMMIT records. You can also observe the locks at various stages in the transaction using the onstat -k command.
Related Information
Was this topic helpful?
Document Information
Modified date:
03 June 2021
UID
swg21447888