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

This article mainly introduces "A brief introduction of the four isolation levels of MySQL". In daily operation, I believe many people have doubts about the four isolation levels of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "briefly introducing the four isolation levels of MySQL". Next, please follow the editor to study!

What is a transaction?

A transaction is a series of rigorous operations in an application, and all operations must be completed successfully, otherwise all changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction are either successful or none at all.

There are two ways to end a transaction, and when all the steps in the transaction are executed successfully, the transaction commits. If one of the steps fails, a rollback operation occurs, undoing all actions from the previous to the beginning of the transaction.

ACID of the transaction

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

Atomicity. Transaction is the logical working unit of the database, and all the operations contained in the transaction are either done or not done.

Consistency. The result of transaction execution must be to change the database from one consistency state to another. So when the database contains only the results of a successful transaction commit, the database is said to be in a consistent state. If a failure occurs in the operation of the database system, some transactions are forced to be interrupted before they are completed, and some of the modifications made by these outstanding transactions to the database have been written to the physical database, and the database is in an incorrect state. Or an inconsistent state.

Isolation. The execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Continuity. Also known as permanent, refers to a transaction once committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not have any impact on the results of their execution.

Four isolation levels of Mysql

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read uncommitted)

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 submission)

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.

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

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, while another transaction inserts several new columns of data at this time. In the previous transaction, several columns of data are not queried in the following query. If the data inserted by another transaction and another transaction are inserted at this time, an error will be reported.

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

Test the isolation level of Mysql

Next, using MySQL's client program, let's test these isolation levels separately.

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

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

Set the isolation level of A to read uncommitted (read not submitted)

A: start the transaction when the data is in the initial state

B: start the transaction, update the data, but not commit

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

B: roll back the transaction

A: read the data again and find that the data has changed back to its original state

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.

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

A: start the transaction when the data is in the initial state

B: start the transaction, update the data, but not commit

A: read the data again and found that the data has not been modified

B: commit transaction

A: read the data again and find that the data has changed, indicating that the changes committed by B have been read by An in the transaction. This is the so-called "non-repeatable read".

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.

Set the isolation level of A to repeatable read (repeatable)

A: start the transaction when the data is in the initial state

B: start the transaction, update the data, but not commit

A: read the data again and found that the data has not been modified

B: commit transaction

A: read the data again and found that the data has not changed, which means it can be read again this time.

B: insert a new piece of data and submit

A: when I read the data again, I found that the data still remained unchanged. Although I could read it repeatedly, I found that it was not the latest data. This is the so-called "phantom reading".

A: commit this transaction, read the data again, and find that the reading is normal.

It can be concluded from the above experiments that the repeatable isolation level only allows reading of committed records, and that other ministries update a record during a transaction reading a record twice. However, this transaction does not require serialization with other transactions. For example, when a transaction can find a record updated by a committed transaction, it may cause phantom reading problems (note that it is possible because the database has a different implementation of the isolation level). Like the above experiment, there is no problem of data phantom reading.

Set the isolation level of A to serializable (Serializable)

A: start the transaction when the data is in the initial state

B: it is found that B has entered the waiting state at this time, because the transaction of A has not yet been committed and can only wait (at this time, B may have a wait timeout)

A: commit transaction

B: found that the insertion was successful

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.

At this point, the study of "A brief introduction to the four isolation levels of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.