The payment system encountered a problem of reading the balance of the account and locking it. How to solve it? - Codes Helper - Programming Question Answer

The payment system encountered a problem of reading the balance of the account and locking it. How to solve it?

when we do the payment system for the first time, there are many merchants on our platform. Each merchant has a large number of orders per minute. I update the balance to the merchant account asynchronously. I use the mysql queue, and each successful order will be added to the queue as an item, crontab to read and update to increase the account balance, but encountered a problem of withdrawing.

this is what I do to withdraw cash, the account balance minus the withdrawal amount, and then update the remaining money to the account balance field, there will be a problem!

1. When you withdraw cash, you get an account balance of 10 yuan, and you want to withdraw 5 yuan, and the withdrawal operation is in progress, but at this time, an order of 5 yuan comes in, and asynchronously needs to update the account. You still get 10 yuan, plus 5 yuan, and then update 15 yuan to the balance field. At this time, the withdrawal operation is also under way, withdrawing 5 yuan, and then updating the remaining 5 yuan to the balance field, covering the previous 15,

.

2. I didn"t add a lock when I checked the balance. Can locking solve the problem? is there any better way to solve the problem besides adding a lock? Whether the logic of updating account balance and withdrawing account asynchronously needs to be improved. I hope you can give us some comments. Thank you

the first item is entered into the account, the first field is the second balance of ID, the third order amount, the fourth updated balance,
the second is the withdrawal, the second is the withdrawal, the third is the withdrawal amount, and the fourth is the same as above

.

Oct.10,2021

definitely opens the transaction and locks it with for update.. Because you have to remember the current balance, you need to use the current balance of the user
if you do not need to use the current balance of the user, you can try the atomic operation of sql:

update user set balance = balance-10 where user_id = xxx;

first of all, the operation of the user account needs to be locked, regardless of consumption or recharge. For example, when recharging, you need to obtain an account lock first. After success, other operations, such as consumption, cannot acquire the account lock and can only wait for the lock to be released.
secondly, you can add a condition to the SQL statement to achieve the failure effect of account changes, for example:

update user set balance = balance+5 where user_id = xxx AND balance = 5;

add that the original balance is 5, but now you want to recharge 5, then add balance=5 to the condition, so that if the balance has been modified, the update fails. So as not to overwrite the data.

again, the change of user balance needs to be streamlined, and when the amount does not match, it can be traced back through pipelining.

optimistic lock.
add a version number field to the balance table. For each operation, the balance version number is + 1. Before updating the balance, get the version number first. Update process:

select version from table order by version desc limit 1;

update table set balace = xx,version = version + 1 where version = xx;

if the update fails (returns 0), continue the above operation, which is called spin lock.

increase the balance

set autocommit=0;
update <table> set balance=balance+<xxx> where id=<id>;
select * from <table> where id=<id>
commit;

make sure that the above two statements are executed in the same transaction. Finally, the operation is done in the program.

balance-< xxx > is the balance before the increase

balance is the increased balance

withdraw

set autocommit=0;
update <table> set balance=balance-<xxx> where id=<id> and balance>=<xxx>;
select * from <table> where id=<id>;
commit;

make sure that the above two statements are executed in the same transaction. Finally, the operation is done in the program.

balance + < xxx > is the balance before withdrawal

balance is the balance after withdrawal

Previous: Style settings for the QprogressDialog class of python PyQt5

Next: How to write the force guide diagram of d3.js version 5.0 or above?

Menu