What is the isolation level of MySQL transactions?

1. The concept of isolation level

Why should we consider isolation level?

Because transactions must be concurrently executed, and concurrent execution may cause some problems: dirty reads, non-repeatable reads, and virtual reads, some are allowed to occur, and some are not allowed to occur. , There are different isolation levels for this different degree of concurrency control that appears or does not occur.

The four isolation levels supported by MySQL are:

• TRANSACTION_READ_UNCOMMITTED: Uncommitted read. It means that transaction A can see the changes of transaction B before committing. In this way, dirty data is read, and non-repeatable reads and phantom reads are allowed.

• TRANSACTION_READ_COMMITTED: Committed read (oracle default), indicating that reading uncommitted data is not allowed (to prevent dirty reads). Non-repeatable reads and phantom reads are still allowed to occur at this level.

• TRANSACTION_REPEATABLE_READ: Repeatable Read (MySQL default), indicating that the transaction is guaranteed to be able to read the same data again without failure, even if other transactions change this data Even if you change it, you will not see the difference in the data of the two queries before and after. But phantom reading still occurs.

• TRANSACTION_SERIALIZABLE: Serialization, is the highest transaction isolation level, which prevents dirty reads, non-repeatable reads and phantom reads. Serial execution is equivalent to a single-threaded operation, the lowest concurrency capability.

Note:

The higher the transaction isolation level, the less performance it takes to avoid conflicts. The more, the lower the efficiency. At the "repeatable read" level, it can actually solve part of the virtual read problem, but it cannot prevent the virtual read problem caused by update updates. To prohibit the occurrence of virtual reads, you still need to set the serialization isolation level.

MySQL client works at the repeatable read level by default:

## 2. Test the TRANSACTION_READ_UNCOMMITTED isolation level

If client A rolls back at this time, zhangsan’s age in the database is restored to 20. At this time, it is too late, because client B has already taken 21. Doing business.

Both clients rollback and abandon the modifications to the data made by the current transaction, and zhangsan’s age is restored to 20

3. Test the TRANSACTION_READ_COMMITTED isolation level

## Because the committed read isolation level is set, no dirty reading occurred in transaction B. This It is implemented by various lock mechanisms and MVCC version control of transaction concurrency.

Under the read committed isolation level, querying submitted data may cause non-repeatable reads to occur, which is allowed. Since non-repeatable reads have occurred, phantom reads can definitely occur.

4. Test TRANSACTION_REPEATABLE_READ isolation level

##In a certain sense, repeatable reading can avoid phantom reading Appear. Because the current repeatable read isolation level prevents insert operations. Although the repeatable read isolation level can prevent insert and delete operations, it cannot prevent update operations.

In fact, transaction A has been inserted and committed, aaa already exists, because transaction B update aaa's age is successful

When the same query is before and after When executed twice, if the amount of data is different, phantom reading will occur. To completely solve the phantom read problem, it cannot be achieved under the repeatable read isolation level. The isolation level must be raised to serialization

5. Test the TRANSACTION_SERIALIZABLE isolation level

Judging from the phenomenon, serialization can solve phantom reading. Querying under the same conditions will be blocked when inserting data into another table. Since transaction B is reading data, this When transaction A writes data again, it is blocked (implemented with a read-write lock, which allows reading and reading, but does not allow reading and writing or writing and writing)

MySQL server will not let its own transaction thread be blocked forever, resulting in the current The lock occupied by the thread cannot be released, and other threads executing transactions cannot obtain the lock and are blocked forever. If the thread executing the transaction waits too long, the timeout mechanism will be triggered, causing the thread to release the lock and return an error

The above is the detailed content of What is the isolation level of MySQL transactions?. For more information, please follow other related articles on the PHP Chinese website!