Race condition among each replicating databases

Question

Description:

I have six databases (MySQL) deployed in specific regions over the world, replication will be set up. Each database be connected by a web server, so total there is 6 web servers each connecting to one database.

Requirement:

For confidential reason I can't directly tell what my system needs, but just imagine I'm doing some sort of apple donation module, so there's two entities, donor (who gives) and donee (who takes)

i. Every donor have a lot of apples, doesn't matter how many they have, just imagine they have millions of apples. The amount would reduce as donee takes apples away from them.

ii. Each donee could only take one apple from a donor at a time, if there is any left.

Problem:

Say there is donor Patrick, he has only one apple left. This information is stored in database and replicated over all six databases, showing he has one apple left.

Donee Sam from region A and donee Alex from region D tries to take Patrick's apple at the same time.

Database A and D shows Patrick still has one apple left, so Sam and Alex successfully earned an apple from Patrick, reducing apple from Patrick by 2.

After replication finishes, depending on database logic, we would have Patrick owning 0 or -1 apple in the end, which both are wrong, I need to be able to queue the "taking" process.

My question is how can this be done over different regions?

I have thought of changing the replication topology from ring to star, where all update statement would goes to one and only one central database, so the locking can be done there.

But this implementation requires central database to have very high processing power as it has to process updates from whole world, while implementing locking mechanism.

Does anyone know any better way to accomplish this type of system design?

Answer 1

You are running into implications of the CAP theorem. From your requirements, you mostly need Consistency and Availability, but could sacrifice Partitioning. This means your data is consistent across all regions, and the system doesn't crash when one node goes down, but if one of your six nodes is down, the clients of that node can't use it.

One approach is to partition your data. Can you break out donors and donees so that they "belong" to particular region? If so, you could maintain consistency and availability within that region, and provide eventual consistency to the master for offline auditing.

If you can't partition your data, you might consider Percona XtraDB Cluster. I have no personal experience with it, but they claim to a drop-in replacement of MySQL replication which provides Consistency and Availablity at the cost of Partition safety.