title: SQL transaction and tables SELECT LOCK clarification
body: <p>I have a question regarding online transactions and want to clarify the below issue with <code>SELECT</code> and <code>UPDATE</code> queries.</p>
  
  <p>I will use the below example to explain this to you.</p>
  
  <p>X is doing an online transaction. His account balance is <code>$1000</code>. He is going to buy $200 item and after that his account balance should be <code>$800</code>.  That is fine; now let me elaborate this further</p>
  
  <pre><code>Begin DB transaction.
  
  Step 1: account balance is $1000
  
  {Select the balance from a different script}
  
  Step 2: Buy something for $200
  
  {Select the balance from another script}
  
  Step 3: Remaining balance $800
  
  Commit DB transaction
  </code></pre>
  
  <p>As you can see in between step 1 and 3 the balance has been checked by a different query outside the transaction and another script is checking balance again within step 2 and 3. </p>
  
  <ol>
  <li>With default Mysql transactions what will be the values for first
  and second balance results? Also by default is Mysql using <code>“SELECT
  ... LOCK IN SHARE MODE” or “SELECT ... FOR UPDATE”</code> features or do
  we have to use them within our queries.</li>
  <li><p>If we need to return $800 for both balance check queries above by
  waiting for the transaction to commit which method can we use Eg:
  “<code>SELECT ... LOCK IN SHARE MODE” or “SELECT ... FOR UPDATE</code>”</p></li>
  <li><p>Also do you know if Mysql and Post greSql handles the
  transactions differently as mentioned above?</p></li>
  </ol>