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

This article will explain in detail what will happen if you do not consider transaction isolation in the database, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

If you do not consider the isolation of transactions in MySQL, the following problems can occur:

1) dirty reading

Refers to reading data from another uncommitted transaction during one transaction.

When a transaction is modifying a data many times, and the many modifications in the transaction have not been committed, then a concurrent transaction to access the data will cause the data obtained by the two transactions to be inconsistent.

For example:

User A transfers RMB 100 to user B, corresponding to the SQL command as follows:

Updateaccountsetmoney=money100wherename='B'; (A notifies B at this time)

Updateaccountsetmoney=money-100wherename='A'

When only the first SQL is executed, An informs B to check the account, and B finds that the real money has arrived in the account (dirty reading occurs at this time), and then, regardless of whether the second SQL is executed or not, as long as the transaction is not committed, all operations will be rolled back, so when B checks the account again later, it will find that the money has not actually been transferred.

2) non-repeatable

For a data in the database, multiple queries within a transaction range return different data values, because it is modified and committed by another transaction at the time of the query.

For example:

Transaction T1 is reading a certain data, and transaction T2 immediately modifies the data and commits the transaction to the database. Transaction T1 reads the data again and gets a different result and sends an unrepeatable read.

The difference between unrepeatable reading and dirty reading is that dirty reading is that one transaction reads dirty data not committed by another transaction, while unrepeatable reading reads data committed by the previous transaction.

In some cases, unrepeatable reading is not a problem, for example, we query a data many times, of course, mainly based on the results of the final query. However, in other cases, problems may occur, for example, queries for the same data An and B may be different, and An and B may fight.

For example, the train ticket system: at the same time, someone may buy a ticket, or someone may be refunding the ticket. The person who buys the ticket may read the data read some time ago. If someone refunds or buys the ticket during this period of time, the data read by the user is not accurate, and the user may indicate that there is no ticket when placing an order. The data is different when querying again.

3) false reading (phantom reading)

Illusory reading is a phenomenon that occurs when a transaction is not executed independently.

For example:

Transaction T1 modifies a data item of all rows in a table from "1" to "2", and transaction T2 inserts a row of data items into the table, and the value of this data item is still "1" and submitted to the database.

On the other hand, if the user who operates transaction T1 looks at the data that has just been modified, he will find that there is still one line that has not been modified. In fact, this line is added from transaction T2, which is like an illusion, which is a hallucination.

About how it will be shared here without considering transaction isolation in the database, I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.