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.
Best Answer
Per the MySQL documentation, SERIALIZABLE is:
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.