title: Synchronized stored procedure execution in mysql
body: <p>I have a stored procedure in <em>mysql</em> thats to perform a task that needs to be synchronized such that if two application calls the stored procedure, only one can access a section of code to perform the task, keeping the other one to get blocked until the first one finishes the task.</p>
  
  <pre><code>DELIMITER $$
  CREATE PROCEDURE SP_GEN_ID(IN NAME VARCHAR(20))
  
  BEGIN 
    DECLARE maxLen int default 0;
  START TRANSACTION;
     #the section of code that needs to be synchronized
  COMMIT
  END;
  $$
  
  DELIMITER ;
  </code></pre>
  
  <p>So, if two applications call the stored procedure simultaneously, the task has to be synchronized.</p>
  
  <p>a. But <em>Start TRANSACTION</em> and <em>COMMIT</em> did NOT synchronize the execution.</p>
  
  <p>b. And LOCK TABLES <em>tableA</em> can not be used in stored procedure to ensure the synchronization too.</p>
  
  <p>c. I tried to synchronize the stored procedure call in application level. I used </p>
  
  <blockquote>
    <p>boost_interprocess scoped_lock lock();</p>
  </blockquote>
  
  <p>It worked perfectly fine in boost 1.41</p>
  
  <p>But interprocess locking mutex is not supported in the boost 1.34 library, which is what is available in my case.</p>
  
  <p>Is there a way to synchronize the stored procedure section of code such that when two calls are made simultaneously, one gets blocked before the other gets executed?</p>
  
  <p>(added the following) 
  edited code: to give an idea what I am trying to perform in the synchronized block of the stored procedure.</p>
  
  <p>It gets the last assigned id, and increment it by one and check whether it is not used for someother 'name' record. 
  When a valid id is found, update the last assigned id record table and then associate that with the 'name' given.</p>
  
  <pre><code>DELIMITER $$
  CREATE PROCEDURE SP_GEN_ID(IN NAME VARCHAR(20))
  
  BEGIN 
    DECLARE maxLen int default 0;
  START TRANSACTION;
     #the section of code that needs to be synchronized
      SELECT lastid into lastgenerated FROM DB_last_id WHERE key = 'NAME_ID';    
  findid_loop:
      LOOP
      set lastid = lastid + 1;
      #this is to check whether it was assigned with someother name before.
      IF not EXISTS (SELECT 1 FROM user_name_id WHERE name_id = lastgenerated) then
                       set nameid = lastgenerated;
                       set found = true;
                       LEAVE findid_loop;
              END IF;
  
              #for loop limit check
              IF (counter &lt; loopLimit) then
                      set counter = counter + 1;
                      ITERATE findid_loop;
              ELSE
                      #reached the limit and not found.
                      LEAVE findid_loop;
              END IF;
      END LOOP findid_loop;
  
       #if a valid id, update last id and assign to name.
       IF (found) THEN
              #update the id.
              update DB_last_id  set lastid = nameid where key = 'NAME_ID';
              insert into user_name_id values (nameid ,name);
       ELSE
              #return an empty string for the application to take action on the failure.
              set nameid = '';
      END IF;
  #end transaction here.
  COMMIT
  
  END;
  $$
  
  DELIMITER ;
  </code></pre>