Binlog - 'Race-like' Condition on Replication

Question

I have a typical master-slave MySQL setup. Slave is in test mode currently. Master is heavily used.

I am noticing something that looks like a potential race-like condition on the slave. On occasion, because of heavy use on the master, I will get 2 entries in the Master binlog with the same timestamp on a very heavily used table.

On the slave, the relay log is identical to the binlog on the Master so I know that part is working properly. However, when these statements are replayed I end up with a 1062 Duplicate error condition.

When I check the slave tables I get the first statement replayed properly but only part of the second. The binlog format is currently mixed.

I am considering testing the setup with row-based instead of mixed mode replication as I have read that it is suppose to help with such a condition - I have not used RBR myself before.

• Has anyone else had similar experiences?
• Do you have a resolution to this condition?
• Since RBR can have an impact on performance, is that significant from your experience?

Here were my settings:

• MySQL ver is 5.5.13
• Sync_binlog=1
• innodb_flush_log_at_trx_commit=1

Thanks

Answer 1

Replication is always serialized to prevent the race-conditions you are speaking about. See this stackoverflow post for my explanation of InnoDB locking. Some important notes:

• RBR with row-based replication and read-committed isolation level sets fewer locks than InnoDB would have previously.

• We should be moving to a world with RBR anyway, because this will open more doors for parallel replication threads (by working out dependencies between transactions).

To answer your last question, I use RBR in production, and I haven't seen any major performance hit. We do generate a lot more binary log data however, since the events logged contain whole rows.

Answer 2

RBR basically updates the slave with the entire row as it is after it has been inserted/updated and updates using the primary key of the table.

The advantages to this over statement is if the master server runs a statement which requires some sort of calculation or if it has a non-deterministic function or sub query in it.

With the former, you do not need to perform any calculation on the slave as you are only updating the row after the calculation has been applied to it.

With the latter, it may resolve race-conditions if the non-deterministic function or sub-query ran at a time when the result would be different then the time when it is run on the slave. So you can use this to keep your data consistent with these types of queries.

Also, it is important to note that you can use RBR in a session (if you are using MIXED binlog_format on your server) for part of your application that you feel has these types of statements (add to that if you create your own temp table and need the slave to do the same). You can do SET SESSION binlog_format = 'ROW'; in your application code for that specific (and potentially problematic) part of your code.

The disadvantages are if on the master, you run one statement that updates 100,000 rows. To update the slave, you just pass that one statement very quickly.

In my opinion, in transactions-based applications (where everything is just 1-2 inserted rows), then RBR can be advantageous. But in any case, you should test it out for yourself.