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
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21212321