title: update statement with where clause on current value
body: <p>I am trying to execute an update statement to update the balance in my database table. This table simply stores the userId and currentBalance.
  I am using mysql with InnoDB.</p>
  
  <p>I was thinking that a query like -</p>
  
  <pre><code>update account a set a.BALANCE= a.BALANCE + WITHDRAW_AMOUNT where a.USER_ID = ?2 and a.BALANCE + WITHDRAW_AMOUNT &gt; 0
  </code></pre>
  
  <p>This query is meant to deposit or withdraw money ( in the case of withdrawal the net balance should not dip below 0)</p>
  
  <p>If say I have a user with id 2. His current balance is 500.
  There are 2 requests coming to withdraw money concurrently - </p>
  
  <p>Req 1 - 300</p>
  
  <p>Req 2 - 400</p>
  
  <p>Now I was thinking that when the rows are being selected to update using the where condition could that happen concurrently? From what I understand right now the execution of the update statement does acquire a lock on the rows.</p>
  
  <p>If yes, then say Req1 acquires lock for the user with id 2 and Req2 goes into waiting for these rows. Then our balance will be updated to 200.</p>
  
  <p>Now the balance is 200, so will Req 2 execute because the rows have been selected or will it not because the entire statement is executed again for Req 2 and the where clause returns false since WITHDRAW_AMOUNT+balance&lt;0 now?</p>