title: mysql - making a mechanism similar to Oracle's seqences
body: <p>MySQL provides an automatic mechanism to increment record IDs. This is OK for many purposes, but I need to be able to use sequences as offered by ORACLE. Obviously, there is no point in creating a table for that purpose.</p>
  
  <p>The solution SHOULD be simple:</p>
  
  <p>1) Create a table to hosts all the needed sequences,</p>
  
  <p>2) Create a function that increases the value of a specific sequence and returns the new value,</p>
  
  <p>3) Create a function that returns the current value of a sequence.</p>
  
  <p>In theory, it looks simple... BUT...</p>
  
  <p>When increasing the value of a sequence (much the same as <code>nextval</code> in Oracle), you need to prevent other sessions to perform this operation (or even fetch the current value) till the updated is completed.</p>
  
  <p>Two theoretical options:</p>
  
  <p>a - Use an UPDATE statement that would return the new value in a single shot, or</p>
  
  <p>b - Lock the table between the UPDATE and SELECT.</p>
  
  <p>Unfortunately, it would appear that MySQL does not allow to lock tables within functions / procedures, and while trying to make the whole thing in a single statement (like UPDATE... RETURNING...) you must use @-type variables which survive the completion of the function/procedure.</p>
  
  <p>Does anyone have an idea/working solution for this?</p>
  
  <p>Thanks.</p>