Re: Which transaction isolation level can prevent lost updates?

From: RK <rajXesh_at_hotmail.com>
Date: 26 Nov 2002 10:39:44 -0800
Message-ID: <548b9514.0211261039.22e9862c@posting.google.com>

huetter_at_grz.at (Arno Huetter) wrote in message news:<a710c60d.0211251145.3f1edc69_at_posting.google.com>...
> Hello there,
>
> I am working on a multi-tier project, using Oracle 8i. My data layer
> consists of container-managed entity java beans, which means that I
> don't have direct control over which SQL statements are being
> generated, hence I cannot force X-locks via dummy-updates, nor by
> invoking SELECT FOR UPDATE. What I can do is to set the isolation
> level for the transactions my entity beans will execute within. Which
> transaction isolation level is necessary to prevent lost updates:
>
> trans 1: trans 2:
> begin;
> begin;
> select i;
> select i;
> i = i + 1;
> i = i + 1;
> update i;
> update i;
> commit;
> commit;
>
> I know isolation level SERIALIZABLE would circumvent lost updates for
> sure. But I once read that REPEATABLE READ avoids lost updates as
> well, yet I am not sure whether that's true...
>
> Thank you!
>
> Kind regards,
> Arno Huetter

You should add a 'last_modified_date' col to your table and use it in the update
so your scenario would be (lmd = last_modified_date)

 trans 1:                       trans 2:
 begin;
                                begin;
 select i,lmd;
                                select i, lmd;
 i = i + 1;
                                i = i + 1;

 update i,
 lmd=sysdate
 where pk,
 lmd = <selected lmd>;

 if sql%rowcount > 0 then

    commit;
 else

    message 'Someone has updated the record'  end if

                                update i, 
                                lmd=sysdate
                                where pk, 
                                lmd = <selected lmd>;
                               
                                if sql%rowcount > 0 then
                                   commit;
                                else
                                   message 'Someone has updated the
record'
                                end if
 

Note that trans 1 will commit whereas trans 2 will fail and display the message. You dont need to mess around in trying to set isolation levels

• rajXesh

Received on Tue Nov 26 2002 - 12:39:44 CST