Server task deadlocks

Below is an example of a deadlock between two processes.

T19	Event sequence	T20
begin transaction update savings set balance = balance - 250 where acct_number = 25 update checking set balance = balance + 250 where acct_number = 45 commit transaction	T19 and T20 start. T19 gets exclusive lock on savings while T20 gets exclusive lock on checking. T19 waits for T20 to release its lock while T20 waits for T19 to release its lock; deadlock occurs.	begin transaction update checking set balance = balance - 75 where acct_number = 45 update savings set balance = balance + 75 where acct_number = 25 commit transaction

T19

Event sequence

T20

begin transaction

update savings
set balance = balance - 250
where acct_number = 25



update checking
set balance = balance + 250
where acct_number = 45

commit transaction

T19 and T20 start. T19 gets exclusive lock on savings while T20 gets exclusive lock on checking. T19 waits for T20 to release its lock while T20 waits for T19 to release its lock; deadlock occurs.

begin transaction

update checking
set balance = balance - 75
where acct_number = 45

update savings
set balance = balance + 75
where acct_number = 25

commit transaction

If transactions T19 and T20 execute simultaneously, and both transactions acquire exclusive locks with their initial update statements, they deadlock, waiting for each other to release their locks, which will not happen.

Adaptive Server checks for deadlocks and chooses the user whose transaction has accumulated the least amount of CPU time as the victim.

Adaptive Server rolls back that user’s transaction, notifies the application program of this action with message number 1205, and allows the other process to move forward.

The example above shows two data modification statements that deadlock; deadlocks can also occur between a process holding and needing shared locks, and one holding and needing exclusive locks.

In a multiuser situation, each application program should check every transaction that modifies data for message 1205 if there is any chance of deadlocking. It indicates that the user transaction was selected as the victim of a deadlock and rolled back. The application program must restart that transaction.