On-board account system balance concurrent update problem records

On an e-commerce platform, an online user reports a bug saying that the account balance information is not consistent with the transaction flow. It can be considered as a problem of concurrent database updates, so as to identify specific causes and provide solutions.

Symptom

Scenario Description

In the online accounting system, when the regular settlement is made to the seller and the high concurrency occurs, after the withdrawal of X yuan (assuming that the current user balance is X yuan) is 0, A sum of money is transferred to the Account (assuming y yuan). As a result, the account balance is changed to x + y yuan, resulting in incorrect user balance. PS: Changes to the account balance are updated in the transaction.

Environment Description

Mysql5.7 + InnoDB, transaction isolation level is REPEATABLE-READ

Scenario Simulation

We simplified the offline data structure and simulated the scenario. The data table is as follows:

'Account master table'

Create Table user (

UID int (11) not null comment 'Type ID + auto-incrementing sequence ',

Name varchar (32) default null,

Primary Key (UID)

) Engine = InnoDB default charset = utf8 comment = 'account master table'

'Account balance detail'

Create Table user_account (

UID int (11) not null,

Amount decimal (19,4) default 0 comment 'account balance ',

Primary Key (UID)

) Engine = InnoDB default charset = utf8 comment = 'account balance details'

Account type configuration

Create Table user_conf (

Type_id int (11) not null, description varchar (32) default null comment 'Type description', primary key (type_id )) engine = InnoDB default charset = utf8 comment = 'account type configuration'

The specific data is:

Select * from user;

+ ------- + ------ + | Uid | Name | + ------- + ------ +

| 10001 | A |

| 1, 10002 | B |

Select * From user_account;

+ ------- + ---------- + | Uid | amount | + ------- + ---------- +

| 10001/10.0000 |

| 10002/108.9900 |

Select * From user_conf;

+ --------- + -------------- + | Type_id | description | + --------- + -------------- +

| 100 | external account |

| 200 | internal account |

The transactions that simulate the concurrent operations of withdrawal (that is, the balance is reduced) and Accounting (that is, the balance plus) are as follows:

Session1-Withdrawal 10 RMB session2-account 20 RMB begin; select description from user_conf where type_id = 100; selectFrom user where uid = 10001 for update; // The User table is used for mutex select amount from user_account where uid = 10001; // 10.00 begin; select description from user_conf where type_id = 100; selectFrom user where uid = 10001 for update; // wating // watingupdate user_account set amount = 0.00 where uid = 10001; commit; get the lock select amount from user_account where uid = 10001; // 10.00 is recorded as 20 RMB. After calculation in the code, it should be 30 RMB update user_account set amount = 30.00 where uid = 10001; commit;

When the problem occurs, the user's balance is 30 yuan, that is, the user's withdrawal of 10 yuan is not reflected in the balance

Locate the cause

Those familiar with MySQL may already know that the problem is caused by snapshot reading at the REPEATABLE-READ isolation level.

Explanation:

At the RR level, a snapshot is generated for the first read operation. For visibility, only modifications committed by other transactions and modifications made by themselves before the first read are visible. Others are invisible.

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html snapshot a representation of data at a particle time, which remains the same even as changes are committed by other transactions.

With Repeatable read isolation level, the snapshot is based on the time when the first read operation is completed MED.

Visibility Principle

Back to the preceding simulation scenario, session2 generates a snapshot in the SQL statement select description from user_conf where type_id = 100. Although session1 is submitted, it is still invisible, resulting in concurrent updates.

In addition, after the transaction is enabled, select... For update does not generate snapshots. you can experiment with them by yourself.

Solution

Solution 1

Change the REPEATABLE-READ isolation level to read-committed to see the latest committed data.

Solution 2

Add for update when reading 'account balance details' user _ account. force read the latest data of the row record, 2. if other transactions do not commit, the transaction will be blocked to ensure serial update.

Solution 3

Snapshots are generated in a delayed manner. After the transaction is started, the user table is used for mutex and the lock is applied directly for update. concurrent updates to multiple transactions are serialized.

Appendix: positioning process

For bug reporting users, query the transaction flow details and balance change details to confirm the account has problems.
Check whether there have been online changes in the accounting system in recent days.
Pull the MySQL General log of the Account Database and find the two transaction sessions for concurrent updates.
The isolation level set in the query database is RR. The application database connection pool configuration is queried, that is, the isolation level of the session is not configured. The database configuration is used.
Validation is caused by RR level (of course it can also be considered as caused by code problems)
It was confirmed that the account system went live one month ago and switched to another connection pool without setting the session isolation level. Previously, the isolation level of the configured session was read-committed.
Extended thinking

What are the applicable business scenarios at the MySQL RR level? If you are interested or have insights, you can leave a message to reply or send a private message ~~

On-board account system balance concurrent update problem records