Error -243 ISAM -154 with Select sentence ✓ Answered: green (solid confidence) — Roger confirmed the root cause Jacques suggested: an external ESQL/C library function silently overrode the session's dirty-read isolation with 'set isolation to committed read', explaining the unexpected lock-timeout on a select. Roger's own lab test had already refuted an earlier exclusive-lock theory.

Advisory only.

Hi, We have a program esql/c that has the following sentences: set isolation to dirty read; set lock mode to wait 30; The program makes several select, update and insert operations Recently these error messages came out when executing a select statement: error -243 (Could not position within a table) ISAM -154 (Lock Timeout Expired) I wonder how it is possible that he has shown a blocking message despite having the sentence set isolation to dirty read; The really weird thing is that this error occurred after executing a Select statement. My environment is 11.70FC8 in HP-UX 11.31 Thanks in advance.

Hi Roger. Row locks due to uncommitted transactions are ignored with "set isolation to dirty read" in force, but select statements can still be blocked by other types of exclusive locks. For example, you could get that result if another session has executed the following and still has the transaction open: BEGIN WORK; LOCK TABLE <table-name> IN EXCLUSIVE MODE; There might also be another session about to make a schema change, particularly if IFX_DIRTY_WAIT has been set: www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_08 91.htm Regards, Doug

Original post: Hi, We have a program esql/c that has the following sentences: set isolation to dirty read; set lock mode to wait 30; The program makes several select, update and insert operations Recently these error messages came out when executing a select statement: error -243 (Could not position within a table) ISAM -154 (Lock Timeout Expired) I wonder how it is possible that he has shown a blocking message despite having the sentence set isolation to dirty read; The really weird thing is that this error occurred after executing a Select statement. My environment is 11.70FC8 in HP-UX 11.31 Thanks in advance. Response: Well there's a couple things that pop to mind. First is isolation level is retained, meaning that if your esql/c program happened to execute a SPL and in the SPL it had code that did set isolation to something other then dirty ready, but didn't set it back before leaving, then the session wouldn't be executing at dirty read anymore (and thus could possibly wait for locks on a select and get the lock timeout error). Also, there is the $ONCONFIG parameter USELASTCOMMITED. That can override what you set your isolation to in the application and cause it to use committed read last committed isolation instead. Certain type of locks are waited on when using that isolation level and could therefore also get the -154 timeout error. I guess first thing I would do is verify the esql/c app is running (for it's duration) at the isolation level you think (ie dirty read) using onstat -g ses <session #> output. There's a isolation level field in there, and/or at least check to see if USELASTCOMMITTED is set to something or not. Jacques Renaut HCL Informix Advanced Support

Thanks Doug, But according to the article: http://informix-technology.blogspot.com/2006/10/when-exclusive-is-not-really-exc lusive.html "When you set the exclusive lock on a table using "BEGIN WORK; LOCK TABLE IN EXCLUSIVE MODE you don't prevent sessions with DIRTY READ isolation level from accessing the table" I've made a little laboratory, and that's certainly true.

Thanks Jacques. You're right. We were monitoring the program and effectively the isolation level is comitted read. Investigating we realize that the main program uses esql functions that are in an external library. One of these functions executes the command "set isolation to committed read" with which it overrride the initial setting (dirty read) of the program. Best regards.