Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Red face Unanswered: Sqlcode -604/isamcode -164

    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?

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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".

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  3. #3
    Join Date
    Mar 2003
    Posts
    4
    Originally posted by eherber
    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!

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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 ?

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  5. #5
    Join Date
    Mar 2003
    Posts
    4
    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!

  6. #6
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  7. #7
    Join Date
    Mar 2003
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •