Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "the concept of dirty reading, unrepeatable reading and phantom reading in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Dirty, unrepeatable, phantom reading

In the modern relational database, the transaction mechanism is very important. If multiple transactions operate the database concurrently, if there is no effective mechanism to avoid it, it will lead to dirty reading, unrepeatable reading and phantom reading.

Dirty reading

1. During the execution of transaction A, transaction A modifies the data resources, and transaction B reads the modified data of transaction A.

2. For some reason, transaction A does not complete the commit, and if a RollBack operation occurs, the data read by transaction B is dirty data.

This phenomenon of reading uncommitted data from another transaction is called Dirty Read.

Non-repeatable

Transaction B reads the data resources twice, and transaction A modifies the data during the two reads, resulting in the inconsistency of the data read by transaction B.

This phenomenon that the data read before and after two times are inconsistent in the same transaction is Nonrepeatable Read.

Illusory reading

Transaction B reads the same range of data twice, and transaction An adds data during the two reads of transaction B. as a result, transaction B reads rows that were not seen by the previous query.

Phantom reading is similar to unrepeatable reading, but phantom reading emphasizes the increase or decrease of sets rather than the update of individual pieces of data.

The first type of update is lost.

Both transaction An and transaction B update the data, but transaction A rolls back for some reason, overwriting the updated data of transaction B that has been committed. This phenomenon is the loss of the first type of update.

The second type of update is lost.

In fact, it is a bit similar to the loss of the first type of update, in which two transactions update the data at the same time, but the update of transaction An overwrites the update data of committed transaction B. This phenomenon is the loss of the second type of update.

Transaction isolation level

In order to solve the above problems, mainstream relational databases provide isolation levels for four transactions. Transaction isolation levels from low to high are: read uncommitted, read committed, repeatable read, serialization. The higher the transaction isolation level, the better the consistency and integrity of the data, but the lower the execution efficiency. So you need to make a tradeoff when setting the transaction isolation level of the database, and MySQL is a repeatable level by default.

Read unsubmitted

Read uncommitted (Read Uncommitted) is the lowest isolation level, and all transactions can see the execution results of other uncommitted transactions. It can only prevent the loss of the first type of updates, can not solve dirty reading, repeatable reading, phantom reading, so it is rarely applied to actual projects.

Read submitted

Read committed (Read Committed), at this isolation level, the update result of one transaction can only be read by another transaction after the transaction commits. It can prevent dirty reading and the loss of the first type of update, but it can not solve the problem of repeatable reading and phantom reading.

Repeatable read

Repeatable (Repeatable Read), the default isolation level for MySQL. At this isolation level, a transaction reads the same data multiple times, and other transactions cannot access the data (including reads and writes) before the transaction ends, so that the data can be read twice within the same transaction is the same. It can prevent dirty reading, non-repeatable reading, the loss of the first type of update and the loss of the second type of update, but there will still be phantom reading.

Serialization

Serializable, which is the highest isolation level. It requires transaction serialization execution, and transactions can only be executed one after another, not concurrently. At this level, all the concurrency issues mentioned above can be resolved, but it can lead to a lot of timeouts and lock contention, and this isolation level is not usually used.

This is the end of the introduction to the concept of dirty reading, unrepeatable reading and phantom reading in mysql. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.