Database is created WITH LOG but with No ANSI option. We put transaction around each individual insert/update/fetch. One process is reading from a table with blob data, while the other process is deleting/updating from the same table. The reading process got 604/164 error and became hung. According to the Informix documentation, under Commited Read, no lock is placed on an undeleted row when it is read. BYTE or TEXT data is read in a second step, after the row has been fetched. During this lengthy step, it is possible for another program to delete this row, commit the deletion, and for the space on the disk page to be reused. If the space has been reused in the interim, the blob timestamp is greater than the timestamp in the forward pointer. In this case, the comparison indicates the obsolete pointer information and the inconsistency is reported. I have a feeling that this is exactly what is occurring in our case, but nothing we have tried or has been suggested to try seems to improve the problem.
Do you think there is anything else we can do to solve the problem?

You might check the following:

strings $INFORMIXDIR/bin/oninit | grep CR_LOCKBLOB

If you find this variable in the above command, you can
try this:

1) Stop the databaseserver (onmode -ky)
2) Set the env variable (export CR_LOCKBLOB=1)
3) Re-Start the databaseserver (oninit)

This variable should prevent the error -604/-164 while
reading blob data with the isolation level "committed read".

Thanks Eric very much for your reply. Actually, we have been playing around with the that env variable CR_LOCKBLOB. Inoformix support gave it to us as a clue to the problem solving. During fetches with the env variable CR_LOCKBLOB set and isolation level committed read, the server will lock tuples with blobs in shared mode and will retain the locks until the actual blobs are returned. For the env variable CR_LOCKBLOB to function correctly i.e to retain locks we should do an explicit begin and commit work. However, when we set the CR_LOCKBLOB to 1 the things got worse. It looks like the manner in
which the fetch is executed is different when CR_LOCKBLOB is set/unset. I do not understand why the lock is not released after each fetch when CR_LOCKBLOB is set. Any ideas? Does anybody have some additional sources abput CR_LOCKBLOB or prehaps some alternative solutions?

Thanks!

Hmm. Unfortunately I have no additonal informtion
about CR_LOCKBLOB.
I know that it has been primarily implemented for
SAP.
IFMX tech support or better advanced support should
have more information on CR_LOCKBLOB.

Are you reading with committed read or repeatable
read ?
Are you doing any updates on the blobs you are
reading ?

Yes, we are using Commited read. The problem occurs when we read from a table that contains blob data and in parallel of this some other process updates/deletes the table raw and the blob. The table has lock level set to row. The number of rows there are millions. Initially, the problem was the error 604/164 (the one from the subject). In order to avoid it, we decided to set CR_LOCKBLOB (as you also suggested). Once we set CR_LOCKBLOB to 1 we found that Informix has different approach when fetching the rows. We implement the application with explicit "BEGIN" and "COMMIT" statements executed for each insert, update, select and delete and we run out of locks when the number of rows is ~ millions. The LOCKS variable is set to 250,000 in the onconfig file. When the number is around 30,000 rows there is no problem. HOwever, if the number is millions of rows it appears as if all locks are being used even when limiting the reading to this number of rows (30 000). That's what we are trying to figure out - How is locking implemented with CR_LOCKBLOB set? According to the Informix manuals, after a COMMIT is issued, any locks should be freed. However, that does not seem to be occurring in this case.
We believe that CR_LOCKBLOB has an effect on the locking mechanism implemented because we have placed a debug statement to be logged after each fetch issued by the report is performed. With CR_LOCKBLOB NOT set, this message is logged after each fetch completes. With CR_LOCKBLOB set, this message is logged only once and we see the number of locks being used increase dramatically during this time.

Regards!

A commit should always release all locks,
otherwise I would consider this as an
IFMX bug.

I've tested the behaviour of CR_LOCKBLOB set
to 1 on a Suse Linux, Kernel 2.4.18 with
IDS 9.30.UC3. From my investigations what
seems to happen is that as soon as the
first fetch is executed every row in the
resultset will get a shared lock placed on
it. After the fetch on a row has been
performed, the shared lock on ths row will be
released.
When a commit inside the fetch-loop (prerequiste
is a cursed declared with hold) is executed,
all locks will be released, however a subsequent
fetch will not place any locks, so you might ran
again in the -604 problem.

The tests has been performed against an unbuffered-
logging database with a table containing a blob
column which resides in a separate blobspace. The
table has row-level-locking enabled.

That said I believe that you might solve the problem
by partitioning you query into smaller parts, which
are executed in separate transactions, avoiding the
locking of a large resultset.
Switching the table to page-level-locking might also
help reducing the number of locks, however concurrency
will also be reduced.

I could not reproduce the behaviour you described
regarding commit not releasing locks or debug messages
that will not be logged if CR_LOCKBLOB is set.

However this might also be different depending on the
IFMX version in use. My recommendation is that you
talk to IFMX tech support, preferably to someone in
advanced support with sourcecode knowledge. They should
be able to tell you what is exactly happening if
CR_LOCKBLOB is set.

You might also try to write a small reproduction esql/c
program based on the stores-database and the table
table 'catalog' which contains blobs (remember to switch
this table to row-level-locking). This will help IFMX
tech support reproducing the problem.

Seems like we are going to solve this problem by changing the Isolation level from Committed Read to Cursor Stability. This will fix our problem but there will be another issue - performance. Time for running reports will require more time and we need to investigate it further. Thanks again and have a great day.