Sqlite, Mysql, Php synchronization?

Question

PHP

Good day.
I am making a plugin for replenishing the game store both on the site and on the game server.
The game store every minute through the plugin accrues 5 units of currency and keeps the amount in sqlite.
Mysql is installed on the web hosting, and in the system of authorization and replenishment of the virtual account (on the site) there is a field for the balance in the table.
Actually, what is the question if you do synchronization:
site -> game server (then the balance in the game will be overwritten in case of replenishment of the site and different amounts of the site and the game)
game server -> site (then the balance on the site will be overwritten if the balance is replenished by the plug-in for time in the game)
The accrual of currency on the site is as follows:
replenishment through the payment gateway -> the current balance of the player on the site + the amount of replenishment.
Since the plugin counts separately and I don’t have source codes from it, the question is actually:
how to synchronize the balance between the game server and the site in a two-way manner and without loss of balance.
Given that there will always be out of sync balances.

Answer 1

You need to implement a locking mechanism.
It does not matter from where and where you will do the synchronization, the main thing is that at the time of updating the source table would be locked until the value is inserted into the target table. And for the duration of the lock, requests to update the source table should be queued, after the lock is released, they will be executed.
Look up how to lock tables in SQLite and MySQL, and then implement a locking synchronization algorithm.