1

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.

expert
  • 29,290
  • 30
  • 110
  • 214

2 Answers2

1

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.

gview
  • 14,876
  • 3
  • 46
  • 51
  • Rollback segment, binary log, and transaction log are all separate things. – Bill Karwin Dec 22 '21 at 20:25
  • @BillKarwin Where did I state that it was? – gview Dec 22 '21 at 20:26
  • @gview Thank you for detailed answer. But I'm curious if there could be more than one open transaction in the binlog at the same time. In other words, I want to understand if XID event in binlog ALWAYS commits transaction denoted by the most recent BEGIN query in binlog. Does it make sense? – expert Dec 22 '21 at 20:27
  • 1
    The question was about the binary log, but your answer includes info about the rollback segment (aka undo log), and then about the transaction log. – Bill Karwin Dec 22 '21 at 20:27
  • Yes, because the overall topic is about how this all fits together imnsho. – gview Dec 22 '21 at 20:28
  • Sorry for the confusion, folks. The question is only about order of serialization of events of transactions in binlog. – expert Dec 22 '21 at 20:29
  • @expert: no, again, I had to do a little editing to make some things clearer, but the binlog only contains completed transactions. – gview Dec 22 '21 at 20:30
  • @gview I see. Could you please confirm it by relevant .c file in mysql sources ? – expert Dec 22 '21 at 20:31
  • 1
    Perhaps you aren't interested in this topic anymore, but the source files having to do with the binlog are: [binlog.h](https://github.com/mysql/mysql-server/blob/8.0/sql/binlog.h) [binlog.cc](https://github.com/mysql/mysql-server/blob/8.0/sql/binlog.cc) [log_event_h](https://github.com/mysql/mysql-server/blob/8.0/sql/log_event.h) [log_event.cc](https://github.com/mysql/mysql-server/blob/8.0/sql/log_event.cc). – gview Dec 22 '21 at 21:24
  • 1
    If the existence of 2 phase commit means you can't have a naive implementation of whatever it is you are looking to do, very well, but XA and 2phase commit setups in general are pretty rare in my experience in the mysql using world. – gview Dec 22 '21 at 21:25
1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • (sigh) Yes, I totally forgot about XA transactions which breaks my simple implementation :( Thank you for pointing this out, Bill. – expert Dec 22 '21 at 20:47
  • MariaDB: XA transactions are logged after XA PREPARE in order of XA PREPARE. That means like conventional transactions only one is active at a time in binlog. Low chances MySQL proposes something different. – midenok Apr 14 '23 at 04:50