Re: Effective deadlock handling

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 02 Jan 2004 16:26:45 -0800
Message-ID: <1073089521.778195@yasure>

Saeed 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. Retries seem to have limited success.
> 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?
>
> Kind regards,
>
> Saeed
>
> sr_ng 786

I'm surprised you are getting deadlocks at all as they are extremely rare in Oracle and it strikes me that you are trying to solve a problem that doesn't exist because of a lack of understanding how Oracle works.

Please provide the following:

1. Hardware and operating system
2. Version and edition of Oracle
3. By what means you are establishing and concluding that htere is a 

deadlock.

My guess, code unseen, is that you think Oracle works like other databases and are confusing a read with a deadlock.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Jan 02 2004 - 18:26:45 CST