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

The following brings you about what are the four isolation levels of Mysql database. If you are interested, let's take a look at this article. I believe it will be helpful for you to read what the four isolation levels of Mysql database are.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL CVM works in client / CVM mode, or in embedded system

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

II. SQL standard defines four types of isolation levels.

2.1. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read unsubmitted)

At 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. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submit)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

2.2. These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. Such as:

Dirty reading (Drity Read): one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable read (Non-repeatable read): data inconsistencies between two queries of a transaction, which may be due to the insertion of original data updated by a transaction between the two queries.

Phantom Read: the number of data pens is inconsistent in two queries of a transaction, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find several columns of data that it did not have before in the next query.

2.3. In MySQL, these four isolation levels are implemented, each of which may cause problems as follows:

Mysql > SHOW GLOBAL VARIABLES LIKE'% iso%'; # to see the isolation level, or select @ @ tx_isolation

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ | # MySQL is rereadable by default |

+-+ +

1 row in set (0.00 sec)

III. Isolation level testing

The test database is test and the table is tx; table structure:

Idint

Num

Int

The two command line clients are Ameme B; constantly change the isolation level of A, and modify the data on the B side.

3.1. Set the isolation level of A to read uncommitted (read uncommitted)

Mysql > SET tx_isolation='READ-UNCOMMITTED'

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

After the above experiment, it can be concluded that transaction B updates a record but does not commit, and transaction A can query out the uncommitted record. Cause dirty reading. Uncommitted reads are the lowest isolation level.

3.2. Set the transaction isolation level of client A to read committed (read commit)

Mysql > SET tx_isolation='READ-COMMITTED'

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

Through the above experiments, it can be concluded that the committed read isolation level solves the problem of dirty reading, but there is a problem of unrepeatable reading, that is, transaction A has inconsistent data in two queries, because transaction B updates a piece of data between two queries. Committed reads allow only committed records to be read, but do not require repeatable readings.

3.3. Set the transaction isolation level of client A to repeatable read (readable)

Mysql > SET tx_isolation='REPEATABLE-READ'

Before B updates the data:

Client A:

B update data:

Client B:

Client A:

B insert data:

Client B:

Client A:

From the above experiments, it can be concluded that the repeatable read isolation level only allows the client to read the committed record, and the data updated and committed by other clients can only be seen after the transaction of the client has been committed. This will also cause illusion, because the data read by the client before and after the transaction commit may be different. if other clients update the data and commit the transaction, the current client has not done anything. It is just that the content read before and after the transaction commit is inconsistent, resulting in illusory reading.

3.4. Set the transaction isolation level of client A to Serializable (serializable)

Mysql > SET tx_isolation='SERIALIZABLEA'

The A side opens the transaction and the B side inserts a record

Side An of the transaction:

B side of the transaction:

Because transaction A's isolation level is set to serializable at this time, transaction B can only wait because it is not committed after starting the transaction.

Transaction A commits the transaction:

Transaction A side

Transaction B side

Serializable completely locks the field, and if a transaction queries the same data, it must wait until the previous transaction is completed and unlocked. Is a complete isolation level that locks the corresponding data table, thus causing efficiency problems.

Read the above about what is the four types of isolation levels of Mysql database details, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

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.