title: mysql stored procedure, select max value and insert the value and assign to variable
body: <p>I am porting a MSSQL stored procedure to MYSQL and i have a stored procedure that does this.</p>
  <ol>
  <li><p>get the last value in a table by select Max.</p>
  </li>
  <li><p>add the new value to the table (along with other record</p>
  </li>
  <li><p>get the last value and store it in a variable for other processing</p>
  </li>
  </ol>
  <p>So far what i have the following</p>
  <pre><code>     DECLARE lastSeq INT Default 0;
  
       SELECT max(seq) INTO lastSeq from mytable;
  
       Set newSeq = lastSeq + 1;    
       insert into mytable (seq, value1, value2, value3) values (newSeq, 1, 2, 3);
  </code></pre>
  <p>Unfortunately this is NOT thread safe, say if I select max(seq) and then a new record was added by other thread running a different query, by the time i reach insert, the value is already wrong.
  In MSSQL I did this by locking the table during query of max(seq).</p>
  <p>BUT</p>
  <p>MYSQL does not allow locking of tables in stored procedure, so I cannot directly port the approach.
  Havent had the luck to find solution while searching, maybe i am not putting the right keywords in search.</p>
  <p>How can I do this in MYSQL thread safe inside stored procedure?</p>
  <p>Update: I cannot use auto_increment for this column as this column is not unique and we allow duplicate, maybe my sample is wrong since i used &quot;sequence&quot; which should be auto increment, but in my real code, i use it for a different column that allows duplication.</p>
  <p>example;</p>
  <pre><code>     record id userid     name  seq   status
       1      1      adam   1     A
       2      1      adam   2     C
       3      2      Bob    1     C
  
  </code></pre>
  <p>In the above record, we have 2 records for Adam but only one valid set to &quot;C&quot; as current and &quot;A&quot; as archived or old value.
  so my table has 2 valid record,</p>