Re: Transaction layer on top of Oracle

A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com> (if that email address didn't require changing) On Fri, 02 Jul 1999 06:47:28 -0400, you wrote:

>Some folks are working on a custom app at my site and wish to prevent
>the following from happening.
>
>Time Session A:
>Session B:
>
>update x set r=1 where t=4;
> update x set r=2 where t=4; (session A
>blocked)
>
>commit;
> commit;
>r=2, session b's update lost.
>I suggested doing a
> Session A:
>Session B:
>
>select r from x where t=4 for update nowait;
> select r from x where t=4 nowait;
> catches exception and reports.
>
>update x set r=1 where t=4;
>
>commit;
> User takes action -
> (finds out what's going on)
>
>This seemed like robustness the likes of which a bank might want. I'm
>wondering
>how common it is..

extremely common.

>Do Oracle apps use it?

Yes, if you use a tool like Oracle Forms -- it does it automatically for you under the covers. It queries the data using a "select", it shows it to you, as soon as you attempt to type over a piece of data in the screen, forms executes:

   select * from T where c1 = c1_you_fetched and c2 = c2_you_fetched ....

                        and rowid = rowid_you_fetched FOR UPDATE NOWAIT;

If that returns 0 records -- someone else updated your record and you get a message "record has changed, please requery".

if that returns an error -- someone else currently has the record locked, you must wait.

if that returns 1 row -- you have locked the record and the values in the database correspond to the values on your screen.

>Do other people have
>experience building
>a layer like this?
>

see above, its pretty easy. many tools do it for you.

>If so, how about if we have to guarantee several row locks in the same
>transaction.
>How does this sound -
>savepoint xyz
>select x for udate nowait;
>catch exception - if not ok, rollback to savepoint
>select y for update nowait;
>catch exception - if not ok, rollbakck to savepoint
>select t for update nowait;
>catch exception - if not ok, rollback to savepoint
>
>if ok - contine

That should work just fine

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 02 1999 - 07:23:30 CDT