Re: Locking issues

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/21
Message-ID: <330cfb0c.42460655@nntp.mediasoft.net>#1/1

On Thu, 20 Feb 1997 11:41:15 -0600, stevec_at_zimmer.csufresno.edu (Steve Cosner) wrote:

>In article <330a6c1c.4042803_at_nntp.mediasoft.net>,
> tkyte_at_us.oracle.com wrote:
><snip>
>> If you are worried about someone walking away from a terminal, perhaps your
>> client server application should have a timeout in it. If no keyboard
>> activity/whatever, issue a message, give them one more minute or something,
>> and then rollback.
>
>Good suggestion, only how would one do this... start a timer in the
>on-lock trigger, or what? I would like to know of anyone has
>successfully implemented this function, and how it is done. It is a
>problem that worries me as I work on forms that will be used in a busy
>OLTP environment.
>

I think the easiest and 'safest' (least likely to miss an interval or accidently think an interval has expired) would be to

create a timer in the when-new-form-instance trigger (when you start the form). have it fire every N minutes. The body of the trigger will simply check a global date field and do something like:

if ( (( sysdate - :global.last_db_access ) * 24 * 60) > N ) then

      create another timer that goes off in 1 minute
      ask the user if they are still there
      kill the timer that goes off in 1 minute

end if

create a user trigger that gets fired by the above. This one will log the user off from the database and close the window that was asking the user if they were still there. perhaps it puts up a window that tells them this occurred.
in every on-* trigger (all the on-* triggers signify database access) code something like:

on-select:

begin

:global.last_db_access := sysdate;
SELECT_RECORD;
end;

Each one of the on-* triggers has a builtin that may be called to do the right thing. You would update the global and then call the builtin. That way the on-* triggers maintain a global for you that tells you how long its been since the last database access.

>> What has been suggested as a locking scheme is:
>>
>> 1.) I read out a list of records and display them (no locks)
>> 2) user attempts to update COPY of record 1, goto database and lock record 1
>> ensuring that the local copy matches the database copy, return error if record
>> is locked OR has been modified.
>> 2a) do the same for records 3 and 5
>> 3) program issues 3 updates
>> 4) database is consistent.
>
>Not only suggested, but also implemented in Forms. This works quite well,
>except for the operator inactivity problem.
>
>Steve Cosner
>
><snip>
>>
>> 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
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

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 21 1997 - 00:00:00 CST