When the database is updated, you will suffer.

When the database is updated, you will suffer.

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.

Calm down, I think it may be a problem with the transaction, because for the Fund table, the funds of the same user may be updated at the same time. I guess, it is the deposit record that is first inserted into and out of the gold record table, and is locked when the Fund table is updated. After the transaction times out, the inserted record is not rolled back, so I can prove it in this way.

 
 

  
  
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
  
  
MoneyUser. setFrozenl (moneyUser. getFrozen (). subtract (moneyTransfer. getount ()));
  
  
// Available funds + deposit 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!

Blog Source: http://blog.csdn.net/qing_gee/article/details/46233301