title: Wait for other transaction to commit/rollback before beginning another transaction?
body: <p>How can I make my transactions to wait if there is still an un-committed/un-roll-backed transaction in MySQL?</p>
  
  <p>Currently I do my transactions on the code side, not on the DB Stored Procedure side, like this:</p>
  
  <pre><code>cmd.Connection.BeginTransaction();
  try {
      // db codes here
      cmd.Transaction.Commit();
  } catch {
      cmd.Transaction.Rollback();
      throw;
  } finally {
      cmd.Connection.Close();
  }
  </code></pre>
  
  <p>I want for other transactions to wait until the previous transaction is finished. Since I have, in some of my stored proc, that gets the <code>MAX(id)</code> when inserting for a foreign keys, where <code>id</code> is an auto-incremented column that is also created on the same transaction. But it doesn't work on my current setup above when 2 transactions occur at the same time, what happened is like this:</p>
  
  <pre><code>Begin Trans1
  Begin Trans2
  Trans1: insert a values(); --id = 1
  Trans2: insert a values(); --id = 1
  Trans1: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
  Trans2: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
  Commit Trans1
  Commit Trans2 --id = 2, aid = 1
  </code></pre>
  
  <p>What I was hoping is like this:</p>
  
  <pre><code>Begin Trans1
  Wait Trans2
  Trans1: insert a values(); --id = 1
  Trans1: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
  Commit Trans1
  Begin Trans2
  Trans2: insert a values(); --id = 2
  Trans2: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 2
  Commit Trans2
  </code></pre>
  
  <p>Is it possible like this?</p>