Re: Row level locking: how to do it?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/08/05
Message-ID: <32054979.13761127_at_dcsun4>#1/1


On Sat, 3 Aug 1996 06:46:01 GMT, leng_at_cougar.vut.edu.au (Leng Kaing) wrote:

>Lim Tik Meng (mte_at_moe.ac.sg) wrote:
>: We are trying to solve a problem involving concurrency processing in
>: embedded SQL in forms 4.5 trigger. We will greatly appreciate if anyone
>: could guide us on how to do row level blocking. The Lock Table command
>: doesnot work for row level locking.
 

>: regards,
 

>: Peter
>
>By default oracle does row level locking. So you've accomplished it
>without even knowing it! There's no "lock row" command. Whenever you
>do an update or delete, you are locking a row.
>
>Leng.
>
>--
>Leng Kaing
>DBA - Directory Technology P/L
>Email: lkaing_at_ventnds1.telecom.com.au
>Ph: +61-3-9892-9081 Fax: +61-3-9892-9344
>WWW: http://www.vut.edu.au/voug

In forms, all concurrency control is pretty much handled for you.

When you read from the database, you are gauranteed a consistent read (correct information is always displayed).

When you (the end user) attempts to modify any of the data on the screen, before you do so forms will automagically issue:

  select .... from Table where rowid = :block.rowid and c1 = :block.c1 ......

  for update nowait
  ^^^^^^^^^^^^^^^^^

That is - forms will ensure the data has not changed (lost update prevention)
        - forms will lock that row down to ensure no one else can change it

Forms will do the above select 3 times if it fails (nowait returns immediately) to attempt to lock the row. If it is locked, forms will tell you "Someone else has the row". If it has been modified, forms will tell "Row has changed, please requery to see new row".

In short, you can lock a row useing select for update on that row but you DON'T need to, forms is already doing all of that for you.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database


statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Aug 05 1996 - 00:00:00 CEST

Original text of this message