title: Select .. for Update in cluster environment
body: <p>I am working on some legacy product and they are using a table to generate unique keys (primary key) for each table.  This table contains latest ID for all other tables.  When they want to insert a row in any other table, following is the logic they use to generate unique ID for that new row</p>
  
  <p>Table to generate keys look likes</p>
  
  <pre><code> ID | NEXT_ID | TABLE_NAME
  
   public synchronized long generateKey(Connection con){
      // select the latest ID value from the table against a row
      // increment the value by 1
      // update the table with this latest value
      // return the latest value
   }
  </code></pre>
  
  <p>In single node threaded environment everything goes fine.  But there are changes to get race condition while executing the above logic in clustered environment.  So in order to over come this issue we thought of having a java function that calls a PL/SQL function that do the above job.  Code is as follows</p>
  
  <pre><code> public long generateKey(Connection con) {
      // call PL/SQL function and return the value
   }
  </code></pre>
  
  <p>Following is the skeleton of PL/SQL function</p>
  
  <pre><code> FUNCTION GET_NEXT_ID(tablename IN VARCHAR2)
   RETURN NUMBER IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      nextID NUMBER;
      BEGIN
         SELECT NEXT_ID INTO nextID FROM &lt;Key_generator_table&gt; WHERE TABLE_NAME=tablename FOR UPDATE;
         UPDATE &lt;Key_generator_table&gt; SET NEXT_ID=NEXT_ID+1 WHERE TABLE_NAME=tablename;
     commit;
     RETURN (nextID);
   END;
  </code></pre>
  
  <p>What I understand wrt SELECT FOR UPDATE is, it locks the row when we retrieve so that no other transaction can see while it is trying to update the record.  So it hold good in non clustered environment.  My questions is would this same hold good in clustered environment?  Would there be any race conditions with this approach?</p>
  
  <p>Unfortunately we could not change the unique ID generation approach due to product constraints.</p>