title: What transactions diminish data integrity?
body: <p>I've learned a decent bit about database integrity, and know I should be using transactions if I "require multiple statements be performed as a unit to keep the data in a consistent state." <a href="https://stackoverflow.com/questions/621884/">Database development mistakes made by application developers</a> (point 16, chosen answer)</p>
  
  <p><a href="http://en.wikipedia.org/wiki/Database_transaction" rel="nofollow noreferrer">Wikipedia</a> uses the example:</p>
  
  <ol>
  <li>Debit $100 to Groceries Expense Account</li>
  <li>Credit $100 to Checking Account</li>
  </ol>
  
  <p>If I try to credit a non-existent account ID, and I'm using constraints properly, an exception will be thrown and I can catch it and roll back. If there is a power outage these two changes are guaranteed to be atomic.</p>
  
  <hr>
  
  <p>However, if I understand properly, transactions by themselves won't help me in all cases: (example with PHP and MySQL)</p>
  
  <ol>
  <li>MySQL: Start transaction</li>
  <li>MySQL: Select data from a table</li>
  <li>PHP: Compute state with the selected data
  
  <ul>
  <li>PHP: If the state is valid, insert data</li>
  <li>PHP: Otherwise, don't insert data</li>
  </ul></li>
  <li>MySQL: Commit transaction</li>
  </ol>
  
  <p>This won't work because the queries can be executed together atomically without failing (it's PHP that decides that there's an error, not some SQL constraint).</p>
  
  <p>Secondly, and I just tested, transactions are committed synchronously, but can be started asynchronously. <strong>If I start a transaction, and add a 10 second delay, I can start the slow script, and start and commit another transaction in that time,</strong> demonstrating concurrent transactions. Two instances can select the same data, before seeing the other's modifications. Only the modifications are guaranteed to be atomic.</p>
  
  <hr>
  
  <p>So what can I do? I suppose locking a table works, but is that good practice? Some conditions can be described with SQL in a single statement, but more complex ones can't.</p>