1

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?

  • 1
    "I somehow have to design my application around this" -- yes. – mustaccio May 13 at 19:30
  • 1
    You could use a message queue (non-SQL) that is highly suited to this form of resource allocation. Even if you just use the message queue to store MYSQL primary keys that's still an improvement and bypasses all MySQL complexity because it doesn't implement a message queue and doing so is hard. – danblack May 14 at 3:59
1

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.

| improve this answer | |
1

Several comments...

  • Master-Master won't help much -- all writes to one master are 'replicated' to the other. Hence you don't get much write scaling.
  • Two clients (or even N clients) could contend on a single master. But the contention is easily handled with transactions.
  • A single UPDATE can grab the 100 tasks atomically.

A suggestion:

Loop:
    UPDATE Tasks SET who_has = $me
        WHERE ... -- if filtering needed
        ORDER BY ... -- if needed
        LIMIT 100;
    SELECT * FROM Tasks WHERE who_has = $me;
    process the tasks
    UPDATE Tasks SET who_has = NULL ...
        WHERE ... -- either all 100 or one at a time;
end_loop.

If the "tasks" might interfere with each other, then use BEGIN...COMMIT. But let's discuss its details. Simple INSERTs can be done without any hassle.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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