> > 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.
At least I made the answer easier to find =)
Received on Wed Jan 07 2004 - 13:43:21 CST
