Mysql with innodb and serializable transaction does not (always) lock rows

I have a transaction with a SELECT and possible INSERT. For concurrency reasons, I added FOR UPDATE to the SELECT. To prevent phantom rows, I'm using the SERIALIZABLE transaction isolation level. This all works fine when there are any rows in the开发者_如何学JAVA table, but not if the table is empty. When the table is empty, the SELECT FOR UPDATE does not do any (exclusive) locking and a concurrent thread/process can issue the same SELECT FOR UPDATE without being locked.

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;

..

This concept works as expected with SQL Server, but not with MySQL. Any ideas on what I'm doing wrong?

EDIT

Adding an index on display_order does not change the behavior.

There's something fun with this, both transaction are ready to get the real lock. As soon as one of the transaction will try to perform an insert the lock will be there. If both transactions try it one will get a deadlock and rollback. If only one of them try it it will get a lock wait timeout.

If you detect the lock wait timeout you can rollback and this will allow the next transaction to perform the insert.

So I think you're likely to get a deadlock exception or a timeout exception quite fast and this should save the situation. But talking about perfect 'serializable' situation this is effectively a bad side effect of empty table. The engine cannot be perfect on all cases, at least No double-transaction-inserts can be done..

I've send yesterday an interesting case of true seriability vs engine seriability, on potsgreSQl documentation, check this example it's funny : http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY

Update: Other interesting resource: Does MySQL/InnoDB implement true serializable isolation?

This is probably not a bug.

The way that the different databases implement specific transaction isolation levels is NOT 100% consistent, and there are a lot of edge-cases to consider which behave differently. InnoDB was meant to emulate Oracle, but even there, I believe there are cases where it works differently.

If your application relies on very subtle locking behaviour in specific transaction isolation modes, it is probably broken:

• Even if it "works" right now, it might not if somebody changes the database schema
• It is unlikely that engineers maintaining your code will understand how it's using the database if it depends upon subtleties of locking

Did you have a look at this document: http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

If you ask me, mysql wasn't built to be used in that way... My recomendation is: If you can affort it -> Lock the whole table.