SAP Note 27181 - Deadlock when updating, despite note 68476

Symptom:

Despite the existence of the database indices according to note 68476, the INFORMIX ISAM error:
INF143 "deadlock detected"
occurs in connection with the Informix SQL errors
INF240 "Could not delete a row"
INF244 "Could not do a physical-order read to fetch next row"
INF246 "Could not do an indexed read to get the next row"
INF271 "Could not insert new row into the table"
during updates on one of the tables VBLOG, GLT0, RFBLG and REGUV.

Cause and prerequisites

There are an unusually large number of entries (more than 500) in the table VBLOG (release < 3.0A) or in the transparent tables VBHDR, VBMOD and VBDATA.

Solution

The remedy is to update or delete the records, having first checked with the users (SM13).

The most sensible procedure is as follows:

1. Update Statistics on the affected table(s)

In order to make the best use of the access path when processing, the information for the optimizer should be updated. Use the tool 'sapdba' to do this,
For example
sapdba -updstat -threshold 0 -t vbmod .
(See note 42574).

Ensure that the correct index is used:

a) If the SAP Release is prior to 3.0A and the tables VBHDR, VBMOD and VBDATA are not transparent, execute the following statements with 'dbaccess'

set explain on;
select * from vblog where vbkey = 'abc12345';

b) If the SAP Release is greater than or equal to 3.0A or the tables VBHDR, VBMOD and VBDATA are transparent, execute the following statements with 'dbaccess'

set explain on;
select * from vbmod where vbkey = 'abc12345';
select * from vbdata where vbkey = 'abc12345';

The query plan for all of these queries (file sqexplain.out) must always contain the field 'vbkey' as the index used:

QUERY:
------
........

1) sapr3.vb...: INDEX PATH
(1) Index Keys: vbkey

........

2. Reduce the number of update programs for this action

It has proved to be advantageous to avoid starting this action when the system load is high. For the purpose of this action, you are recommended to reduce the number of update processes to 1 or 2.

3. After the number of entries in the tables has returned to a 'normal' level again (< 100), another Update Statistics should be executed.

4. The number of update processes should be reduced as far as possible to a suitable number.