Unix Technical Forum

SPL Contention

This is a discussion on SPL Contention within the Informix forums, part of the Database Server Software category; --> -- Solaris 2.6 -- -- IDS 9.4 UC4 -- I ran into an apparent contention problem today with a ...


Go Back   Unix Technical Forum > Database Server Software > Informix

SPL Contention SPL Contention

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 04-20-2008, 12:30 PM
Ben
 
Posts: n/a
Default 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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 04-20-2008, 12:30 PM
bozon
 
Posts: n/a
Default Re: SPL Contention
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>&nbsp;</DIV>
> <DIV><FONT size=2>I ran into an apparent contention&nbsp;problem today with a
> stored procedure.</FONT></DIV>
> <DIV><FONT size=2></FONT>&nbsp;</DIV>
> <DIV><FONT size=2>SPL 'X'</FONT></DIV>
> <DIV><FONT size=2></FONT>&nbsp;</DIV>
> <DIV><FONT size=2>process 1 executes SPL 'X' many times in serial</FONT></DIV>
> <DIV><FONT size=2></FONT>&nbsp;</DIV>
> <DIV><FONT size=2>process 2 executes SPL 'X' once every few minutes</FONT></DIV>
> <DIV><FONT size=2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
> <DIV><FONT size=2>Any ideas?&nbsp; Thank you in advance.</FONT></DIV>
> <DIV><FONT size=2></FONT>&nbsp;</DIV>
> <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 04-20-2008, 12:30 PM
Art S. Kagel
 
Posts: n/a
Default Re: SPL Contention
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

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 04-20-2008, 12:30 PM
scottishpoet
 
Posts: n/a
Default Re: SPL Contention

bozon wrote:
> 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.

updating the statistics of the procedure may resvolve the 211 error

What can cause this problem is that something changes the version
number of a table the procedure uses which invalidates the query plans
of the procdure causing it to be recomplied. Subsequently running
update statistics on the procedure will manually regenrate the query
plans

things that can change the version number of the table include but are
not limited to :

1/ update statistics of a table

2/ adding or revoking priviledges

3/ modifying the columns

4/ adding or removing indexes

Some older versions of the ODBC driver were forcing update statistics
on tables and causing this problem to occur.

run SELECT taname, version FROM systables

for each of the tables your procedure references and see if and when
the value of version changes



>
> 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>&nbsp;</DIV>
> > <DIV><FONT size=2>I ran into an apparent contention&nbsp;problem today with a
> > stored procedure.</FONT></DIV>
> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
> > <DIV><FONT size=2>SPL 'X'</FONT></DIV>
> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
> > <DIV><FONT size=2>process 1 executes SPL 'X' many times in serial</FONT></DIV>
> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
> > <DIV><FONT size=2>process 2 executes SPL 'X' once every few minutes</FONT></DIV>
> > <DIV><FONT size=2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
> > <DIV><FONT size=2>Any ideas?&nbsp; Thank you in advance.</FONT></DIV>
> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
> > <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML>
> >
> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 04-20-2008, 12:31 PM
Ben
 
Posts: n/a
Default Re: SPL Contention
Thank you for all of your responses - they have been very helpful and I
really appreciate it.

Have a good weekend!

Ben


----- Original Message -----
From: "scottishpoet" <dryburghj@yahoo.com>
Newsgroups: comp.databases.informix
To: <informix-list@iiug.org>
Sent: Thursday, July 06, 2006 5:49 PM
Subject: Re: SPL Contention


>
> bozon wrote:
>> 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.
>
> updating the statistics of the procedure may resvolve the 211 error
>
> What can cause this problem is that something changes the version
> number of a table the procedure uses which invalidates the query plans
> of the procdure causing it to be recomplied. Subsequently running
> update statistics on the procedure will manually regenrate the query
> plans
>
> things that can change the version number of the table include but are
> not limited to :
>
> 1/ update statistics of a table
>
> 2/ adding or revoking priviledges
>
> 3/ modifying the columns
>
> 4/ adding or removing indexes
>
> Some older versions of the ODBC driver were forcing update statistics
> on tables and causing this problem to occur.
>
> run SELECT taname, version FROM systables
>
> for each of the tables your procedure references and see if and when
> the value of version changes
>
>
>
>>
>> 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>&nbsp;</DIV>
>> > <DIV><FONT size=2>I ran into an apparent contention&nbsp;problem today
>> > with a
>> > stored procedure.</FONT></DIV>
>> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
>> > <DIV><FONT size=2>SPL 'X'</FONT></DIV>
>> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
>> > <DIV><FONT size=2>process 1 executes SPL 'X' many times in
>> > serial</FONT></DIV>
>> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
>> > <DIV><FONT size=2>process 2 executes SPL 'X' once every few
>> > minutes</FONT></DIV>
>> > <DIV><FONT size=2></FONT>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
>> > <DIV><FONT size=2>Any ideas?&nbsp; Thank you in advance.</FONT></DIV>
>> > <DIV><FONT size=2></FONT>&nbsp;</DIV>
>> > <DIV><FONT size=2>Ben</FONT></DIV></BODY></HTML>
>> >
>> > ------=_NextPart_000_001C_01C6A0FF.DA9D14B0--
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« RE: statistics | fragmentation »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 10:59 AM.

Contact Us - Unix Technical Forums - Top

Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0
www.UnixAdminTalk.com