Do not do this when database update

Preface : Do not do this when database update, what is this? Please come with me to see.

Earlier, the customer sent such a paragraph of the text "XX, XXXXXXXX has a 100,000 not to the account, to help Add." "I immediately looked at the database, looking for reasons, and then replied to the phrase" kidding. " Although there have been similar problems before, but are small funds, manually in the database Riga, encountered such a large amount of money or the first time, so I stepped up to look at the database record rhythm.

I found that the user's deposit record is indeed an audit pass status, and the user's available funds are not added, which has been an accident.

Then I looked at the code and didn't find any logical problems, which made me feel confused.

Then look at the log, found that there is no abnormality, OK, feel is set the same.

Looking back, this situation is rare, the procedure in most cases does not have this situation, everything is normal. This is bad, there is no error log is the biggest error.

Calm down, I think there may be a problem with the transaction, because for the money table, the same user's funds at the same time is likely to be updated, I guess, is the deposit record first inserted into the Deposit record table, wait until the time to update the money table is locked, the transaction time-out after the inserted record is not rolled back, So I'm going to prove it in this way.

 START TRANSACTION; INSERT  into record VALUES(1,123); UPDATE money SET money = money + ten; COMMIT; START TRANSACTION; UPDATE money SET money = money + +; COMMIT;

By manually controlling the transaction lock, I found that it was not what I thought. And then I tried it the following way:

 START TRANSACTION; UPDATE money SET money = money + ten; INSERT  into record VALUES(1,123); COMMIT; START TRANSACTION; UPDATE money SET money = money + +; COMMIT;

Find the effect is the same, the transaction will be rolled back.

At this time, my train of thought was imprisoned, I discussed with colleagues, to see what he thought, after some ideological struggle, the colleague still gave his idea, I deeply agree.

MoneyUser moneyUser = moneyUserMapper.selectByPrimaryKey(members.getUid());   // 冻结资金-出金金额   moneyUser.setFrozenl(moneyUser.getFrozen().subtract(moneyTransfer.getount()));   // 可用资金+出金金额   moneyUser.setTotaaymoney(moneyUser.getTotalpayey().add(moneyTransfer.getount()));this.moneyUserMapper.updateByPrimaryKey(moneyUser);

Such code in the event of concurrency, when acquiring the Moneyuser object, where the frozen funds and available funds to join are 0, then two concurrency at the time of the update, two are executed, but the initial freezing funds and the same available funds, will result in a sum of money is not added.

This problem is more easily detected when debugging. Oh,my Karma, is really a big problem, but also in the funding problem, I am glad that this problem occurs not high frequency. But I should be fortunate or not fortunate, perhaps the number of times, I will be more likely to reflect on the crux of the problem.

So how do we solve the problem?

UPDATE moneser  SET  frozapital = frozpital - #{amount,jdbcType=DECIMAL},  totaloney = totaloney + #{amount,jdbcType=DECIMAL}  WHERE uid = #{uid,jdbcType=INTEGER}

You do this by updating the fields in the SQL statement, not in the Java class, because MySQL itself is handled in this way.

This problem, before the leader has suggested that I try to add in the SQL statement when the update data, rather than the class field to do the processing.

Summary : This problem, if you inadvertently, it is easy to happen this problem, I did not have such a consciousness, it is sad and regrettable!

Do not do this when database update