![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Deadlocks
A deadlock occurs when two sessions are holding onto the lock that each other needs. The result is that they will wait indefinitely for each other, but this situation is detected and prevented from happening by the database server. For example:
T1: Read row A and place a lock on it.
T2: Read row B and place
a lock on it.
T1: Request a lock on row B.
T2: Request a
lock on row A.
IDS maintains an internal lock table. To prevent a deadlock, it checks the internal lock table to see whether a deadlock can occur before a session can wait for a lock. This is what happens:
T1: Set lock mode to wait.
T2: Set lock mode to wait.
T1:
Read row A and place a lock on it.
T2: Read row B and place a
lock on it.
T1: Request a lock on row B; has to wait for T2 to
release the lock on row B.
T2: Request a lock on row A, but is
rejected because deadlock can occur if it has to wait for the
lock.
From the internal lock table, IDS detects that:
- T1 is holding to the lock on row A
- T2 is holding to the lock on row B
- T1 is waiting for the lock on row B that T2 is holding
A deadlock will occur if T2 is allowed to wait for a lock on row A that T1 is holding. Thus, the lock request is rejected with an error (-143 ISAM error: deadlock detected.)
Examine the onstat -p output for deadlocks:
Listing 10.
onstat -p output for deadlocks$ onstat -p IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 2 days 22:34:37 -- 157696 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 13851 44301 1894394 99.27 22604 41159 429311 94.73 isamtot open start read write rewrite delete commit rollbk 1178884 64495 88313 413795 46723 58450 5657 17833 16 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 89.23 13.32 366 70849 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 191 0 750183 5 0 6 4010 6770 ixda-RA idx-RA da-RA RA-pgsused lchwaits 231 2 3080 3313 254 |
Looking at the "deadlks" field, you can see that five deadlock events have occurred since the database server came online. If deadlocks are happening frequently, examine the application and try to stagger the use of the frequently changed rows. By reducing the time to wait for a lock, the chances of deadlock can also be reduced.
For a distributed transaction, a session uses the
DEADLOCK_TIMEOUT configuration parameter to
wait for a response from the remote database server before it returns
an error. The "dlouts" field in the
onstat -p output shows the statistics for
distributed deadlock timeouts.
Logging and non-logging database
In a logging database, the database server keeps a record of its transaction and locks held over the span of the transaction. Often, the locks held by the transaction can be identified unless the transaction is not committed or is rolled back. In a non-logging database, there are no transactions, but locks are still used. Though easily missed, locks are used by the server when executing the Data Manipulation Language and released very quickly after the statement is completed.
