Do not do this during database update.
Preface: Do not do this during database update. What is this? Please come with me.
Earlier in the year, the customer sent the text "XX, XXXXXXXX has a 0.1 million account not received, help add it ." I immediately checked the database, looked for the reason, and then replied to this sentence "joke ". Although such problems have occurred before, but they are all small amounts of money, and they are manually added to the database. The first time they encounter such a large amount of money, I stepped up to review the pace of database records.
I found that the user's deposit record was indeed approved, but the user's available funds were not added, which is already surprising.
Then I checked the code and did not find any logical problems, which made me confused.
Looking at the log, we found that there were no exceptions. Well, we felt like we were set up.
Looking back, this is rare. In most cases, the program does not. Everything is normal. This is too bad. The biggest error is no error log.
Vcq9wLTWpMP30rvPwqGjPC9wPgoKCgo8cHJlIGNsYXNzPQ = "brush: SQL;">START TRANSACTION;INSERT INTO record VALUES(1,123);UPDATE money SET money = money + 10;COMMIT;START TRANSACTION;UPDATE money SET money = money + 100;COMMIT;
By manually controlling the transaction lock, I found that it was not what I thought. Then I tried the following method:
START TRANSACTION;UPDATE money SET money = money + 10;INSERT INTO record VALUES(1,123);COMMIT;START TRANSACTION;UPDATE money SET money = money + 100;COMMIT;
The results are the same and transactions are rolled back.
At this time, my ideas were banned. I discussed them with my colleagues to see what they thought. After some ideological struggles, my colleagues gave his thoughts, I deeply agree.
MoneyUser moneyUser = moneyUserMapper. selectByPrimaryKey (members. getUid (); // freeze funds-monetary amount of moneyUser. setFrozenl (moneyUser. getFrozen (). subtract (moneyTransfer. getount (); // available funds + monetary amount moneyUser. setTotaaymoney (moneyUser. getTotalpayey (). add (moneyTransfer. getount (); this. moneyUserMapper. updateByPrimaryKey (moneyUser );
When such code is concurrent, when the moneyUser object is obtained, both frozen funds and available funds are added to 0, and both are executed during the update process, however, the initial frozen funds are the same as the available funds, resulting in a fund not being added.
It is easier to find this problem during debugging. OH, my dear, this is a big problem. I am glad the frequency of this problem is not high enough when there is a funding problem. But should I be glad or not lucky? Maybe when there are many times, it is easier for me to reflect on the problem to find out the crux of the problem.
So how can we solve the problem?
UPDATE moneser SET frozapital = frozpital - #{amount,jdbcType=DECIMAL}, totaloney = totaloney + #{amount,jdbcType=DECIMAL} WHERE uid = #{uid,jdbcType=INTEGER}
Update fields in SQL statements, rather than in Java classes, because mysql itself has such a processing method.
In this case, I was advised to add data in SQL statements instead of class fields when updating data.
Summary: This problem is easy to happen if you don't care about it. I didn't realize it at the beginning. It's really a pity!