Transactions and Master + Slave Replication

6
votes

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?

2
mysqldatabasereplicationinnodb

2 Answers

3
votes

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.

0
votes

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.