In-depth understanding of four isolation levels of Mysql and four mysql

In-depth understanding of four isolation levels of Mysql and four mysql

1. What is a transaction first?

A transaction is a series of strict operations in an application. All operations must be completed successfully. Otherwise, all changes made in each operation will be revoked. That is to say, a transaction is atomic, and a series of operations in a transaction either succeed or do not.

There are two types of transactions to end. When all the steps in the transaction are successfully executed, the transaction is committed. If one of the steps fails, the rollback operation will occur, undo the operation that was performed before the start of the transaction.

Ii. ACID of transactions

Transactions have four features: Atomicity, Consistency, Isolation, and Durability ). These four features are short for ACID.

1. atomicity. A transaction is the logical unit of work of a database. All operations contained in a transaction are either done or not done.

2. consistency. The result of event execution must be that the database changes from one consistent state to another consistent state. Therefore, when the database only contains the results of successful transaction commit, the database is in a consistent state. If a fault occurs in the database system, some transactions are forced to be interrupted before they are completed. Some of the changes made to the database by these unfinished transactions have been written to the physical database, the database is in an incorrect or inconsistent state.

3. Isolation. The execution of a transaction cannot be disturbed by other transactions. That is to say, the operations and data used within a transaction are isolated from other concurrent transactions, and each transaction that is executed concurrently cannot interfere with each other.

4. continuity. It is also called permanent, which means that once a transaction is committed, its changes to the data in the database should be permanent. Other subsequent operations or faults should not affect the execution results.

Iii. Four isolation levels of Mysql

The SQL standard defines four isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Low-level Isolation generally supports higher concurrent processing and lower system overhead.

Read Uncommitted (Read Uncommitted content)

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 no better than other levels. Read uncommitted data, also known as Dirty Read ).

Read Committed (Read submitted content)

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

Repeatable Read (repeable)

This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this will lead to another tricky problem: Phantom Read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. When the user reads data in this range, there will be a new Phantom line. The InnoDB and Falcon storage engines solve this problem through the Multi-version Concurrency Control (MVCC, Multiversion Concurrency Control) mechanism.

Serializable (Serializable)

This is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.

The four isolation levels adopt different lock types. If the same data is read, problems may occur. For example:

Dirty Read: a transaction has updated the data, and another transaction has Read the same data at this time. For some reason, the previous RollBack operation is performed, the data read by the other transaction is incorrect.

Non-repeatable read: the data in the two queries of a transaction is inconsistent. This may be because the original data updated by a transaction is inserted in the two queries.

Phantom Read: the number of data records in two queries of a transaction is inconsistent. For example, a transaction queries several rows of data, another transaction inserts several new columns of data at this time. In the subsequent query, the previous transaction will find that several columns of data are not at the beginning.
In MySQL, these four isolation levels are implemented, which may cause the following problems:

Iv. Test the isolation level of Mysql

Next, we will use the MySQL client program to test these isolation levels.

The test database is demo and the table is test. The table structure is as follows:

The two command line clients are A and B respectively. The isolation level of A is constantly changed and data is modified on the B side.

(1) set the isolation level of A to read uncommitted (uncommitted read)

A: Start the transaction. The data is in the initial state.

B: Start the transaction, update the data, but do not submit

A: Read the data again and find that the data has been modified. This is the so-called "Dirty read"

B: roll back the transaction.

A: Read the data again and find that the data changes back to the initial state.

After the experiment above, we can conclude that transaction B updates A record but does not commit it. At this time, transaction A can query uncommitted records. This causes dirty reading. Uncommitted read is the lowest isolation level.

(2) set the transaction isolation level of client A To read committed (committed read)

A: Start the transaction. The data is in the initial state.

B: Start the transaction, update the data, but do not submit

A: Read the data again and find that the data has not been modified.

B: Submit the transaction.

A: Read the data again and find that the data has changed. This indicates that the modification submitted by B has been read by A in the transaction. This is the so-called "non-repeated read"

After the above experiment, we can conclude that the committed read isolation level solves the dirty read problem, but the problem of non-repeated read occurs, that is, transaction A's data in the two queries is inconsistent, because transaction B updates A piece of data between the two queries. Committed read: only submitted records can be read, but repeat is not required.

(3) set the isolation level of A to repeatable read (repeatable read)

A: Start the transaction. The data is in the initial state.

B: Start the transaction, update the data, but do not submit

A: Read the data again and find that the data has not been modified.

B: Submit the transaction.

A: Read the data again and find that the data remains unchanged. This indicates that the data can be read again.

B: Insert a new data record and submit it.

A: When I read the data again, I find that the data remains unchanged. Although I can read it again, I find that the read data is not the latest. This is the so-called "Phantom read"

A: Submit the transaction and read the data again. The read operation is normal.

From the above experiments, we can conclude that the Repeatable read isolation level only allows reading committed records, and that during the two reads of one record by one transaction, other departments will update this record. However, this transaction does not require serializability with other transactions. For example, when a transaction can find a record updated by a committed transaction, it may cause phantom read problems (note that it is possible because the database has different isolation levels ). As in the above experiments, there is no phantom data reading problem.

(4) set the isolation level of A to Serializable)

A: Start the transaction. The data is in the initial state.

B: It is found that B enters the waiting state at this time because A's transaction has not been committed and can only wait (at this time, B may wait for timeout)

A: Submit the transaction.

B: insertion successful

Serializable completely locks the field. If a transaction is used to query the same data, it must wait until the previous transaction is completed and unlocked. Is a complete isolation level, it will lock the corresponding data table, so it will be efficient.

Summary

The above is all the content of this article. I hope the content of this article can be helpful for your learning. If you have any questions, please leave a message.