Search code examples
mysqlserialization

Why does MySQL set Transaction level by session

Why MySQL can have multiple sessions set with different transaction levels to operate on the same table without conflicts?

If transaction A is Repeatable reading and transaction B is Serialization, what happens to transaction A when transaction B runs?

Solution

  • SERIALIZABLE transactions do not block other transactions from reading, regardless of the other transaction's isolation level.

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

    That's all it does.

    Multiple sessions can still read the same data concurrently, and they don't conflict. Even multiple SERIALIZABLE transactions can all read the same data, because multiple sessions can hold shared locks.

    Where they conflict is with exclusive locks. So they block, and are blocked by, any transaction that tries to get an exclusive lock on those rows. E.g. SELECT ... FOR UPDATE, or any other DML like INSERT/UPDATE/DELETE or DDL like ALTER/DROP/TRUNCATE.

    This means changes to the table are serialized, but reads are not.