Re: Effective deadlock handling

Saeed wrote:

[]
>
> I tried imposing some form of ordering with this as follows:
>
> CURSOR get_lock ( cp_per_id NUMBER,
> cp_old_date DATE,
> cp_new_date DATE ) IS
>
> IF p_old_date < p_old_date THEN
> OPEN get_lock ( p_per_id, p_old_date, p_new_date );
> CLOSE get_lock;
> ELSE
> OPEN get_lock ( p_per_id, p_new_date, p_old_date );
> CLOSE get_lock;
> END IF;
>
> But this had no effect. I even tried using indexes with hints to
> influence the order in which Oracle finds and subsequently locks the
> rows, but this too did not work. Using the previous logic (2 separate
> OPEN's) appears to be working fine now, though there have been a few
> false dawns before.
>
> The production system on which the error was first noticed is Oracle
> 8.1.7.4 on Solaris 8 (E10000). The error has been reproduced on the
> development Oracle 8.1.7.4 instances on both Windows NT and Solaris 8.
> In addition I have conducted tests on a 9i instance on Linux, and have
> again been able to reproduce the error.
>
> If anyone would like me to send them some standalone scripts that
> reproduce the problem, feel free to email me (remove the "goaway" from
> the email address), and I will oblige.
>
> Kind regards,
>
> Saeed
>
> sr_ng 786

I'd be surprised if the "order" of dates in the SELECT...FOR UPDATE; made any difference.

  I do have a couple comments based on my experience, both with ORACLE systems and with Real-Time systems. By realtime I mean any system where a late answer is the wrong answer. Many business "realtime" systems are close, but not really realtime. 8^)

One solution to avoid the locking is, as you thought, to use a separate pending updates table, what was called on one project, the WIP tables (work in process). Rather than even trying to do the update, your application only does inserts to the WIP tables, thus never a lock. Background, batch processes perform the real updates, serialized by a timestamp. To get "realtime" views, the application must join both the WIP and base tables. (More complex but not necessarily much slower than straight selects on the base tables.)

The other thing that might help, depending on your application design (including possibly operating systems and other factors), is how this is solved in realtime systems. The solution is to assign a strict ordering of process priority. So for example, the process updating cancellations might be higher priority than the one that does the modifications due to schedule slips. (I'm guessing what these old and new dates you mentioned really are doing.) and the batch process you mentioned in your first post might be highest or lowest (Depending on how "realtime" batch data can be for you.) There can be a lot of time trying to figure out what are the correct priorities to assign. Testing helps, but shouldn't be the only approach.

If you need some more help, post or drop me a line. Looks to me like WIP tables are in your future.

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700   --- 7295 Popham Place, Solon, OH 44139
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com