MySQL master to master race conditions, do I need to use transactions/locks for this code

master-master-replicationMySQLreplication

I'm playing around with master to master MySQL replication hoping I can run two copies on my C# application on two different servers to help improve performance. Rather than the one copy on one server I have currently.

However I'm struggling to understand how MySQL prevents or handles race conditions and maintains the integrity of the database. For example my application has code similar to the below.

SELECT 100 TASKS TO PROCESS

UPDATE THE 100 TASKS TO SAY WE'RE WORKING ON THEM

INSERT THE RESULTS OF THE 100 TASKS INTO THE DATABASE

Because the code above runs so frequently eventually my application will try to work on a task that has already been "SELECTED" but not "UPDATED" yet by the other instance of my application and when it does that it will try and "INSERT" data that has already been inserted and this produces unique key errors which break the MySQL master to master replication.

I can see from this answer that the mysql instances seem very separate and appear to have no awareness of each others locks/transactions.

https://stackoverflow.com/questions/36026769/transactions-between-two-replicating-master-mysql-servers

I can see that I can disable unique key errors with the config below but I'm not sure if that will cause more problems than it will fix.

slave-skip-errors=1169

Is this something I can avoid with locks and transactions?

Or is this just part of master to master replication and I somehow have to design my application around this?

Best Answer

In short - yes, you will have to engineer your application in a way that makes it resistant to race conditions. Transactions won't help you with this.

Related Question