Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Are you writing to the same tables on both masters? –  Aaron Brown Jun 22 '12 at 14:53
    
Yes, same table, same schema. –  Newton Jun 22 '12 at 15:51
    
You don't want to do that. –  Aaron Brown Jun 22 '12 at 15:52
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
Your answer would look a lot messier if we are talking Schooner or XtraDB Cluster. +1 !!! –  RolandoMySQLDBA Jun 22 '12 at 15:57
    
I'm not very familiar with Schooner, but I think Percona XtraDB Cluster (which is Galera compiled into Percona Server) would handle this situation better because of synchronous replication and the in-memory certification process that occurs. In my example, one of those COMMITs would just outright fail instead of causing data sync issues. –  Aaron Brown Jun 22 '12 at 21:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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