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.
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.
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?