MySQL (26): The isolation level of a transaction has a phantom read of the problem

1. Phantom reading

Phantom Reading (Phantom read), also known as virtual reading, refers to the number of data bars in a transaction within two queries, the Phantom reads and does not repeat some types, the same is in the two query process, the difference is that the Phantom reading is due to other transactions to do the operation of inserting records, resulting in an increase in the number of records.

For example, when the bank is doing statistical statements to count the total amount of all users in the Account table, there are altogether three accounts, a total amount of 3000 yuan, when a new user account is added, and deposited 1000 yuan, this time the bank again statistics will find the total account amount of 4000, resulting in phantom reading situation

2. Demo Phantom Read:

(1) First we set the isolation level of the B account

B Account: Because the transaction isolation level is set to repeatable read earlier, this isolation level avoids the appearance of Phantom reads, so you need to set the isolation level of the transaction to lower, and the isolation level of the transaction is set to

Read Committed, with the following specific statements:

Set Transaction Isolation  Level Read committed;

After execution of the above statement, the isolation level for the B account transaction is read Committed

(2) Demo Phantom Read:

B Account: First open a transaction in the B account, and then in the current transaction to query the account balance information, the query results are as follows:

A account: Before adding an account to a, use the SELECT statement to view the information in the current a account and execute the following results:

Next to the a account to add operations, a account does not have to open the transaction, directly perform the add operation, the specific statement is as follows:

Insert  into account (name,moneyvalues('C',1000 );

The results of the implementation are as follows:

B Account: When a account added record is completed, you can re-check the balance information in the B account, as follows:

Before and after setting the Read Committed isolation level for the B account, it is not wrong to find that the second query data is one more record than the first query data, but it may not be in line with the actual requirements. It is important to note that when the above scenario is complete, the transactions in the B account are submitted.

3. Problem solving:

(1) Reset the isolation level of the B account

B Account: To prevent Phantom reads, you can set the isolation level of the B account to repeatable read, with the following specific statements:

Set Transaction Isolation  Level Repeatable read;

After the above statement has been executed, the transaction isolation level is set to repeatable Read

MySQL (26): The isolation level of a transaction has a phantom read of the problem