SQL error: -243, ISAM code: -154

Hi Guys, Currently we're facing a situation where we're getting the error in our application. SQL error: -243, ISAM code: -154 The scenario is one thread has completed inserting a record in a table say employee_master and committed second thread while trying to open a cursor on the same table employee_master for the same record gets this error. Any leads will be appreciated. Cheers, Mukesh PS: 1. We've gone through the Informix manuals for the error codes -243 and -154 2. Development environment is ids11 on Linux

This is just a timing problem, almost a race condition. The first inserter's lock is living just a fraction of a second, but during that time the second client is trying to acquire a lock also. Here's the fix, in both sessions (indeed you should be doing this in all of your applications) immediately after connecting to the database (OK after error checking the connection), do: SET LOCK MODE TO WAIT 10; That will allow each session to wait up to 10 seconds for transient locks to clear before returning a lock error. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Mon, Feb 15, 2010 at 5:26 AM, MUKESH RIJHWANI <mukesh.rijhwani@logica.com > wrote: > Hi Guys, > > Currently we're facing a situation where we're getting the error in our > application. > SQL error: -243, ISAM code: -154> > The scenario is > one thread has completed inserting a record in a table say employee_master > and > committed > second thread while trying to open a cursor on the same table > employee_master > for the same record gets this error. > > Any leads will be appreciated. > > Cheers, > Mukesh > > PS: > > 1. We've gone through the Informix manuals for the error codes -243 and > -154 > 2. Development environment is ids11 on Linux > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747373a8d0d56047fa37669

Error -154 means Lock Timeout Expired. - Check how long application waits (NNN seconds) for a lock to be acquired from 'SET LOCK MODE TO WAIT NNN' statement. Increase the wait time little bit, that might help. - Secondly, check what type locking granularity being used -- page or row level lock (IDS defaults to page level). - IDS 11 supports -- optimistic concurrency via "COMMITTED READ LAST COMMITTED" isolation level, see if that helps. Search IDS info center for USELASTCOMMITTED. - Nilesh - ids-bounces@iiug.org wrote on 02/15/2010 04:26:47 AM: > From: > > "MUKESH RIJHWANI" <mukesh.rijhwani@logica.com> > > To: > > ids@iiug.org > > Date: > > 02/15/2010 04:28 AM > > Subject: > > SQL error: -243, ISAM code: -154 [19046].> > Sent by: > > ids-bounces@iiug.org > > Hi Guys, > > Currently we're facing a situation where we're getting the error in our > application. > SQL error: -243, ISAM code: -154> > The scenario is > one thread has completed inserting a record in a table say > employee_master and > committed > second thread while trying to open a cursor on the same table employee_master > for the same record gets this error. > > Any leads will be appreciated. > > Cheers, > Mukesh > > PS: > > 1. We've gone through the Informix manuals for the error codes -243 and -154 > 2. Development environment is ids11 on Linux > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Another few things to look at are: 1) You have the statistics updated and are using an index and not a sequential scan to find records. You tend to encounter more locks with sequential scans. 2) If you are using version 11 you can consider the last committed option. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/15/2010 05:19:17 AM: > [image removed] > > Re: SQL error: -243, ISAM code: -154 [19047] > > Art Kagel > > to: > > ids > > 02/15/2010 05:21 AM, > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > This is just a timing problem, almost a race condition. The first > inserter's lock is living just a fraction of a second, but during that time > the second client is trying to acquire a lock also. Here's the fix, in both > sessions (indeed you should be doing this in all of your applications) > immediately after connecting to the database (OK after error checking the > connection), do: > > SET LOCK MODE TO WAIT 10;> > That will allow each session to wait up to 10 seconds for transient locks to > clear before returning a lock error. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Mon, Feb 15, 2010 at 5:26 AM, MUKESH RIJHWANI <mukesh.rijhwani@logica.com > > wrote: > > > Hi Guys, > > > > Currently we're facing a situation where we're getting the error in our > > application. > > SQL error: -243, ISAM code: -154> > > > The scenario is > > one thread has completed inserting a record in a table say employee_master > > and > > committed > > second thread while trying to open a cursor on the same table > > employee_master > > for the same record gets this error. > > > > Any leads will be appreciated. > > > > Cheers, > > Mukesh > > > > PS: > > > > 1. We've gone through the Informix manuals for the error codes -243 and > > -154 > > 2. Development environment is ids11 on Linux > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00151747373a8d0d56047fa37669 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >