During my 14+ years of providing Informix services and support for XTIVIA’s Virtual-DBA remote database administration service, this is one of the more common errors that I am asked to address. I want to share three possible fixes for this problem.
1) Check the lock mode of the tables involved. If a table is set to page-level locking, then a lock will be placed on an entire data page when a row is updated. This leads to a greater chance of lock contention. In versions of Informix prior to 11, the default table lock mode was “page”. Informix version 11 introduced the onconfig parameter DEF_TABLE_LOCKMODE and environment variable IFX_DEF_TABLE_LOCKMODE to allow the default to be set to “row”. If you want to change the current lock mode of a table from page to row, simply run the following alter command:
ALTER TABLE tabname LOCK MODE (ROW);2) Use “SET LOCK MODE TO WAIT”
By default, if an application or stored procedure encounters a lock when trying to read data, it will throw an error. Locks are normally transient, so you can use the lock mode wait statement to cause the application process to wait for the lock to be released before proceeding. You have the option of specifying a number of seconds as the maximum that it should wait before returning an error. Alternatively, if you do want an error returned immediately, it can be set to “not wait”.
SET LOCK MODE TO WAIT; -- Will wait indefinitely
SET LOCK MODE TO WAIT 20; -- Will wait 20 seconds
SET LOCK MODE TO NOT WAIT; -- Will fail immediately3) USE LAST COMMITTED
This option was also introduced in Informix Dynamic Server version 11. The default isolation level for non-ANSI logged databases is “Committed Read”. This causes reads against the database to check for locks before returning a row so that uncommitted rows will not be returned. An error will be reported if a lock is encountered.
The “last committed” option used in conjunction with this isolation level will reduce the possibility of errors when a locked row is detected. This will instruct the database server to return the most recently committed version of the row rather than returning an error or waiting for a lock to be released. This is effective only for concurrent read operations and will not change the behavior of two processes that are trying to write to the same row. The keywords will also have no effect on tables with a lock mode of page.
This option can be set with either the USELASTCOMMITTED onconfig parameter or environment variable. In order to instruct transactions to use the last committed value, set this parameter or environment variable to “Committed Read” or “All”. The onconfig parameter can be set with onmode –wf or –wm if bouncing the instance isn’t feasible.
These suggestions have provided good results for our clients. I hope that they will help you to eliminate most of your lock contention problems.