MySQL Forums
Forum List  »  Performance

Understanding Serializable Isolation Level
Posted by: morpheus na
Date: April 15, 2023 10:32AM

from the docs:
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html


SERIALIZABLE

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

if we consider the example given here:
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

As an example, consider a table mytab, initially containing:

class | value
-------+-------
1 | 10
1 | 20
2 | 100
2 | 200
Suppose that serializable transaction A computes:

SELECT SUM(value) FROM mytab WHERE class = 1;
and then inserts the result (30) as the value in a new row with class = 2. Concurrently, serializable transaction B computes:

SELECT SUM(value) FROM mytab WHERE class = 2;
and obtains the result 300, which it inserts in a new row with class = 1.

---
Per MySQL docs, Tx1 will lock rows 1,2 and Tx2 will lock rows 3,4. And both will be able to insert concurrently leading to a serialization anomaly.

I tried running this example on MySQL 8.0 and it correctly handled the problem. It blocked one Tx and when both tried to commit it detected a deadlock and aborted one of the transactions. I am very impressed with it, however as per docs both transactions should have gone through since SELECT FOR SHARE would lock rows 1,2 for tx1 and rows 3,4 for tx2. Can someone explain this to me? what is happening behind the scenes in MySQL with this example? thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Understanding Serializable Isolation Level
202
April 15, 2023 10:32AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.