Home | Previous Page | Next Page   Tuning Database Server Performance > Managing Resource Use for Specific Sessions >

Monitoring Deadlocks

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.

Reducing Deadlocks

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:

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]