Transactions and Master + Slave Replication

Question

I'm looking to clear up a bit of confusion. I have a master + slaves setup. I have one master, and 3 slaves. All writes (INSERT|UPDATE|DELETE) go the master. All reads (SELECT) go to one of the slaves, which is chosen at random. All of my tables are using the InnoDB storage engine.

I'm curious how MySQL/InnoDB handles transactions in this setup. If MySQL writes each change within the transaction to the binlog, then all should be fine. However I can see there being some big problems if the binlog isn't written to until the transaction is commited.

Can anyone explain what's going on within MySQL during transaction with replication in place?

Harberd

Answer 1

According to this, the slaves can only see changes after the transaction is committed.

Transactions that fail on the master do not affect replication at all. MySQL replication is based on the binary log where MySQL writes SQL statements that modify data. A transaction that fails (for example, because of a foreign key violation, or because it is rolled back) is not written to the binary log, so it is not sent to slaves.

And this confirms:

The binary log is crash-safe. Only complete events or transactions are logged or read back.

Vyatka

Answer 2

Generally, if a transaction isn't written to the binary log until it is committed. Assuming the table is transactional, all the statements in the transaction are grouped together into one BEGIN \ COMMIT statement.

Oasis

Answer 3

It's deponde on you config, you can see https://dev.mysql.com/doc/refman/8.0/en/replication-features-transactions.html

Binding

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Enrapture