From: mcstockX@Xenquery   
      
   "Andrew Baker"  wrote in message   
   news:c19b84e5.0407271054.54ae108a@posting.google.com...   
   | What is the best way to lock an individual row in the following   
   | scenerio:   
   |   
   |     --TODO - LOCK THIS ROW   
   |     -- Return the next id   
   |     SELECT next_id   
   |     INTO next_id_out   
   |     FROM owner.my_id_table   
   |     WHERE app_id = app_id_in;   
   |   
   |     -- Update the next id on the table   
   |     UPDATE owner.my_id_table   
   |     SET next_id = next_id_out + 1   
   |     WHERE app_id = app_id_in;   
   |   
   | I need to make sure that nothing changes the id table between me   
   | selecting the id and updating the table with the next available id.   
   |   
   | thanks   
   | andrew   
   |   
   | PS. yes I am new to oracle :)   
      
      
   First, make sure that you really want to do this -- if you're simply   
   assigning surrogate key (ID) values, then you probably want to use a   
   SEQUENCE object -- they avoid the serialization that this approach causes,   
   but have the sometimes unwanted characteristic of allowing gaps between IDs   
   (only a problem if you've got auditing requirements that disallow gaps in a   
   series of IDs)   
      
   If you do need to have a table-based counter, here's the most reliable way   
   to increment it:   
      
       update owner.my_id_table   
       set next_id = next_id +1   
       where app_id = app_id_in   
       returning next_id into some_plsql_variable;   
      
   however, if the row is locked by another process (which is likely executing   
   the same statement and has delayed committing or rolling back) then this   
   process will hang until the other process's transaction completes. if you   
   want to return control to this process rather than wait on a lock, you need   
   to do one of the following before you update statement:   
      
       select next_id   
       into next_id_out   
       from owner.my_id_table   
       where app_id = app_id_in   
       for update nowait;   
      
       select next_id   
       into next_id_out   
       from owner.my_id_table   
       where app_id = app_id_in   
       for update wait 5;   
      
   the first raises an oracle error immediately if it cannot lock the row, the   
   second waits up to 5 seconds to obtain the lock. the second syntax takes   
   whatever number of seconds you want, but unfortunately the number of seconds   
   has to be specified in a literal (not with a bind variable)   
      
   ++ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)