Can MySql binlog have more than one open transaction?

Issue

Can MySql binlog have more than one open transaction at the same time (= events of different transactions are interleaved in binlog) ?

There is XID event that contains transaction ID but there is no event that denotes beginning of transaction and contains transaction ID. I made "and" bold because there is QUERY event with query "BEGIN" in it but it doesn’t say what transaction it belongs to.

Or does mysql serialize transactions in binlog even if several of them are active in the DB ?

Looking at debezium sources here it seems answer is NO, but I’d love to see confirmation in sources of mysql or official documentation.

Solution

For conventional transactions, the binary log can cannot contain any uncommitted transactions. Data changes are not written to the binary log until they are committed.

But XA transactions are different. The XID event is part of an XA transaction. There may be multiple "prepared" XA transactions in the binary log.

https://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html says:

Note that the initial part of the transaction, identified by XA_prepare_log_event, is not necessarily followed by its XA COMMIT or XA ROLLBACK, which can cause interleaved binary logging of any two XA transactions.

That doesn’t state explicitly that the binary log can contain multiple open XA transactions, but it’s implicit. It would not make sense for XA transactions to be interleaved if there could only be one active at a time.

Note this applies to MySQL 5.7.7 and later. Earlier versions of MySQL did not support binary logging XA transactions at all.

If you want to read the source, it’s in sql/xa.cc.

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published