title: Database race conditions
body: <p>I've heard about many application developers having a bit of trouble in regards to race conditions in database processing. A typical example goes something like this:</p>
  
  <ul>
  <li>User 1 selects a field, say, numStock, which is 3</li>
  <li>User 2 also selects numStock, which is still 3</li>
  <li>User 1 decrements numStock (in the app), and sets it to 2 in the database.</li>
  <li>User 2 also decrements numStock (in the app), and sets it to 2 in the database.</li>
  </ul>
  
  <p>In this example, the numStock field should have become 1, but it was set to 2 instead due to the race between users.</p>
  
  <p>So of course locks can be used, but I've thought of another way of handling this - passing all row details as WHERE criteria. Let me explain... </p>
  
  <p>In the example above, the SQL codes might look like this:</p>
  
  <p>//select</p>
  
  <pre><code>SELECT itemID, numStock FROM items WHERE itemID = 45
  </code></pre>
  
  <p>//update</p>
  
  <pre><code>UPDATE items SET numStock = 2 WHERE itemID = 45
  </code></pre>
  
  <p>My idea for resolving the race:</p>
  
  <p>//select</p>
  
  <pre><code>SELECT itemID, numStock FROM items WHERE itemID = 45
  </code></pre>
  
  <p>//update</p>
  
  <pre><code>UPDATE items SET numStock = 2 WHERE itemID = 45 AND numStock = 3
  </code></pre>
  
  <p>Thus, the query checks if the data has changed since it SELECT-ed the data. <strong>So my question is:</strong> (1) Would this [always] work? and (2) is this a better option compared to database locking mechanisms (eg, MySQL Transactions)?</p>
  
  <p>Thanks for your time.</p>