SPL Contention
-- Solaris 2.6 -- -- IDS 9.4 UC4 -- I ran into an apparent contention problem today with a stored procedure. SPL 'X' process 1 executes SPL 'X' many times in serial process 2 executes SPL 'X' once every few minutes process 2 cannot execute SPL 'X' while process 1 is running - returns -211 error. I have searched Google and found similiar issues but no solid solutions - it seems that one or more of the sys* tables is locked in exclusive mode which prevents process 2 from executing the SPL. Any ideas? Thank you in advance. Ben
Make sure that you have explicitly updated statistics on this procedure. I believe that you get this problem if the procedure had its statistics updated. Ben wrote: > -- Solaris 2.6 -- > -- IDS 9.4 UC4 -- > > I ran into an apparent contention problem today with a stored procedure. > > SPL 'X' > > process 1 executes SPL 'X' many times in serial > > process 2 executes SPL 'X' once every few minutes > > process 2 cannot execute SPL 'X' while process 1 is running - returns -211 error. > > I have searched Google and found similiar issues but no solid solutions - it seems that one or more of the sys* tables is locked in exclusive mode which prevents process 2 from executing the SPL. > > Any ideas? Thank you in advance. > > Ben > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0 > Content-Type: text/html; charset=iso-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 1526 > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> > <META content="MSHTML 6.00.2900.2873" name=GENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY bgColor=#ffffff> > <DIV><FONT size=2> > <DIV><FONT size=2>-- Solaris 2.6 --</FONT></DIV> > <DIV><FONT size=2>-- IDS 9.4 UC4 --</FONT></DIV></FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>I ran into an apparent contention problem today with a > stored procedure.</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>SPL 'X'</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>process 1 executes SPL 'X' many times in serial</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>process 2 executes SPL 'X' once every few minutes</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>process 2 cannot execute SPL 'X' while process 1 is running - > returns -211 error.</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>I have searched Google and found similiar issues but no solid > solutions - it seems that one or more of the sys* tables is locked in exclusive > mode which prevents process 2 from executing the SPL.</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>Any ideas? Thank you in advance.</FONT></DIV> > <DIV><FONT size=2></FONT> </DIV> > <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML> > > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--
Ben wrote: > -- Solaris 2.6 -- > -- IDS 9.4 UC4 -- > > I ran into an apparent contention problem today with a stored procedure. > > SPL 'X' > > process 1 executes SPL 'X' many times in serial > > process 2 executes SPL 'X' once every few minutes > > process 2 cannot execute SPL 'X' while process 1 is running - returns > -211 error. > > I have searched Google and found similiar issues but no solid solutions > - it seems that one or more of the sys* tables is locked in exclusive > mode which prevents process 2 from executing the SPL. > > Any ideas? Thank you in advance. This will happen with stored procedures which create and destroy tables. What happens is that running the procedure invalidates the routine's stored query plans causing it to be recompiled automatically the next time it is run which locks the sysprocplan and sysprocbody records for the routine. This in turn prevents other users from getting the shared lock on those records that are needed to prevent recompilation while the routine is being executed by the second user. If the routine is only dealing with temp tables SOMETIMES running UPDATE STATISTICS on the procedure will solve the problem. However, if the proc does a "create temp table..." trapping the error if the table already exists (which happens if a user short circuits the run of a procedure that returns many records and cleans up the temp table at after exiting the cursor loop), then drops and recreates it, you're hosed. You have to rewrite the proc to do a DROP TABLE and trap/ignore the NOT FOUND error instead and always create the table. That tends to be less troublesome. Art S. Kagel