Search code examples
mysqlmysqlbinlog

Concurrency transactions behavior on mysql binlog events

If many transactions commit at the same time, how those binlog events record? Continuous or discrete？

Continuous maybe like：

trx1 begin transction
trx1 update someting
trx1 commit
....
trx2 begin transction
trx2 update someting
trx2 commit

Discrete maybe like:

trx1 begin transction
trx2 begin transction
trx1 update someting
trx2 update someting
trx2 commit
trx1 commit
Solution

  • https://dev.mysql.com/doc/refman/8.0/en/binary-log.html says:

    Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

    This means the binary log writes whole transactions, as in your first example. They are not interleaved as in your second example.

    Things get more interesting if you update both transactional tables (e.g. InnoDB) and non-transactional tables (e.g. MyISAM) during a given transaction.

    I recommend to always use InnoDB unless you specifically need some feature of MyISAM.