Binlog - 'Race-like' Condition on Replication

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

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.

StackExchange Database Administrators Q#6606, answer score: 2