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
Last activity: Oct 24, 2018, 06:36 PM