Informix

 View Only
  • 1.  Table Lock

    Posted Mon March 06, 2023 09:16 AM

    Hi,

    We have 4 servers that run the same services to call stored procedures to validate and insert data to tables, these services run every 10 second by cron.  Sometimes we get this error "Could not position within a file via an index", believe it happen because table lock, same process tries to update same table. Our table mode already set row locks and our store procedures for updating table have set lock wait 10, what else we can do to fix this issue. The current version IDS 14.10FC8 runs on VMware Server Red hat Linux 8.

    Please advice

    Thank You



    ------------------------------
    MOHD FADZIL JUSOH
    ------------------------------


  • 2.  RE: Table Lock

    IBM Champion
    Posted Mon March 06, 2023 10:34 AM

    What your quoting, "Could not position within a file via an index", is the SQL error message.

    It normally comes with an associated ISAM error code/message which indicates the actual lower level reason for the SQL error and which, in this case, likely is some sort of locking error.  Without knowing what precisely that is, it's hard to say what's going on.



    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: Table Lock

    IBM Champion
    Posted Mon March 06, 2023 10:56 AM
    Hi,,
    this error comes up if you have a lock in a row (for whatever reason,
    could be a non-committed update/delete/insert or a cursor for update
    or a table lock because of a DDL manipulation or explicit table lock)
    and try to query it.

    In order to prevent this, you need to change the isolation level.
    I'd expect your check procedures are designed to check only committed data.
    So my assumption would be you could go best with level "committed read last committed".

    You should play with the modes in order to getting the results you want to have.
    Also the database mode (ansi vs. standard logged) makes a difference.


    MARCUS HAARMANN






  • 4.  RE: Table Lock

    IBM Champion
    Posted Mon March 06, 2023 06:34 PM

    Hi,

    1. Ensure transactions are as short as possible i.e. the app does not do BEGIN WORK, lock rows and wait for sometime that is not the database.

    2. This can be because a lock on an index key is the same as locking all rows with the same values in the indexed columns.

    Ensure that the table has a serial or bigserial column and add that to the end of each non-uniqure index or unqiue index with nulls in a column so each index key only covers 1 row.

    Regards,
    David.

    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 5.  RE: Table Lock

    IBM Champion
    Posted Tue March 07, 2023 02:07 AM

    High Mohd,

    I would also try it with "dirty read" and then measure the runtime of the individual calls. Maybe it is because of access from another process and the "lock mode wait 10" is not enough. in case of need, you could switch on the "trace on" for the procedure. So a little research work - but it should work in any case finally as you describe it.

    Good Luck,

    Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 2022 2023
    IIUG Board Member
    ------------------------------



  • 6.  RE: Table Lock

    Posted Tue March 07, 2023 04:04 AM

    On onconfig file have parameter for USELASTCOMMITTED

     USELASTCOMMITTED - Controls the committed read isolation level.
    #                    Acceptable values are:
    #                    - "NONE" Waits on a lock
    #                    - "DIRTY READ" Uses the last committed value in
    #                      place of a dirty read
    #                    - "COMMITTED READ" Uses the last committed value
    #                      in place of a committed read
    #                    - "ALL" Uses the last committed value in place
    #                      of all isolation levels that support the last
    #                      committed option

    Currently we used default value "NONE" , If we change it to "ALL", it will help to solve this problem? OR which option best to used. Our database is in Buffer mode, our java code has set begin work and commit for all process.

    Thank You



    ------------------------------
    MOHD FADZIL JUSOH
    ------------------------------