Are MySQL InnoDB transactions serializable?

Question

I am confused whether or not MySQL transactions are serializable and whether or not read-anomalies are guarded against?

For example, what if I SELECT a value from a row, then UPDATE that row by incrementing the value. There is an opportunity for another query to UPDATE the row which is being read, leading to an improper update.

However, I was under the impression that even if I wrap these two statements in a transaction, that I am not guaranteed to be free of race conditions. See this StackOverflow thread. So to prevent this I used UPDATE with COALESCE which is a single query and thus guaranteed to be atomic. Was this assumption correct? Or would a transaction have worked here to guarantee no race conditions?

I see from the MySQL 5.7 documentation on isolation levels that transactions are by default REPEATABLE_READ rather than SERIALIZABLE. What if I had set the transaction level to SERIALIZABLE? I attempted to read the documentation on REPEATABLE_READ but it did not increase my understanding with regards to this issue.

Answer 1

Per the MySQL documentation, SERIALIZABLE is:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Locking rows FOR SHARE is not equivalent to a SERIALIZABLE transaction because it doesn't protect against rows being inserted by racing transactions that could have been returned by a SELECT statement.

The PostgreSQL docs on SERIALIZABLE provide an example case where this will occur. In your specific case, I believe MySQL's SERIALIZED isolation level will not protect you because two competing transactions can both lock the row FOR SHARE, then both can issue UPDATEs incrementing the value, one transaction will go through, the next will wait for the first to commit, but then go through. You will get a net of one increment instead of two.

Answer 2

• Minimize what you put in transactions -- to keep them fast.
• Always check for errors and restart the transaction.

Bottom line: Race conditions will be rare and you will handle those that do occur.

As for SERIALIZABLE -- that is the slowest mode; don't use it for speed.

Anyway, are you expecting thousands of transactions per second?