title: Conflicting Transactions causes odd behavior
body: <p>I have an application that records sales transactions. During this process, inventory amounts are updated and logged. These queries run as a transaction. But if 2 of these transactions run at once; the inventory count and logging can be incorrect.</p>
  
  <p>Below is an example of queries. If they run at the same time the 2nd query might see the same value's that the 1st query saw resulting in only 1 of the transaction to update the quantity correctly. The transaction log will have both entries but the current quantity would be wrong if they run at the same time.</p>
  
  <p>What is the best way to ensure that these transactions run in a way that won't cause inconsistencies</p>
  
  <pre><code>INSERT INTO transaction_log(item_id, current_quantity, amount_changed) VALUES (1, 10,-1);
  INSERT INTO inventory (current_quantity, item_id) VALUES (10, 1) ON DUPLICATE KEY UPDATE quantity = 10
  </code></pre>