Re: Record Locking ???

froliol_at_yahoo.com (Louis Frolio) wrote in message news:<94c28610.0305081211.75b0bf74_at_posting.google.com>...
> You should not let this developer do any record locking. The Oracle
> RDBMS will handle all record locking much more efficiently than any
> user could. What is the motivation for the user wanting to lock a
> record? Until he/she issues a commit or rollback the data(prior to
> the new data issued by this user) in that record will still be able to
> be viewed by another user. Dirty reads are not possible in Oracle.
>
> Louis.
>
>
> Walt <walt_at_boatnerd.com.invalid> wrote in message news:<3EB97CEF.EB7E177A_at_boatnerd.com.invalid>...
> > TurkBear wrote:
> > > Michael Hill <hillmw_at_ram.lmtas.lmco.com> wrote:
> > >
> > > >I have a Coldfusion web application that makes calls to Oracle tables,
> > > >inserts, updates, et. al and one of my users wants to add "Record
> > > >Locking". I am thinking this is not practical and is only valid in a
> > > >client-server environment.
> > > >
> > > >Am I wrong?
> > > >
> > > >Comments?
> > > Ask your user why he wants to manually do what Oracle will handle automatically?
> > >
> > > Just curious...
> >
> > While Oracle handles row locking automagically within a transaction, the
> > issue as I see it is how to preserve a transaction across multiple web
> > requests. i.e. the interaction is something like
> >
> > 1. begin a transaction
> > 2. get the data with a select for update
> > 3. update the data
> > 4. commit or rollback the transaction
> >
> > The issue is item 2 and item 3 are handled in separate web requests -
> > when the user comes back to update the data, how does he pick up the
> > transaction that he opened in item 1?
> >
> > There's no reason that I see that would make this technically
> > impossible, but I have no idea how to do it in Cold Fusion.

While working on a web-based application, there is no straight forward means to lock records. The reason for this is the statelessness of web based applications. The web browser sends a request and the web server responds to the request. The transaction is restricted to this duration and all locks holds only within this transaction.

The only way in which you can achieve locking in such a system is by what is called as optimistic locking, where you maintain a time stamp column (date and time of last update) in every table. When a page is requested, the date and time of last update of the record is also sent. Now, when the user updates the record, check if the time stamp of the record has changed. If yes you can throw an error.

I guess this should work in you case as well.

Cheers
Viki Received on Fri May 09 2003 - 06:21:52 CEST