Erik wrote:
> 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
I think the problem has always been easily solvable using defensive
programming and examples similar to yours using FOR UPDATE have been
suggested.
What appears to be keeping this thread alive, prior to your contribution
was a combination of testosterone and technology. Always an unsightly
mixture.
--
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 Tue Jan 06 2004 - 13:17:24 CST
