Translating with DrWatson… this can take a few seconds the first time.
This is a genuine, complex translation. DrWatson protects commands, error codes, and log output while naturally translating the surrounding text. It’s translated once and saved.
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.
Roger Vilca got error -243 / ISAM -154 on a SELECT in an ESQL/C program using dirty read (Informix 11.70FC8, HP-UX). Doug Lawry noted dirty read ignores row locks but not table exclusive locks. Jacques Renaut suggested an SPL changing isolation or USELASTCOMMITTED. Roger found an external library function executed 'set isolation to committed read'. Solved.
ROGER VILCA — — source: IIUG Forums & Mailing Lists
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
↪ replying to DOUG LAWRY
ROGER VILCA — — source: IIUG Forums & Mailing Lists
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.
↪ replying to JACQUES RENAUT
ROGER VILCA — — source: IIUG Forums & Mailing Lists
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.
We use strictly necessary cookies to make this site work. With your
consent we’d also use optional cookies for analytics and marketing. You can accept all,
reject all, or choose. Read our Cookie Policy.