2

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 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.

Tobias G.
  • 74
  • 1
  • 7

3 Answers3

1

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?

Community
  • 1
  • 1
regilero
  • 29,806
  • 6
  • 60
  • 99
  • I noticed the possible deadlocks/timeouts as well. Handling the deadlocks/timeouts and retrying the transactions would be possible but I would prefer a clean solution without possible deadlocks. – Tobias G. Jan 12 '11 at 13:22
  • If you handle serializable transaction you must already handle exceptions in your transaction handling (with retry loops). deadlock abort is just another exception to handle, it's the same algorithm from the application. Serializable transaction are very likely to get 'unzerializable exceptions', I would just consider the deadlock detected exception the same way. – regilero Jan 12 '11 at 13:35
  • We do handle these exceptions of course (and also retry the transaction when we see a deadlock/timeout exception), but I'm wondering why the (exclusive) lock isn't taken (which should prevent any possible deadlocks). – Tobias G. Jan 12 '11 at 13:46
  • I suspect that the EXCLUSIVE SHARE is not stored on the table but on every row of the table... but there's certainly something stored at table level as new rows obtains this share... you should maybe fill a bug report on MySQL, you'll get informations from real gurus. – regilero Jan 12 '11 at 13:59
  • You probably don't need to use select for update in SERIALIZABLE mode. – Seun Osewa Mar 13 '12 at 22:20
0

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.

Florian F
  • 4,044
  • 3
  • 30
  • 31
  • better look at 5.1 documents, isolation levels management is quite different now – regilero Jan 12 '11 at 12:56
  • the Mysql documentation explicitly recommends using FOR UPDATE for these cases as I understand it. It's just that the semantics seem to be different with empty tables. – Tobias G. Jan 12 '11 at 13:15
0

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
MarkR
  • 62,604
  • 14
  • 116
  • 151
  • I agree that this isn't necessarily a bug, I'm just wondering why it doesn't work the way I would like to see it. I couldn't find anything about this issue in the Mysql documentation. – Tobias G. Jan 12 '11 at 13:25