StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIn MySQL, why is it safe to turn off innodb_support_xa for single threaded updates?
text
Body
copied!<p>In MySQL's docs the following is said about the <code>innodb_support_xa</code> option:</p> <blockquote> <p>Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. <strong>If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them.</strong> This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.</p> <p><strong>For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance for InnoDB tables.</strong> For example, you can turn it off on replication slaves where only the replication SQL thread is changing data. </p> </blockquote> <p>However, from my understanding from reading on the infamous <a href="http://kristiannielsen.livejournal.com/12408.html" rel="nofollow">group commit bug</a>, 2PC is used to guarantee that the transaction log and the binlog contain the same set of transactions, while the <code>prepare_commit_mutex</code> is responsible for ensuring the same order.</p> <p>With <code>prepare_commit_mutex</code>, the writing to the transaction log and the binlog are already serialized, then what's the difference between multi-threaded and single-threaded updates?</p> <p>On the other hand, even if there is only one thread that can change data, without 2PC, if a crash occurs after a transaction is written to the binlog, but before it's written to the transaction log, how is Innodb going to deal with this situation in recovery? In theory it can simply execute the outstanding transaction in the binlog, just as what the slaves do, but I doubt Innodb actually does that, because otherwise why do we need 2PC at all?</p> <p>I'm not familiar at all with the internals of MySQL, so please forgive me if I'm terribly wrong. Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload