Encountered this problem in production in the form of a deadlock. Figured out that if a transaction was inserting a row on my table, and I wanted to select a totally different row from that table, I would get the following error:
245: Could not position within a file via an index.
144: ISAM error: key value locked
Error in line 1
Near character position 70
My select statement was of the form select * from table where bar = 3 and foo = "CCCC";, where "foo" is a foreign key to a table with 18 rows, and "bar" is the first table's primary key. My insert statement was also inserting a row with foo = "CCCC". Curiously, the select query also returned the desired row before outputting the error.
I tried all this on informix 12.10 with isolation level set to repeatable read. I tried it on production, and in a fresh DB I set up with only the two tables mentioned. The lock mode of both tables is "row".
I investigated by modifying the select statement: select * from table where bar = 3; would not fail. Also, select * from table where bar = 3 and foo = "CCCC" order by ber; would not fail (ber being a random field from the table, ber is not indexed).
I would expect all the select statements I tried to return the desired row without error, OR all of them to fail. My solution in production was to order by a random field in the table, which fixed the deadlock issue
Does anyone know why this issue could have happened ? I suspect it is linked to the indexes on the table, which were all created automatically when adding the primary and foreign keys to the table. But I do not know enough about indexes to understand what happened. Could this be a bug ?
Schema of the tables:
create table options (
foo char(4) not null,
fee int not null)
extent size 16 next size 16
lock mode row;
alter table options add constraint (
primary key (foo)
constraint cons1 );
create table decisions (
bar char(3) not null,
foo char(4) not null,
ber int not null)
extent size 131072 next size 65536
lock mode row;
alter table decisions add constraint (
primary key (bar)
constraint cons2 );
alter table decisions add constraint (
foreign key (foo) references options(foo)
constraint cons3 );
Data I inserted into the "options" table:
AAAA|0|
BBBB|0|
CCCC|1|
DDDD|4|
EEEE|1|
FFFF|8|
Data I inserted into the "decisions" table:
QWE|AAAA|0|
WER|AAAA|9|
ERT|CCCC|2|
RTY|AAAA|32|
TYU|CCCC|1234|
YUI|CCCC|42398|
UIO|AAAA|23178|
IOP|CCCC|1233|
OPA|CCCC|11|
PAS|AAAA|890|
ASD|AAAA|90|
SDF|CCCC|2|
DFG|AAAA|4|
FGH|CCCC|7|
Edit: I used set explain on; for the queries.
select * from decisions where foo = "CCCC" and bar = "QWE" order by foo; returned that the index used was on foo="CCCC". However, for select * from decisions where foo = "CCCC" and bar = "QWE" order by ber;, it's indexed on bar="QWE".
I asked my question to more experienced people working with me and here is the answer:
When something updates a row in the table (for example inserting a row) in a transaction the index keys for the values inserted get locked. Since I was inserting a row with foo="CCCC", that key value was locked in the foo index. Then, when I did a select statement where foo="CCCC", when that statement used the foo index, it errored, and when it used another index, it worked.
Using Optimizer Directives, you can specify an index to use in a query or an index to avoid.
The solution I chose was, first, to define explicit indexes for my table. Then, I used the AVOID_INDEX directive to avoid the "foo" index.
Other solutions would have been to specify the index to use instead (but I was worried the select could use two indexes and still encounter the foo index); to set the lock mode to WAIT X (but since I found the issue through deadlocks this wouldn't have helped); or to use isolation dirty read (but this was unwanted for the purpose of that specific select).
Following the fix, I haven't gotten a deadlock yet.