6.1.11

SAP Note 17267 - SQL Error "ISAM-134: no more locks"

Symptom:


When you
create/process batch input sessions

or
perform a client import, export, copy, or delete

or
import/complete a language

the following message occurs:
Informix SQL error -134 "ISAM error: no more locks"

together with
Informix SQL error -240 (INF240) "Could not delete a row"

or
Informix SQL error -246 (INF246) "Could not do an indexed read to get the next row"

or
Informix SQL error -271 (INF271) "Could not insert new row into the table"

or
Informix SQL error -418 "NULL SQLDA descriptor or host variable list encountered"

or
Informix SQL error -603 (INF603) "Cannot close blob"

or
Informix SQL-Fehler -605 (INF605) "Cannot write blob"

in operations on any tables.

Additional key words


INF240 INF246 INF271 INF418 INF603 INF605 INF134 ISAM134
RSCLICOP RSCLIEXP RSLANGIM RSLANGCP R3TRLANZ

Cause and prerequisites


The number of database locks which can be held simultaneously in a database is limited. A lock request which exceeded this maximum value led to the termination of the relevant transaction.
Usually the DB transaction which terminated is also the one which requested the large number of locks. However, the termination of the DB transaction responsible generally causes a very lengthy rollback, during which the same symptoms can also occur in other transactions.

Solution


As soon as the rollback of the DB transaction responsible is complete, the DB locks held by it exclusively are freely available again. If the SAP transaction responsible is executed again, the same situation generally arises. This can be solved only by carrying out one of the following actions:

    1. Increase the maximum number of DB locks in the DB server:

    The maximum number of DB locks in the DB server is defined via the Informix Profile Parameter 'LOCKS'. You can change this as database administrator either by using 'onmonitor' or by editing the file '$INFORMIXDIR/etc/$ONCONFIG' directly.
    In each case you must restart the database in order to activate this change.

    Up to and including R/3 Version 2.1H/2.2B, that is up to Informix Version 6.00.UE1, the maximum number of DB locks was 256000.
    From R/3 Version 2.1I/2.2C, that is from Informix Version 6.00.USAP1, this maximum value is 8000000, i.e. 8 million, and is thus many times greater.

    The number of DB locks can thus in theory be raised to this maximum. However, because this causes a linear increase in the shared memory allocated at the database start (approx. 4.5 MB per 100000 locks), the greatest practicable value depends on the main memory available.

    The first time an overflow occurs, try doubling the current value of 'LOCKS'. After successfully executing the critical transaction, you should reduce the value for 'LOCKS' again, in order that the lock table does not take up the main memory in the DB server unnecessarily.
    2. Change the granularity of the DB locks:

    The Informix database recognizes three granularities of DB locks:
    - row lock (locks one table line),
    - page lock (locks one data block) and
    - table lock (locks a complete table).
      a) Locks at table level:
      Setting a 'table lock' is only possible explicitly in the transaction via the DML instruction 'LOCK TABLE...' and can therefore only be used in particular cases, e.g. when deleting a client.
      In this case the critical DB operation (generally DELETE) must be executed for the database individually using 'dbaccess', for example:

      $ dbaccess -
      > begin work;
      > lock table in exclusive mode;
      > delete from
      where ;
      > commit work;
      >

      After this, the transaction which was terminated can be started again.
          b) Locks at data level:
          Setting a 'row lock' or a 'page lock' takes place implicitly in the relevant DB operation and therefore does not require any operation in the progam logic. The choice between 'row lock' and 'page lock' is made via the current table definition and can be changed using a DDL instruction.

          In the standard delivery of R/3 on Informix, all tables are set to 'row lock'. If a data record is smaller than an Informix data block and therefore several data records fit in one block (this is usually the case), you can reduce the number of locks needed by changing to 'page lock' for the relevant table.

          The change is made as followed:

          $ dbaccess -
          > alter table
      lock mode (page);
      >

      After successfully executing the transaction which terminated previously, it is important that you cancel the change, because the higher granularity of locks is always accompanied by a deterioration in the serialization of transactions.

      The change can be canceled as follows:

      $ dbaccess -
      > alter table
      lock mode (row);
      >
      3. Splitting the critical DB transaction:

      Because batch input sessions, for example, run within one DB transaction, the DB locks are not released (COMMITed) until its completion.
      Therefore, if you use COMMITs more often in the batch input session, the locks will be released more quickly.
      By -

      No comments:

      Post a Comment

      Subscribe to: Post Comments (Atom)