DEF_LOCKMODE onconfig parameter
We converted from 9.21 to 9.40FCX7 a few months back. One of the second set of parameter changes I made was to implement the DEF_LOCKMODE=ROW configuration parameter. Lately, the developers have been coming to the dba's about locking issues. Consistently, there is are locking issues with a very small table ( row size = 49 number of columns = 3 index size = 0 and row count=98)-that is accessed by many other tables. How does the database handle this parameter internally ? Why would this parameter cause issues with lock table overflow and performance degradation for tables that were previously created as lock mode (page)? I am curious if anyone else has experienced issues with this new parameter. It sounded like such a good option. We are running on Sun Solaris 8 and use an outside vendor for 90 % of the code. There are some tables/code that were created by the developers for different functions and they are researching the requirements on their tables first.
I have had some issues since going to 9.40.FCX with small tables not fuctioning the same way as in earlier versions. In the past Indexes would be used where they are not being now. Using directives have allowed us to get around the issue but we have not looked any futher at this time. In a none logged database we will get a locking error when one person is updating a row and the next person wants to update any row physical past that row. Again we have found it seems to be a index use problem as the directive keeps us out of this problem. Maybe I should follow up with Informix support to make sure the latest behavor is expected... The index is there to avoid locking issues... If you can have your developers look at adding the avoid full directive or an index directive to force the using of the index on the query. The problem appears that the cost is lower on a table scan vs. index so when someone has something locked you run into problems. Question/Statement check for the group.. Please point out how this question is... if it is; In a none logged system an update lock on a given row should not stop the next user form locating and locking a different row as the default is dirty read. Correct? On 3/20/06, LISA NELSON <lisa.nelson@agedwards.com> wrote: > > We converted from 9.21 to 9.40FCX7 a few months back. One of the second set of > parameter changes I made was to implement the DEF_LOCKMODE=ROW configuration > parameter. > > Lately, the developers have been coming to the dba's about locking issues. > Consistently, there is are locking issues with a very small table ( row size = > 49 number of columns = 3 index size = 0 and row count=98)-that is accessed by > many other tables. > > How does the database handle this parameter internally ? Why would this > parameter cause issues with lock table overflow and performance degradation > for tables that were previously created as lock mode (page)? > > I am curious if anyone else has experienced issues with this new parameter. It > sounded like such a good option. > > We are running on Sun Solaris 8 and use an outside vendor for 90 % of the > code. There are some tables/code that were created by the developers for > different functions and they are researching the requirements on their tables > first. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Hi,
you could set
OPTCOMPIND 0in the onconfig-file (for whole server) or in the application environment.
With that setting IDS should prefer index access - regardless if the cost
of full table scan is lower (SAP R/3 runs with this parameter set). But
you might get performance problems when joining large tables with large
result sets, depends on your application.
Regards,
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> Eric Rowell
> Gesendet: Dienstag, 21. März 2006 20:29
> An: ids@iiug.org
> Betreff: Re: DEF_LOCKMODE onconfig parameter [6527]
>
>
>
> I have had some issues since going to 9.40.FCX with small tables not
> fuctioning the same way as in earlier versions. In the past Indexes
> would be used where they are not being now. Using directives have
> allowed us to get around the issue but we have not looked any futher
> at this time. In a none logged database we will get a locking error
> when one person is updating a row and the next person wants to update
> any row physical past that row. Again we have found it seems to be a
> index use problem as the directive keeps us out of this problem.
> Maybe I should follow up with Informix support to make sure
> the latest
> behavor is expected... The index is there to avoid locking issues...
>
> If you can have your developers look at adding the avoid full
> directive or an index directive to force the using of the
> index on the
> query. The problem appears that the cost is lower on a table scan vs.
> index so when someone has something locked you run into problems.
>
> Question/Statement check for the group.. Please point out how this
> question is... if it is;
>
> In a none logged system an update lock on a given row should not stop
> the next user form locating and locking a different row as
> the default
> is dirty read. Correct?
>
> On 3/20/06, LISA NELSON <lisa.nelson@agedwards.com> wrote:
> >
> > We converted from 9.21 to 9.40FCX7 a few months back. One
> of the second set
> of
> > parameter changes I made was to implement the
> DEF_LOCKMODE=ROW configuration
> > parameter.
> >
> > Lately, the developers have been coming to the dba's about
> locking issues.
> > Consistently, there is are locking issues with a very small
> table ( row size
> =
> > 49 number of columns = 3 index size = 0 and row
> count=98)-that is accessed
> by
> > many other tables.
> >
> > How does the database handle this parameter internally ?
> Why would this
> > parameter cause issues with lock table overflow and
> performance degradation
> > for tables that were previously created as lock mode (page)?
> >
> > I am curious if anyone else has experienced issues with
> this new parameter.
> It
> > sounded like such a good option.
> >
> > We are running on Sun Solaris 8 and use an outside vendor
> for 90 % of the
> > code. There are some tables/code that were created by the
> developers for
> > different functions and they are researching the
> requirements on their
> tables
> > first.
> >
> >
> >
> **************************************************************
> *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
I would agree this should be true and I have had a few systems in the
past that had this set. Problem is in a none logged system this
appears to have no effect as there is only one isolation mode.
For Lisa this could be an option. But do watch out for your other queries.
On 3/22/06, Andreas.KUT.... <andreas.kutsche@spar.at> wrote:
>
> Hi,
>
> you could set
> OPTCOMPIND 0> in the onconfig-file (for whole server) or in the application environment.
> With that setting IDS should prefer index access - regardless if the cost
> of full table scan is lower (SAP R/3 runs with this parameter set). But
> you might get performance problems when joining large tables with large
> result sets, depends on your application.
>
> Regards,
> Andreas Kutsche
>
> >
> -------------------------------------------
> SPAR Oesterreichische Warenhandels-AG
> Hauptzentrale
> Europastrasse 3
> A - 5015 Salzburg
>
> Tel: +43 662 4470 24223
> Mobile: +43 664 6259575
> E-Mail: Andreas.KUTSCHE@spar.at
> Internet: http://www.spar.at
> -------------------------------------------
> -----Ursprüngliche Nachricht-----
>
> > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> > Eric Rowell
> > Gesendet: Dienstag, 21. März 2006 20:29
> > An: ids@iiug.org
> > Betreff: Re: DEF_LOCKMODE onconfig parameter [6527]
> >
> >
> >
> > I have had some issues since going to 9.40.FCX with small tables not
> > fuctioning the same way as in earlier versions. In the past Indexes
> > would be used where they are not being now. Using directives have
> > allowed us to get around the issue but we have not looked any futher
> > at this time. In a none logged database we will get a locking error
> > when one person is updating a row and the next person wants to update
> > any row physical past that row. Again we have found it seems to be a
> > index use problem as the directive keeps us out of this problem.
> > Maybe I should follow up with Informix support to make sure
> > the latest
> > behavor is expected... The index is there to avoid locking issues...
> >
> > If you can have your developers look at adding the avoid full
> > directive or an index directive to force the using of the
> > index on the
> > query. The problem appears that the cost is lower on a table scan vs.
> > index so when someone has something locked you run into problems.
> >
> > Question/Statement check for the group.. Please point out how this
> > question is... if it is;
> >
> > In a none logged system an update lock on a given row should not stop
> > the next user form locating and locking a different row as
> > the default
> > is dirty read. Correct?
> >
> > On 3/20/06, LISA NELSON <lisa.nelson@agedwards.com> wrote:
> > >
> > > We converted from 9.21 to 9.40FCX7 a few months back. One
> > of the second set
> > of
> > > parameter changes I made was to implement the
> > DEF_LOCKMODE=ROW configuration
> > > parameter.
> > >
> > > Lately, the developers have been coming to the dba's about
> > locking issues.
> > > Consistently, there is are locking issues with a very small
> > table ( row size
> > =
> > > 49 number of columns = 3 index size = 0 and row
> > count=98)-that is accessed
> > by
> > > many other tables.
> > >
> > > How does the database handle this parameter internally ?
> > Why would this
> > > parameter cause issues with lock table overflow and
> > performance degradation
> > > for tables that were previously created as lock mode (page)?
> > >
> > > I am curious if anyone else has experienced issues with
> > this new parameter.
> > It
> > > sounded like such a good option.
> > >
> > > We are running on Sun Solaris 8 and use an outside vendor
> > for 90 % of the
> > > code. There are some tables/code that were created by the
> > developers for
> > > different functions and they are researching the
> > requirements on their
> > tables
> > > first.
> > >
> > >
> > >
> > **************************************************************
> > *****************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> >
> > **************************************************************
> > *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>