MySQL has several isolation levels

Mysql has 4 isolation levels: 1. Read uncommitted content; in this isolation level, all transactions can see the execution results of other uncommitted transactions. 2. Read the submission content; only after a transaction is submitted, the data it modifies will be seen by other things. 3. Repeatable reading; the data seen during the execution of a transaction is always consistent with the data seen when the transaction is started. 4. Serializable; by forcing transactions to be sorted, making it impossible for them to conflict with each other, thereby solving the phantom read problem.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

A transaction has ACID characteristics, namely (Atomicity, Consistency, Isolation, Durability, that is, atomicity, consistency, isolation, and durability).

Mysql’s four isolation levels

SQL standard defines 4 types of isolation levels, including some specific rules to limit the internal and external transactions Which changes are visible and which are invisible. Lower isolation levels generally support higher concurrency and have lower system overhead. [Video tutorial recommendation: Mysql tutorial]

1. Read uncommitted content (read-uncommitted)

In this isolation level, all Transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels.

The problem that occurs with this isolation level is: Dirty Read, that is, uncommitted data is read.

2. Read-committed content (read-committed)

This is the default isolation level of most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions.

The problem with this isolation level is: Nonrepeatable Read, that is, nonrepeatable read means that we may see different results when we execute the exact same select statement in the same transaction.

The reasons for this situation may be:

1). There is a new commit in a cross transaction, resulting in data changes;

2). When the database is operated by multiple instances, other instances of the same transaction may have new commits while the instance is processing

3. Repeatable-read

This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently.

But in theory, this will lead to another thorny problem: Phantom Read.

Simply put, phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range, You will find that there are new "phantom" rows.

InnoDB and Falcon storage engines solve this problem through the multiversion concurrency control (MVCC, Multiversion Concurrency Control) mechanism.

4. Serializable

This is the highest isolation level. It solves the illusion by forcing transactions to be ordered so that they cannot conflict with each other. Read the question. In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

At this level, it may lead to a large number of timeouts and lock competition.

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL has several isolation levels. For more information, please follow other related articles on the PHP Chinese website!