"
...
You would have to ensure that all interested applications used your technique to
serialize access to this row.
...
"

I don't like this, it's a bit too dangerous.
(You never can ensure it, can you ?)

Better approach I would suggest:

1. create view <view_to_update> as select * from <table_to_update>

2. create trigger tr_u_v instead of update on <view_to_update> for each row .....
(continued under 4.)

3. forbid direct updates of <table_to_update> for example check in before update trigger via dbms_utility.format_call_stack that update is called via tr_u_v

4. (2. continued)
call dbms_lock.allocate like suggested, update <table_to_update>

what do you think ?
is it an improvement ?

Thanks Tom. I was planning on all interested applications using nowait. That would make it highly unlikely that the edit session would lose its lock. Named locks would eliminate the risk entirely.

However, neither approach protects me from "non-interested" applications, like SQL*Plus. If another application is willing to wait for the lock, then my user will lose it when they click save. I will have no choice but to bounce them out of the edit screen. To guarantee the edit session is not interrupted, I would need the non-existent "commit keep lock". Or, I can wait and commit when they exit the edit screen (no save button).

Maybe the save button is not necessary. However, it is a popular paradigm, likely to be intuitive (and desirable) to the typical user. "Office" applications work that way. If you open a spreadsheet "read/write", then it is all yours until you close it. You can protect your progress at any time by clicking save.

My argument is similar to yours about Google's method of returning search results. Why mess with a paradigm that works well and that people are already used to? Oracle's concurrency model (elegant as it is) doesn't seem to allow me to (perfectly) implement the popular save button paradigm.

simply use two transactions (T1 and T2) and a secondary table (with the same structure as the first one).

you take the lock with T1.

you save the user work in the secondary table and do a commit with T2.

when the editing is finished, you simply delete the secondary table and update the primary table and commit everything in T1.

if something goes wrong the user will come back for editing the same row.
you just have to implement the logic in you application to check if a record is present in the secondary table for the same PK. if there is one, you can ask the user to restore this one ...

Laurent, thanks.  That is worthy of consideration.  That is more or less how a spreadsheet does it.  So far I have been working on Tom's first suggestion.

Tom, what about using a trigger to short circuit updates and deletes that don't hold the named lock?  I can't stop select for update, but I can make is so that only compliant applications can update.  See below.  

- Jack


SQL> ----------------
SQL> -- Create table.
SQL> ----------------
SQL> 
SQL> drop table test_tb;

Table dropped.

SQL> create table test_tb
  2  as
  3  select 100 id, 'SAM' name from dual
  4  UNION ALL
  5  select 200 id, 'BOB' name from dual
  6  ;

Table created.

SQL> select * from test_tb order by 1;

  ID NAME
---- ----
 100 SAM
 200 BOB

SQL> ---------------------------------------
SQL> -- Create trigger to require user lock.
SQL> ---------------------------------------
SQL> 
SQL> create or replace trigger test_tr
  2    before update or delete on test_tb
  3    for each row
  4  declare
  5    my_count int;
  6  begin
  7  
  8    select count(*)
  9    into my_count
 10    from dba_locks
 11    where session_id = sys_context('userenv','sid')
 12      and lock_id1 = :old.id
 13    ;
 14  
 15    if my_count = 0 then
 16      raise_application_error(-20000,'PL/SQL User Lock required.');
 17    end if;
 18  
 19  end;
 20  /

Trigger created.

SQL> 
SQL> ------------------
SQL> -- Attempt update.
SQL> ------------------
SQL> 
SQL> update test_tb set name = 'JOE' where id = 100;
update test_tb set name = 'JOE' where id = 100
       *
ERROR at line 1:
ORA-20000: PL/SQL User Lock required.
ORA-06512: at "JMULHO.TEST_TR", line 13
ORA-04088: error during execution of trigger 'JMULHO.TEST_TR'


SQL> -----------------
SQL> -- Get user lock.
SQL> -----------------
SQL> variable result number
SQL> set autoprint on
SQL> 
SQL> begin
  2     :result := dbms_lock.request(
  3                                 id => 100,
  4                                 lockmode => dbms_lock.x_mode,
  5                                 timeout => 0,
  6                                 release_on_commit => false
  7                                 );
  8  end;
  9  /

PL/SQL procedure successfully completed.


RESULT
------
     0

SQL> select session_id, lock_id1, lock_type
  2  from dba_locks
  3  where lock_type = 'PL/SQL User Lock'
  4  ;

SESSION_ID LOCK_ID1   LOCK_TYPE
---------- ---------- --------------------------
       422 100        PL/SQL User Lock

SQL> ------------------------
SQL> -- Attempt update again.
SQL> ------------------------
SQL> 
SQL> update test_tb set name = 'JOE' where id = 100;

1 row updated.

SQL> ---------------------------------------------------
SQL> -- Show that user lock is still there after commit.
SQL> ---------------------------------------------------
SQL> 
SQL> commit;

Commit complete.

SQL> select session_id, lock_id1, lock_type
  2  from dba_locks
  3  where lock_type = 'PL/SQL User Lock'
  4  ;

SESSION_ID LOCK_ID1   LOCK_TYPE
---------- ---------- --------------------------
       422 100        PL/SQL User Lock

SQL> ---------------------
SQL> -- Release user lock.
SQL> ---------------------
SQL> 
SQL> begin
  2    :result := dbms_lock.release(100);
  3  end;
  4  /

PL/SQL procedure successfully completed.


RESULT
------
     0

SQL> select session_id, lock_id1, lock_type
  2  from dba_locks
  3  where lock_type = 'PL/SQL User Lock'
  4  ;

no rows selected