Re: Locking issues

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/14
Message-ID: <3304b9d3.20626929@nntp.mediasoft.net>#1/1

the way Oracle Forms handles this is as follows:

query up the information, include the rowid for each table in the query.
when a user attempts to modify a value (before they actually modify it), the applications issues:

   select *
     from t
    where rowid = (theRowidYouSelected)

      and c1 = (theFirstColumnYouSelected)
      and ...
      and cn = (theFirstColumnYouSelected)

for update nowait;

3) if the above results in an error (-54), the resource is already locked, forms retries the above query 3 times before returning the error message that the row you want is already locked.

if the above results in no rows returned, you know that someone else has either deleted the row or modified its value. the user needs to requery the row.

if the above results in one row being returned, you can let the user update the data safe in the knowledge that they will be able to update it when the time comes. others can read it but they cannot select ... for update it.

On 14 Feb 1997 16:43:10 GMT, "David Kaiser" <davidk_at_sfmic.com> wrote:

>Oracle's default locking method causes C/S applications to wait for a lock
>to clear. This can make the user think that their program crashed, when
>they are actually waiting for a lock.
>
>The only way around this that I've seen is to have the client program
>timeout after waiting for a certain period of time.
>
>Is there anyway with Oracle 7.3 to have Oracle return an error message
>notifying the client program that the record it is trying to update is
>locked?
>--
>
>+---------------------------------------+-------------------------------+
>| David Kaiser | e-mail: davidk_at_sfmic.com |
>| Project Manager | WWW: http://www.sfmic.com |
>| State Fund Mutual Insurance Company | |
>| 7500 Flying Cloud Drive, Suite 900 | voice: 612.914.1317 |
>| Eden Prairie, MN 55344 USA | fax: 612.944.1169 |
>+---------------------------------------+-------------------------------+
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com

http://govt.us.oracle.com

Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Feb 14 1997 - 00:00:00 CST