title: How to avoid race condition in MySQL InnoDB?
body: <p>We're using MySQL InnoDB. The system support multiple request that can happen simultaneously.
  Here is a simplified example of what we're trying to achieve.
  Let's assume we have a table that holds entities that represent light bulb. These light bulbs can be turned on (activated) by users. Users can add light bulbs to the table, which initially are deactivated, and only 10 of them can be marked as activated. So we have a limit of 10. Since the system supports concurrency, there is a risk of race condition based on the following example:</p>
  
  <p>SESSION 1:</p>
  
  <p>1: Get the number of active bulb</p>
  
  <p>2: Check the number of activated bulbs</p>
  
  <p>3: If limit not reached yet, activate the bulb and commit.</p>
  
  <p>4: Otherwise rollback.</p>
  
  <p>The issue here is that during step 2 and 4 another session could enter and could end up exceeding the limit.</p>
  
  <p>This is a sql representation of what we are doing currently:</p>
  
  <ol>
  <li>SELECT count(id) AS total FROM tbl WHERE is_active = 1;</li>
  <li>if(total &lt; 10) {</li>
  <li>activate another bulb;
  } else {</li>
  <li>rollback;
  }</li>
  </ol>
  
  <p>What do you recommend as a solution for this scenario?
  We were considering "select for update" but that, if I'm not mistaken, locks selected rows and users can add bulbs to the table while selected rows are under lock.</p>
  
  <p>Thank you.</p>