Table Lock

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 ------------------------------

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 ------------------------------

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. https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-set-isolation-statement

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 ------------------------------

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 ------------------------------

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 ------------------------------