locks on sysprocplan

I have a couple of questions on locks on sysprocplan. I came across several threads in this newsgroup where this has been previously discussed but just want to confirm some of the facts stated in those threads and my own conclusions based on what I read in those threads. One thing that was mentioned in previous threads was that if a user session calls a stored procedure that needs recompilation (because of for example updated statistics) within a transaction, then IDS will update sysprocplan within that user transaction rather than a transaction controlled by the db engine. This means that any locks on sysprocplan will stay until that user transaction is committed or rolled back. Is this true? Is there any workaround or fix that will make the db engine not depend on a user transaction to commit or abort to release locks on system tables? I am working with an application that runs against IDS 9.40 UC2. Recently one installation ran into locking issues on sysprocplan (resulting in -211 errors caused by isam error -154) that brought the entire system to a halt. The errors did not go away until the application had been restarted and all sessions agains informix had been released. Statistics on all tables are updated by a nightly script and these errors occured in the early hours of the following morning. One possible explanation I see for this would be that a session executed a stored procedure within a transaction sometime after the nightly "update statistics" job and that because of the updated statistics this stored procedure was recompiled. Due to a software error in the client application the transaction was neither committed nor rolled back but the session stayed alive since the application was still running. Does this make sense? Would adding "update statistics for procedure..." for all stored procedures that belong to this application lower the risk for locks on sysprocplan? Does the order of execution in the "update statistics for procedure" make any difference (ie should I update the most commonly executed procedures last to ensure that their execution plans are stored)?

--This means that any locks on sysprocplan will stay until that --user transaction is committed or rolled back. Is this true?? yes!! -- Is there any workaround or fix that will make the db engine not -- depend on a user transaction to commit or abort -- to release locks on system tables? no; code and handle sqlerrors in your application. may be wait x secs for a lock and if the lock is still there rollback work; make your trx small !! --Would adding "update statistics for procedure..." for all stored --procedures that belong to this application lower the risk for locks --on sysprocplan? yes. do 'em all... update statistics for procedures;or update statistics for routines; --Recently one installation ran into locking issues on sysprocplan --(resulting in -211 errors caused by isam error -154) that brought --the entire system to a halt. The errors did not go away until the --application had been restarted and all sessions agains informix --had been released. YUK what was the session holding the lock doing??? waiting on user input?? some user who went out for a smoke or coffee??? also if one grant/revoke permissions the db may need to recompile the spl. Superboer.

Superboer, Thanks for your reply - you have confirmed that this is really as scary as I feared. :) --YUK what was the session holding the lock doing??? --waiting on user input?? some user who went out for a smoke or --coffee??? Hehe, you don't want to know. This is a piece of very old code (1992 or so) written by someone with no understanding of RDBMses - transactions are initiated, committed and rolled back from the client and the client goes away (killed process, unplugged network cable, going for coffee etc) then the middle tier will just leave that session "as-is" until someone manually goes in to kill the worker process in the middle tier. Anyway, changing/fixing/rewriting it is not an option (not my decision) so I guess the update stats is the next best thing. :)

This means that any locks on sysprocplan will stay until that user transaction is committed or rolled back. Is this true? yes, but the updated sysprocplan is not rolled back and so would not be recompiled again Is there any workaround or fix that will make the db engine not depend on a user transaction to commit or abort to release locks on system tables? yes, recompile all you stored procedures any time you you do something that will change the Version number of your table eg update statistics or change permissions Statistics on all tables are updated by a nightly script and these errors occured in the early hours of the following morning. One possible explanation I see for this would be that a session executed a stored procedure within a transaction sometime after the nightly "update statistics" job and that because of the updated statistics this stored procedure was recompiled. Due to a software error in the client application the transaction was neither committed nor rolled back but the session stayed alive since the application was still running. Does this make sense? yes Would adding "update statistics for procedure..." for all stored procedures that belong to this application lower the risk for locks on sysprocplan? yes Does the order of execution in the "update statistics for procedure" make any difference (ie should I update the most commonly executed procedures last to ensure that their execution plans are stored)? I wouldn't run them seperately but run the command that updates all the procedures

And..... I would suggest you explicitly name each stored procedure as well, i.e. generate the sql "UPDATE STATISTICS FOR PROCEDURE <procname>;". Using a generic 'update statistics for procedure;' may fail if it encounters a proc in use and any subsequent procs won't get updated/recompiled. Regards Colin There are 10 types of people in the world, those that understand binary and those that don't >From: "scottishpoet" <dryburghj@yahoo.com> >To: informix-list@iiug.org >Subject: Re: locks on sysprocplan >Date: 28 Apr 2006 05:01:00 -0700 > >This means that any locks on >sysprocplan will stay until that user transaction is committed or >rolled back. Is this true? > >yes, but the updated sysprocplan is not rolled back and so would not be >recompiled again > Is there any workaround or fix that will >make the db engine not depend on a user transaction to commit or abort >to release locks on system tables? > >yes, recompile all you stored procedures any time you you do something >that will change the Version number of your table > >eg update statistics or change permissions > > >Statistics on all tables are updated by a nightly script and these >errors occured in the early hours of the following morning. One >possible explanation I see for this would be that a session executed a >stored procedure within a transaction sometime after the nightly >"update statistics" job and that because of the updated statistics this > >stored procedure was recompiled. Due to a software error in the client >application the transaction was neither committed nor rolled back but >the session stayed alive since the application was still running. Does >this make sense? > >yes > >Would adding "update statistics for procedure..." for all stored >procedures that belong to this application lower the risk for locks on >sysprocplan? > >yes > >Does the order of execution in the "update statistics for >procedure" make any difference (ie should I update the most commonly >executed procedures last to ensure that their execution plans are >stored)? > >I wouldn't run them seperately but run the command that updates all the >procedures > >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters