Transaction layer on top of Oracle

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Fri, 02 Jul 1999 06:47:28 -0400
Message-ID: <377C98C0.26D9ED57@bigfoot.com>

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.. Do Oracle apps use it? Do other people have experience building
a layer like this?

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 Received on Fri Jul 02 1999 - 05:47:28 CDT