Re: Which transaction isolation level can prevent lost updates?

Rajxesh,

yes, I think that's the way to go once the container itself does not provide pessimistic locking. As I do not invoke SQLs directly, I solved it on the application level, simply by adding a timestamp attribute to my entity beans, set it when reading, and compare it to it's original value when writing back.

Thanks for the hint!

Kind regards,
Arno Huetter

rajXesh_at_hotmail.com (RK) wrote in message news:<548b9514.0211261039.22e9862c_at_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 Wed Nov 27 2002 - 00:20:00 CST