Locking Issues

Posted in 2011

SHAHZAD SALAM KASI — Thu, 06 Jan 2011 06:58:45 — source: IIUG Forums & Mailing Lists

Hi, Is there any particular parameters that can reduce the effect of locking in our OLTP environment, as we are facing some issues. The code will be modified soon, bu we want to know that can we do something while modifying some of the parameters in config file, to handle / reduce this effect. We want to handle locking and I/O contention issues. Thanks.

↪ replying to SHAHZAD SALAM KASI

Art Kagel — Thu, 06 Jan 2011 11:15:13 — source: IIUG Forums & Mailing Lists

VERSION VERSION VERSION?!?!?!?!?!?!?!?!?!?!?!?!? PLEASE! Post your version and platform information whenever you post a new question! The answer you need may depend on what version you are on. If you are on 11.50 or later, you can set up a login procedure to change the isolation level for users to COMMITTED READ with the LAST COMMITTED option. That will reduce lock contention. You are only going to reduce IO contention by reorganizing your storage through analyzing existing access patterns of your tables and indexes and moving highly active tables and indexes to independent storage. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 6, 2011 at 1:58 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com> wrote: > Hi, > Is there any particular parameters that can reduce the effect of locking in > our OLTP environment, as we are facing some issues. The code will be > modified > soon, bu we want to know that can we do something while modifying some of > the > parameters in config file, to handle / reduce this effect. > We want to handle locking and I/O contention issues. > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5c1eb2cbd1d04992b9d05

↪ replying to Art Kagel

SHAHZAD SALAM KASI — Thu, 06 Jan 2011 11:17:22 — source: IIUG Forums & Mailing Lists

We are using IDS 11.50.FC6 on Solaris Sparc. Sorry for inconvenience.

↪ replying to Art Kagel

Alexandre Marini — Thu, 06 Jan 2011 11:34:02 — source: IIUG Forums & Mailing Lists

Hello. Another issue that has a great impact on lock issues, on 11.50 (and up), is the lock level of tables. That should be recommended to change its lock level to row, instead of page level locking, even most on larger page sizes ;) Best regards. Em 06/01/2011 07:15, Art Kagel escreveu: > VERSION VERSION VERSION?!?!?!?!?!?!?!?!?!?!?!?!? > > PLEASE! Post your version and platform information whenever you post a new > question! > > The answer you need may depend on what version you are on. > > If you are on 11.50 or later, you can set up a login procedure to change the > isolation level for users to COMMITTED READ with the LAST COMMITTED option. > That will reduce lock contention. > > You are only going to reduce IO contention by reorganizing your storage > through analyzing existing access patterns of your tables and indexes and > moving highly active tables and indexes to independent storage. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Jan 6, 2011 at 1:58 AM, SHAHZAD SALAM KASI<skasi@i2cinc.com> wrote: > >> Hi, >> Is there any particular parameters that can reduce the effect of locking in >> our OLTP environment, as we are facing some issues. The code will be >> modified >> soon, bu we want to know that can we do something while modifying some of >> the >> parameters in config file, to handle / reduce this effect. >> We want to handle locking and I/O contention issues. >> >> Thanks. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001636c5c1eb2cbd1d04992b9d05 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11