To change lock mode:
alter table XXX lock mode (page|row);
For row lock mode you need to define more locks in the onconfig - then you have less chances for waitings. The management of the locks costs some. If you have many users to the same table on the same time - think about row.
- Default lock mode can be changed in the newer engines
(IDS 9.3 and 9.4) with the following parameter:
This parameter can be set to page or row (where page is the default)
- You can avoid a 4GL prgram to crash at an error with the statement:
WHENEVER ERROR CONTINUE
- Why it works with an index and not without is also quite logical.
Without an index IDS has to search/investigate all rows for satisfying
conditions. Table is read sequentially, everything is read. By default (in
a logged database) the isolation level is commited read. So every
record that is in an open transaction may not be read. So Informix will
say: Record locked.
With an index you scan the tree (which is not locked) and only some
rows, so lock contention is less likely. So this works.
- When raising such an issue always take a look at:
+ Lock level (page/row)
+ ISOLATION LEVEL
+ LOCK MODE
+ Error capturing in your application
These four things must be set correctly to get the desired goal.