`SELECT FOR UPDATE` in master-master mysql setup

Question

I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration.

For the moment it seems to work, but I am either doing INSERT on one and SELECT FOR UPDATE + UPDATE on the other, or doing it on both at the same time.

There is a big lag in the replication (the numbers shows that it keeps growing over time). What would happen if I do some SELECT FOR UPDATE + UPDATE on both while still doing some INSERT on one and not both ?

Can the database be corrupted ?

Answer 1

Generally, it is considered a very bad idea to write to both masters in master/master replication, unless you are writing to different schemas.

MySQL replication is asynchronous (MySQL 5.5 has "semi-synchronous" replication, but it's still asynchronous in practice). There is no mechanism to synchronize locks or detect conflicts between hosts.

Consider what happens in this situation

master1> BEGIN;
master1> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master1> UPDATE talbe SET col = "Newton" WHERE id = 7;

master2> BEGIN;
master2> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master2> UPDATE table SET col = "Aaron" WHERE id = 7;

master1> COMMIT;
master2> COMMIT;

Depending on timing, replication lag, and whether you have ROW or STATEMENT based replication, you might end up with different data on different hosts. Certainly, master1's FOR UPDATE will not lock the rows on master2 and vice versa, thus negating the utility of FOR UPDATE (which is to be avoided).

In your situation you could, at best, end up with broken replication and at worst end up with silent data sync issues between the servers.