A deadlock occurs when user processes hold locks that other users want to acquire.
For example, user joe holds a lock on row 10. User jane holds a lock on row 20. Suppose that jane wants to place a lock on row 10, and joe wants to place a lock on row 20. If both users execute SET LOCK MODE TO WAIT, they might wait for each other forever.
If user processes access tables or fragments on the local coserver, the database server uses the lock table to detect deadlocks automatically and stop them before they occur. Before a lock is granted, the database server examines the lock list for each user. If a user holds a lock on the resource that the requestor wants to lock, the database server traverses the lock wait list for the user to see if the user is waiting on any locks that the requestor holds. If so, the requestor receives an deadlock error.
Deadlock errors in OLTP applications can be unavoidable if applications update the same rows frequently. However, certain applications might always be in contention with each other. Examine applications that are producing a large number of deadlocks and try to run them at different times. To monitor the number of deadlocks, use the deadlks field in the output of xctl onstat -p.
Deadlocks often occur in OLTP systems in which the same table or table fragment is updated or read with locking by many users almost simultaneously. For this reason, you should resolve deadlocks automatically and immediately so that they do not slow or halt the system.
In DSS databases, queries are usually executed in Dirty Read isolation level, in which tables are not locked. If other isolation levels are in effect, deadlocks might also occur if many queries are reading the same table.
To reduce the number of deadlocks in OLTP databases, you might use the following methods:
Use the deadlock information in the message log to determine which tables and fragments are associated with most deadlocks and consider a different fragmentation strategy. You might make the fragmentation granularity smaller.
For information about the relation of the isolation level and locking, refer to the IBM Informix: Guide to SQL Tutorial.
Make sure that SPL routines used in transactions specify the appropriate isolation level.
Unless transactions change a significant number of rows at a time, set STANDARD tables used by OLTP applications to row-level locking.
Make as many tables as possible STATIC tables, which do not permit changes but also do not require locks of any kind. Tables that contain information that does not change often or changes only at regular intervals, such as product tables, department tables, and so on, can be made STATIC tables. When updates are required, you can change the table mode. Updates might be applied in a batch process.