2

In MySQL documentation it's written:

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.

Why statement-based logging format is not working with READ COMMITTED or READ UNCOMMITTED?

1

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order.

More specifically, from the Change Log:

----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- -----

With statement-based binary logging format and a transaction isolation level of READ COMMITTED or stricter, InnoDB printed an error because statement-based logging might lead to inconsistency between master and slave databases. However, this error was printed even when binary logging was not enabled (in which case, no such inconsistency can occur). Bug #40360

  • 1
    So when in the row-based advantages documentation it says "Fewer row locks are required on the master, which thus achieves higher concurrency..." does it assume one of the above isolation levels is used? – Benny Bauer May 11 '15 at 10:51
  • Hmmm... I can't think why the locks would be different on the Master based on the binlog format. Are you having performance problems on the Master? And not the Slave? If so, can we look at specific queries? There could be other things going on. – Rick James May 11 '15 at 14:21
  • No problems yet, it's still a theoretical discussion, I'm trying to understand why MySQL documentation states that there are fewer locks on master when the row-based logging is used (see reference above) – Benny Bauer May 11 '15 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.