The role of innodb_support_xa

Innodb_support_xa can switch on and off xa two-stage transaction commit for InnoDB.

How to open it?

Innodb_support_xa=true, supporting xa two-segment transaction commit.

The default is true, and the value is on. Commit transactions are executed concurrently by multiple threads, and binlog is written according to the order of transactions. If closed, the order in which binlog records transactions may be inconsistent with the actual order, resulting in slave inconsistency.

Mysql > show global variables like 'innodb_support_xa'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_support_xa | ON |

+-+ +

1 row in set (0.01 sec)

Internal XA

Now a process within mysql looks something like this:

1. Prepare, and then persist the redo log to disk

two。 If the previous prepare is successful, then continue to persist the transaction log to binlog

3. If the previous is successful, then write a commit record in redo log

So if any of the three steps fail, how does crash recovery do it?

At this point, the transaction starting from the most recent checkpoint is read out from redo log, and then restored with reference to the transaction in binlog.

If it is at 1 crash, then naturally the whole transaction will be rolled back

If it is at 2 crash, then the entire transaction will also be rolled back

If it is at 3 crash (only the commit record is not written successfully), it doesn't matter because the binlog of the transaction is already recorded in 2, so commit this. So to sum up, all transactions in redo log that prepare succeeds but commit fails will first go to binlog to determine whether they exist (through XID, do you often see Xid=xxxx in binlog? This is the xa transaction id). If there is one, the transaction will be commit, otherwise rollback.