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.

CodePudding user response：

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.

CodePudding user response：

First we have to caveat this that "transactions" are a function of a particular engine. InnoDB is the primary engine used by people so I'll focus on that.

Yes, certainly there can be multiple transactions, because if there wasn't you would never have deadlocks.

But the binlog doesn't include anything that wasn't committed:

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

So by necessity, the transaction log is inherently serialized.

MariaDB has some InnoDB documentation that includes this:

You can modify data on a maximum of 96 * 1023 concurrent transactions that generate undo records. Of the 128 rollback segments, InnoDB assigns 32 to non-redo logs for transactions that modify temporary tables and related objects, reducing the maximum number of concurrent data-modifying transactions to 96,000, from 128.000. The limit is 32,000 concurrent transactions when all data-modifying transactions also modify temporary tables.

The purpose of the log is to be able to recover from a catastrophic loss, by being able to replay completed statements and transactions. If recovery goes through the transaction log and a transaction is never committed, that transaction isn't in the transaction log.