title: MySQL: Transactions vs Locking Tables
body: <p>I'm a bit confused with transactions vs locking tables to ensure database integrity and make sure a SELECT and UPDATE remain in sync and no other connection interferes with it.  I need to:</p>
  
  <pre><code>SELECT * FROM table WHERE (...) LIMIT 1
  
  if (condition passes) {
     // Update row I got from the select 
     UPDATE table SET column = "value" WHERE (...)
  
     ... other logic (including INSERT some data) ...
  }
  </code></pre>
  
  <p>I need to ensure that no other queries will interfere and perform the same <code>SELECT</code> (reading the 'old value' before that connection finishes updating the row. </p>
  
  <p>I know I can default to <code>LOCK TABLES table</code> to just make sure that only 1 connection is doing this at a time, and unlock it when I'm done, but that seems like overkill.  Would wrapping that in a transaction do the same thing (ensuring no other connection attempts the same process while another is still processing)?  Or would a <code>SELECT ... FOR UPDATE</code> or <code>SELECT ... LOCK IN SHARE MODE</code> be better?</p>