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