title: Mysql with innodb and serializable transaction does not (always) lock rows
body: <p>I have a transaction with a <code>SELECT</code> and possible <code>INSERT</code>. For concurrency reasons, I added <code>FOR UPDATE</code> to the <code>SELECT</code>. To prevent phantom rows, I'm using the <code>SERIALIZABLE</code> transaction isolation level. This all works fine when there are any rows in the table, <em>but not if the table is empty</em>. When the table is empty, the <code>SELECT FOR UPDATE</code> does not do any (exclusive) locking and a concurrent thread/process can issue the same <code>SELECT FOR UPDATE</code> without being locked.</p>
  
  <pre><code>CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    display_order INT
  ) ENGINE = InnoDB;
  
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  START TRANSACTION;
  SELECT COALESCE(MAX(display_order), 0) + 1 from t FOR UPDATE;
  
  ..
  </code></pre>
  
  <p>This concept works as expected with SQL Server, but not with MySQL. Any ideas on what I'm doing wrong?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Adding an index on display_order does not change the behavior. </p>