We created two new mysql servers running 5.5. Our old server were 5.0.

The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues.

We have some long running select queries (40-50 sec), and when they are run replication breaks - we have to skip a couple of records to get replication restarted. This is consistent and repeatable. I suspect that some tables are getting updated on each server, but replication is paused trying to update a locked table - and when it is done, each server is trying to send data to the other and replication is broken.

I am seeing a lot of "waiting for table level lock" on select statements when this long query is running.

Is there a setting I've missed to prevent selects from locking tables? Is there some other cause, or any way to fix this?

  • Is any or all of your data MyISAM ??? Commented Jun 26, 2014 at 20:34
  • Yes, it's all MyISAM.
    – chris
    Commented Jun 26, 2014 at 20:41

1 Answer 1


Since all the data is MyISAM, you should expect table locks rather frequently since INSERT , UPDATE, and DELETE command issues a full table lock.

If you have a lot of tables that use auto increment keys, you should add the following


auto_increment_increment = 5
auto_increment_offset = 1


auto_increment_increment = 5
auto_increment_offset = 2

This will prevent PRIMARY KEY collisions with autoincrement columns

I have other suggestions in my post I have been tasked with Mysql Master-Master replication?

  • That seems to have helped - I wondering why with a 5.0.x system it wasn't need, but we started seeing errors on the 5.5 system?
    – chris
    Commented Jun 27, 2014 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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