from the docs:
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:
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.