Shulou(Shulou.com)05/31 Report--

This article mainly introduces the database update problems when how to solve the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that you read this database update problems how to solve the article will have a harvest, let's take a look at it.

Don't do this when you update the database. What is this? Please follow me to have a look.

Earlier, the client sent a message like "XX, XXXXXXXX has a 100000 missed account, help add it." I immediately started checking the database to find out why, and then replied, "are you kidding?" Although there have been similar problems before, but all small funds were manually added to the database, and it was the first time that I encountered such a large amount of money, so I stepped up the pace of flipping through the database records.

I found that the user's deposit record has indeed been approved, but the user's available funds have not been added, which is already surprising.

Then I looked at the code and found nothing wrong with the logic, which confused me again.

Look at the log again and find that there is nothing abnormal. Well, it feels like it has been set up.

In retrospect, this situation is rare, the program does not have this situation in most cases, and everything is normal. This is too bad. No error log is an error.

Calm down, I think it may be the problem of the transaction, because for the fund table, the funds of the same user may be updated at the same time. I guess that the deposit record is first inserted into the deposit record table. When the fund table is updated, it is locked, and the record inserted after the transaction timeout is not rolled back, so I prove it in this way.

START TRANSACTION; INSERT INTO record VALUES (1123); 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 way:

START TRANSACTION; UPDATE money SET money = money + 10; INSERT INTO record VALUES (1123); COMMIT; START TRANSACTION; UPDATE money SET money = money + 100; COMMIT

It is found that the effect of the two is the same, and the transaction is rolled back.

At this time, my train of thought was imprisoned, and I discussed with my colleagues to see what he thought. After some ideological struggle, my colleagues still gave his ideas, and I deeply agree with them.

MoneyUser moneyUser = moneyUserMapper.selectByPrimaryKey (members.getUid ()); / / Frozen funds-moneyUser.setFrozenl (moneyUser.getFrozen (). Subtract (moneyTransfer.getount (); / / available funds + contribution amount moneyUser.setTotaaymoney (moneyUser.getTotalpayey (). Add (moneyTransfer.getount (); this.moneyUserMapper.updateByPrimaryKey (moneyUser)

When concurrency occurs in such code, when the moneyUser object is obtained, both frozen funds and available funds are added to 0, then both of them are executed in update, but the initial frozen funds and available funds are the same, resulting in a sum of funds not added.

It is easier to find this problem when debugging. OH,my, it's a big problem. When it comes to financial problems, I'm glad this problem doesn't happen frequently enough. But should I be lucky or not? maybe when it happens more often, it will be easier for me to reflect to find out the crux of the problem.

So how to solve the problem?

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

By updating the field in the sql statement, rather than in the Java class, because mysql itself will have this way.

This is the end of the article on "how to solve the problems in database update". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to solve the problems in database update". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.