There are InnoDB tables with user accounts that are on different shards. The task is to make an analogue of the transaction for transferring from one account to another. How I do: 1. I reduce one account 2. Then I increase the other 3. I compare them after the change. If it doesn't fit, I roll it back.

With such a scheme, a situation of “race” is likely on my site, if between requests to the database another request changes the account of one of the users.

How to avoid it? Keep all accounts on the same server in one database and use the built-in transaction mechanism? But such a scheme is not scalable in contrast to sharding.

    2 answers 2

    I think you should look in the direction of locks ( table lock and record lock ) records in InnoDB. Here is a small sharper.

    The scheme of work seems such:

    1. Reducing the first account, setting a lock on this entry.
    2. Increase the second account, setting a lock on this entry.
    3. Transaction comparison
    4. Unlocking.

    This may reduce the speed of the program, but such is the charge for synchronization.

      I think it would be right here not to manipulate the decrease / increase, but to create a transaction table - in one record there is a negative amount for the account number, in the other one it’s the same amount, and I’ll probably still link them.

      And the balance of the account, calculate the amount of transactions

      • one
        +1 Probably in hindsight, someone will ask about the history of transactions. - Sergiks