IBM Support

How are deadlocks handled and avoided by IBM Informix Dynamic Server

Question & Answer


Question

How does IBM® Informix® Dynamic Server™ (IDS) handle and avoid deadlocks?

Answer


INTRODUCTION

A deadlock is a circular wait condition. When a deadlock occurs, a user is waiting on a resource that cannot be freed until he releases a resource he owns. He cannot release a resource while he is waiting.

Many times you can eliminate frequent lock contention by using row level locks as opposed to page level locks.

To determine what type of locking is in force for a table, e.g "customer", do these steps:

  • Use the dbschema command with the -ss option.
  • Query on the locklevel column in the systables table:


Example:

    SELECT
         tabname,
         DECODE(locklevel, "P" , "Page" , "R" , "Row")
    FROM systables
    WHERE tabname = "customer";

STEPS

If your system has frequent deadlock problems, check to make sure that the involved tables have row level locking. Page level locking increases the probability that users will contend for the same resource. Row level locking might decrease the number of deadlocks and the amount of waiting on locks on your system.

IDS handles deadlocks by not allowing them to occur in first place.

If a deadlock is detected, the IDS server produces an error message. You will find below examples of such error messages.


Example:
    -143: ISAM error: deadlock detected

You may also need to modify the LOCKS parameter to a higher value in your ONCONFIG file, if you change a table to use row level locking.

The server handles distributed deadlocks by preventing distributed transactions from locking indefinitely by enforcing a lock time-out. To prevent distributed systems from locking indefinitely, the database server has provided the ONCONFIG parameter "DEADLOCK_TIMEOUT" .

When a session waits on a response from a remote request for more than the time specified by DEADLOCK_TIMEOUT, the server issues this error message:


Example:
    -154: ISAM error: Lock Time-out Expired

It is normal that the two aforementioned ISAM errors are accompanied with SQL errors -243 and -244.

The default behaviour for the IDS server is to return an error message to the application.

If you prefer to wait indefinitely for a lock, you can execute this SQL statement:


    SET LOCK MODE TO WAIT

When this lock mode is set, your program usually ignores the existence of other concurrent programs. In most cases, the delays are imperceptible.

You can also set wait for a specific number of seconds by executing this SQL statement:


    SET LOCK MODE TO WAIT 20

This statement places an upper limit of 20 seconds on the length of any wait. If the lock is not removed in that time, the IDS server produces an appropriate error code.

Related Information

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"},{"code":"PF008","label":"DYNIX\/ptx"},{"code":"PF010","label":"HP-UX"},{"code":"PF015","label":"IRIX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF026","label":"Reliant UNIX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;7.3;9.4;11.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21212321