IBM Informix: getting 245, 144 error doing a select while another transaction has done an insert - possible bug?

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.