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

This article shows you what transaction isolation levels are in MySQL, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Four ACID attributes of a transaction: Atomicity atomicity, Consistency consistency, Isolation isolation, and Durability persistence.

Atomicity: the transaction is the smallest execution unit that is indivisible, emphasizing the indivisibility of the transaction.

Consistency: data integrity is consistent before and after the transaction is executed.

Persistence: once the transaction execution is complete, the data is saved to the database.

Isolation: the execution of one transaction should not be disturbed by other transactions.

MySQL has four levels of transaction isolation: uncommitted reads, committed reads, repeatable reads, and serialization.

Uncommitted read: transaction A can read data that has not been committed by transaction B. For example, transaction B changes Xiaoming's account balance from 100 to 110, and transaction B has not yet committed. Under this isolation level, transaction A reads 110 data. If transaction B rolls back, Xiaoming's account balance in the database becomes 100 again, but transaction A returns 110 data. This leads to dirty reading.

Committed read: transaction A reads the data after transaction B commits, that is, after one transaction commits, its changes will be read by another transaction. For example, transaction B changes Xiaoming's account balance from 100 to 110. Before transaction B commits, the data read by transaction An is still 100. Because at this isolation level, one transaction can only read data after another transaction has been modified and committed. But this situation will cause a problem, that is, transaction A reads 110 after transaction B implicitly commits data, which is no problem, but before transaction A commits, transaction B changes Xiaoming's account balance to 120 and implicitly commits, and then transaction A reads 120 when transaction A reads it again, so the two reading results of transaction An are inconsistent. This makes it unrepeatable.

Repeatable: transaction A can only read the committed data modified by transaction B, but the data read for the first time, even if this value is modified by another transaction, is the same as that obtained for the first time and will not change with the modification of other transactions. The difference between this and a committed read is that the value it reads repeatedly is the same. So it's called repeatable, apt name. Take the example just now, when transaction A reads Xiaoming's account balance many times in its own transaction, if the first read is 100, then no matter how transaction B modifies it after that, for transaction A, the result of multiple reads is 100. Repeatable reading solves the problem of non-repeatable reading (which sounds like nonsense), but there are cases of phantom reading.

Illusion: when transaction A queries the number of customers with account balance = 100, if 10,000 is returned, transaction B changes Xiaoming's account balance from 110 to 100 and commits the transaction. When transaction A queries the number of customers with account balance = 100 again, the statistical number returned becomes 101. This is phantom reading.

There is a difference between phantom reading and unrepeatable reading. The focus of unrepeatable reading is to read the same piece of data, and the data is modified, such as reading Xiaoming's account balance; the focus of phantom reading is on adding or deleting (the number of data items changes). Under the same conditions, the number of records is different between the two queries.

Serialization: the above three isolation levels can read and write the same record concurrently, but only read-read concurrency can be performed at the serialized isolation level. As long as one transaction operates on the writing of a record, all other transactions that want to read the record have to wait. For example, transaction B modifies Xiaoming's account before transaction An is committed, then transaction A can only wait until transaction B commits.

Half of the serialized isolation levels are unused because of low performance, and committed and repeatable reads are commonly used.

Most databases use committed reads, and the default isolation level for MySQ is: repeatable reads.

You can view the current isolation level at select @ @ global.tx_isolation;.

✘ does not cause ✔ to cause isolation levels.

Dirty read not repeatable uncommitted read ✔✔✔ committed read ✘✔✔ repeatable readable ✘✘✔ serialization ✘✘✘ the above are the transaction isolation levels in MySQL, have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.