Suppose there is a simple guessing game, in which the user bets big or small, after deducting 3% of the fee, all the money is put into the pool, and the winner divides the pool equally according to the proportion of the betting. Using MySQL as the database, the accuracy of the system is up to one decimal.
This article will explain the data problems caused by business settlement and the solutions.
Logic Design of Database
There should be a user wallet table in the system that specifies two records for the system revenue account and the system allocation account. In this way, the system account balance can be increased while betting, and the subtraction operation of the system account balance can be separated when awarding.
It can avoid the settlement of the previous game and the problem of lock waiting for the betting of the next game.
Considering the high concurrency, it is recommended to use exclusive locks with mysql, but not optimistic locks, because optimistic locks need to retry mechanism, while queue settlement is not considered for the time being.
When a user initiates a bet, the order of inspection should be as follows
- Check System Game Switch
- (Redundancy) Query whether the balance of a user is greater than the amount of this subscription.
- Open transaction
- Adding exclusive lock to system income account
- Adding Exclusive Locks to User Income Accounts
- Check whether the user balance is sufficient
- Deductions to users
- Collecting money from the system
- Adding 97% of the betting quota to the pool
- Transaction submission
The reason why we need to redundantly check the user’s quota is to avoid the consumption of open transactions and malicious attacks to consume system resources to open meaningless transactions.
97% of the prize pool quota needs to maintain one-bit accuracy. If the user’s bet is 98, the calculated value should be 95.06, we should take it.95.0Instead of95.1Otherwise, you will save more than 97% of the prize pool in the end, so the system extraction will not reach 3%. It doesn’t matter if the user points less, to ensure that the system points to 3%.
A simple sentence is:Abandoned after precision position
Design of award process
Assuming that according to the proportion of bets, the total number of bonuses allocated is22.1A user’s share is 55.5%. A user gets it.12.2655The share of B users is 45%. B users get it.9.8345。
In this case, you will find that, according to the principle of abandonment, they are12.2and9.8As a result, it’s only distributed.22If you follow the rounding principle, you can give it out.22.1
So why insist on abandoning the principle? Because, suppose an extreme case, when you come across the value of A, is12.05The value of B is9.05According to the principle of abandonment, the total is still22.1。 But according to the rounding principle, the total value of the distribution is22.2Now.
In the computer system, the calculation of floating-point numbers is not reliable in itself. Shaping should be used to avoid it in business. When designing percentage operation, please try to use the abandonment principle to ensure that it does not occur frequently. According to the abandonment principle, it is not important for business to give users less value of 0.05. If it is overissued, it will lead to accounting confusion in the system, and the consequences will be unimaginable.