Re: Effective deadlock handling

On Fri, 2 Jan 2004, sr_ng_at_goawaynms-sys-lts.demon.co.uk wrote:
> Hello,
>
> A somewhat perplexing problem that I would like to share, and
> perhaps in doing so someone wiser will spot what I'm doing
> wrong.
>
> he following description is a made up application to help
> explain the problem, so if it sounds a bit naff, sorry.
>
> An application has a JOB_STATS table as follows:
>
> Column Type Null?
> ------------------- ----------- --------
> ID NUMBER(10) NOT NULL
> PER_ID NUMBER(10) NOT NULL
> JOB_DATE DATE NOT NULL
> NUM_BOOKED NUMBER(4) NOT NULL
> NUM_CANCELLED NUMBER(4) NOT NULL
> NUM_COMPLETED NUMBER(4) NOT NULL
>
> This table is for upto the minute stats that managers can view
> to see how jobs are stacking up for future days, and how the
> current load and backlog is progressing. Actual core data that
> these stats are based upon are maintained elsewhere. Whenever a
> change in the core data occurs that pertains to these stats,
> this table is updated with appropriate triggers. During batch
> loads in the morning and late evening, this can result in
> hundreds of updates per second from the batch concurrent jobs.
>
> Now, there are occasions when in a single transaction 2 rows
> may be updated, e.g. if PER_ID 10 has cancelled an appointment
> on 10/12/2003 and moved it to 11/12/2003, but the auto
> scheduler has also decided to swap jobs for this person, by
> cancelling the 11/12/2003 and creating one on 10/12/2003. So
> the procedure that would handle this would be called by session
> 1 as:
>
> pa_stats.swap_appt ( p_per_id => 10,
> p_old_date => '10/12/2003',
> p_new_date => '11/12/2003' );
>
> and session 2 would have:
>
> pa_stats.swap_appt ( p_per_id => 10,
> p_old_date => '11/12/2003',
> p_new_date => '10/12/2003' );
>
> The procedure should carry out the following UPDATES:
>
> 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;
>
> Now, if the these 2 sessions invoke the procedures as above at
> the same time, we get a deadlock.
>
> To solve this, I have tried a number of methods:
>
> 1. SELECT FOR UPDATE
>
> CURSOR get_lock ( cp_per_id NUMBER,
> cp_old_date DATE,
> cp_new_date DATE ) IS
> SELECT id,
> num_cancelled,
> num_booked
> FROM job_stats
> WHERE per_id = cp_per_id
> AND ( job_date = cp_old_date OR job_date = cp_new_date )
> FOR UPDATE OF num_cancelled, num_booked;
>
> This helps but does not eliminate the deadlocks, especially
> during batch updates.
>
> 2. SELECT FOR UPDATE NOWAIT
>
> As above but with a NOWAIT. With this we obviously avoid the
> deadlock, but the problem then is how to deal the "failed"
> transaction.

My guess is that you have a business rule being violated. One should win and the other lose. Now, what to do with the guy that lost? Hm... I don't know, but it seems the appropriate thing to do is exactly what the UPDATE NOWAIT does. Let the first one in win.

> Retries seem to have limited success.

What is happening here?

> The only reliable option appears to be to write the data to an
> intermediate table and then another job would complete the
> UPDATEs from the intermediate data. This is messy, and renders
> the stats near-time rather than real-time.
>
> 3. Exert Some Order in the Locking
>
> We replace the cursor above with:
>
> CURSOR get_lock ( cp_per_id NUMBER,
> cp_date DATE ) IS
> SELECT id,
> num_cancelled,
> num_booked
> FROM job_stats
> WHERE per_id = cp_per_id
> AND job_date = cp_date
> FOR UPDATE OF num_cancelled, num_booked;
>
> Then in the code we try to ensure that the order of the locks
> is consistent:
>
> IF p_old_date < p_new_date THEN
> OPEN get_lock ( p_per_id, p_old_date );
> CLOSE get_lock;
> OPEN get_lock ( p_per_id, p_new_date );
> CLOSE get_lock;
> ELSE
> OPEN get_lock ( p_per_id, p_new_date );
> CLOSE get_lock;
> OPEN get_lock ( p_per_id, p_old_date );
> CLOSE get_lock;
> END IF;
>
> Now this should, to my mind, prevent deadlocks since we know
> that the locks will be obtained in a consistent ordered
> manner. To my surprise, this still did not prevent the
> deadlocks. I'm surprised because I cannot see how a deadlock
> can occur with this code, even if we have dozens of sessions
> with varying values (maybe three-way or 4-way contention) I
> cant see how with this scheme the deadlock can arise.
>
> Can anyone explain this to me?

I don't know how your last option would cause a deadlock as well, except, maybe you are somehow not handling cursors correctly. Have you tried the logic from #3 but just execute the original update statements based on the switch logic?

-- 
Galen Boyer