Re: Effective deadlock handling

From: Erik <no_at_spam.com>
Date: Tue, 6 Jan 2004 19:34:07 +0100
Message-ID: <btev58$kl9$1@news.lth.se>

This was a long thread, and I didn't feel like reading it all to see if someone
has solved the problem. The problem is in the code:

> UPDATE job_stats
> SET NUM_CANCELLED = NUM_CANCELLED - 1
> WHERE per_id = p_per_id
> AND job_date = p_old_date;
>
> UPDATE job_stats
> SET NUM_BOOKED = NUM_BOOKED + 1
> WHERE per_id = p_per_id
> AND job_date = p_new_date;

Say job_stats contains three rows:
(1) per_id = 10, job_date = '2004-01-01' and
(2) per_id = 10, job_date = '2004-01-02'

Now, thread #1 calls the procedure with
p_per_id = 10, p_old_date = '2004-01-01' and p_new_date = '2004-01-02'

and slightly afterwards (before the second UPDATE is started), thread #2 calls it with
p_per_id = 10, p_old_date = '2004-01-02' and p_new_date = '2004-01-01'.

Now, what will happen is:

1. Thread #1 locks row (1) with the first UPDATE statement
2. Thread #2 locks row (2) with the first UPDATE statement
3. Thread #1 tries to lock row (2), with the second UPDATE, but this row is
        locked by thread #2, so thread #1 is waiting for the lock to be

released.
4. Thread #2 tries to lock row (1) with the second UPDATE, but this row is

        locked by thread #1, so Oracle detects a deadlock.

So what to do about it? The rows must be locked at the same time. I can think
of two obvious ways to do this:

1. Lock both rows at the same time by doing something like this before the first UPDATE: CURSOR c IS SELECT job_date FROM job_stats WHERE per_id = p_per_id AND job_date IN(p_new_date, p_old_date) FOR UPDATE OF num_cancelled, num_booked; OPEN c;
2. Do both updates within the same statement, like UPDATE job_stats SET num_booked = CASE WHEN job_date = p_new_date THEN num_booked + 1 ELSE num_booked END, num_cancelled = CASE WHEN job_date = p_old_date THEN num_cancelled + 1 ELSE num_cancelled END WHERE per_id = 10 AND job_date IN(p_old_date, p_new_date)

/ Erik Received on Tue Jan 06 2004 - 12:34:07 CST