Sample Header Ad - 728x90

Are MySQL InnoDB transactions serializable?

2 votes
2 answers
2367 views
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.
Asked by Adam Thompson (469 rep)
Mar 30, 2018, 04:36 AM
Last activity: Oct 24, 2018, 06:36 PM