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?