Four isolation levels in MySQL transactions-Mysql Tutorial-php.cn

1. Four major characteristics of transactions

Atomicity: After the transaction starts, all operations are either completed or not completed, and it is impossible to stagnate in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction started, and all operations will be as if they did not happen.

Consistency: Before and after the transaction starts and ends, the integrity constraints of the database are not violated.

Isolation: Only one transaction is allowed to request the same data at the same time, and there is no interference between different transactions.

Durability (Durability): After the transaction is completed, all updates to the database by the transaction will be saved to the database and cannot be rolled back.

2. Transaction concurrency issues

Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation, then A reads The data is dirty data

Non-repeatable reading: (Transaction A reads the same data multiple times, and transaction B updates and commits the data during the multiple reads of transaction A, causing transaction A to read multiple times. When reading the same data, the results are inconsistent.) (Focus: Data modification)

Phantom reading: System administrator A changed the grades of all students in the database from specific scores to ABCDE grades, but system administrator B changed At this time, a record with a specific score was inserted. When the system administrator A completed the change, he found that there was still one record that had not been changed. It was like an hallucination. This is called phantom reading. (Focus: Adding or deleting data)

Summary: It is easy to confuse non-repeatable reading and phantom reading. Non-repeatable reading focuses on modification, and phantom reading focuses on adding or deleting. To solve the problem of non-repeatable reading, you only need to lock the rows that meet the conditions. To solve the problem of phantom reading, you need to lock the table

3. MySQL transaction isolation level

Uncommitted read (READ UNCOMMITED) (Uncommitted data can be read, dirty reading, these data are called dirty data)

READ COMMITED (Committed data can be read, non-repeatable reading)

RePEATABLE READ) The data seen during the execution of a transaction is always consistent with the data seen when the transaction is started. Of course, under the repeatable read isolation level, uncommitted changes are also invisible to other transactions.

Serializable (SERIALIZABLE) (Read operations will implicitly acquire shared locks, which can ensure mutual exclusion between different transactions. Don’t think about locking tables. The shortcomings are also obvious. See the locking tables. You will understand, efficiency issues)