Shulou(Shulou.com)05/31 Report--

What are the MySQL transaction isolation levels? in view of this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Dirty reading

Dirty reading refers to reading uncommitted data from other transactions, which means that the data may be rolled back, that is, data that may not eventually be stored in the database, that is, data that does not exist. The data that has been read and must eventually exist is dirty reading.

Repeatable read

Repeatable readability means that within a transaction, the data read at the beginning is consistent with the same batch of data read at any time before the end of the transaction. It is usually targeted at data * * update (UPDATE) * * operations.

Non-repeatable

Compared with repeatable readings, unrepeatable readings mean that the same batch of data read at different times within the same transaction may be different and may be affected by other transactions, such as other transactions changing the data and submitting it. It is usually targeted at data * * update (UPDATE) * * operations.

Illusory reading

Phantom reading is for data INSERT operations. Assuming that transaction A has changed the contents of some rows, but has not yet committed, transaction B inserts the same record row as the record before transaction A changes, and commits before transaction A commits. At this time, when querying in transaction A, you will find that the change seems to have no effect on some data, but in fact, transaction B has just been inserted, which makes the user feel magical and illusory. It's called hallucination.

Transaction isolation level

The SQL standard defines four isolation levels, all of which are supported by MySQL. The four isolation levels are:

Read unsubmitted (READ UNCOMMITTED)

Read submit (READ COMMITTED)

Repeatable read (REPEATABLE READ)

Serialization (SERIALIZABLE)

From top to bottom, the isolation strength increases gradually, and the performance becomes worse. Which isolation level to adopt is weighed according to system requirements, where repeatable readability is the default level for MySQL.

In fact, transaction isolation is to solve the problems mentioned above, such as dirty reading, unrepeatable reading and phantom reading. The following shows the degree to which these three problems are solved by four isolation levels.

Only serialized isolation levels solve all three problems, and the other three isolation levels are flawed.

Find out what happened.

Next, let's analyze the meaning of these four isolation levels one by one.

How to set the isolation level

We can see the isolation level of the current database through the following statement. From the following statement, we can see that the isolation level of the MySQL I use is REPEATABLE-READ, which is repeatable, which is the default level of MySQL.

# View show variables like 'transaction_isolation' after transaction isolation level 5.7.20 SELECT @ @ transaction_isolation # 5.7.20 after SELECT @ @ tx_isolation show variables like 'tx_isolation' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | | +-+ |

Later, we will change the isolation level of the database, so let's first learn how to modify it.

The statement to modify the isolation level is: set [scope] transaction isolation level [transaction isolation level], SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ COMMITTED | REPEATABLE READ | SERIALIZABLE.

Where the effect can be SESSION or GLOBAL,GLOBAL is global, while SESSION is only for the current reply window. The isolation level is READ UNCOMMITTED READ COMMITTED | REPEATABLE READ | SERIALIZABLE, which is case-insensitive.

For example, the following statement means to set the global isolation level to the read commit level.

Execute transactions in mysql > set global transaction isolation level read committed;MySQL

The execution process of the transaction is as follows, starting with begin or start transaction, then performing a series of operations, and finally performing a commit operation before the transaction ends. Of course, if a rollback operation (rollback) is performed, the transaction will also end.

It is important to note that the begin command does not represent the beginning of the transaction, which begins when the first statement after the begin command is executed. For example, in the following example, select * from xxx is the beginning of the transaction

Begin; select * from xxx; commit;-- or rollback

In addition, you can query how many transactions are currently running with the following statement.

Select * from information_schema.innodb_trx

All right, here's the point. let's analyze these isolation levels.

Next, I will use a table to verify it. The structure of the table is as follows:

CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (30) DEFAULT NULL, `age` tinyint (4) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Initially, there was only one record:

Mysql > SELECT * FROM user; +-+ | id | name | age | +-+ | 1 | Ancient kites | 1 | +-+ read unsubmitted

MySQL transaction isolation is actually achieved by locking, and locking will naturally lead to performance loss. The read uncommitted isolation level is unlocked, so its performance is the best, without the performance overhead of locking and unlocking. But there are advantages and disadvantages, which is basically equivalent to streaking, so it can not even solve the problem of dirty reading.

Any changes made by a transaction to the data are immediately exposed to other transactions, even if the transaction has not been committed.

Let's do a simple experiment to verify that, first, set the global isolation level to read uncommitted.

Set global transaction isolation level read uncommitted

After the setting is complete, it only works for the newly created session, but not for the session that has already been started. If you use the shell client, you need to reconnect to MySQL, and if you use Navicat, you need to create a new query window.

Start two transactions, transaction An and transaction B, use the update statement in transaction A, modify the value of age to 10, initially 1, after the execution of the update statement, query the user table in transaction B, you will see that the value of age is 10, at this time transaction A has not been committed, and transaction B may take the modified age=10 to do other operations. During the operation of transaction B, it is very possible that transaction A has carried out a transaction rollback operation for some reason. In fact, transaction B gets dirty data and carries out other calculations with dirty data. There must be something wrong with the result.

The sequence of operations in the two transactions is indicated along the timeline, focusing on the value of the age field in the figure.

Reading uncommitted means that you can read uncommitted data from other transactions, but there is no way to guarantee that the data you read will eventually be submitted. If a rollback occurs, there will be a dirty data problem. Reading uncommitted cannot solve the dirty data problem. Not to mention repeatable and illusory reading, don't even think about it.

Read submit

Since reading uncommitted does not solve the problem of dirty data, there is a read commit. Read commit means that a transaction can only read the data that has been committed by other transactions, that is, the data after other transactions call the commit command. The problem of dirty data was easily solved.

The read commit transaction isolation level is the default transaction isolation level for most popular databases, such as Oracle, but not for MySQL.

Let's continue to do some verification, first changing the transaction isolation level to the read commit level.

Set global transaction isolation level read committed

After that, you need to reopen a new session window, that is, a new shell window.

Also open transaction An and transaction B, and use the update statement in transaction A to change the age field of the record row of id=1 to 10. At this point, when we use the select statement in transaction B to query, we find that before transaction A commits, the record age queried in transaction B is always 1 until transaction A commits. At this time, select query in transaction B finds that the value of age is already 10.

This gives rise to a problem: in the same transaction (transaction B in this example), the query records are different under the same query conditions at different times of the transaction, and the commit of transaction An affects the query results of transaction B. this is non-repeatable read, that is, read commit isolation level.

Each select statement has its own snapshot, not a transaction, so the data queried may be inconsistent at different times.

Read submission solves the problem of dirty reading, but it can not be read repeatedly, nor can it solve the problem of phantom reading.

Repeatable read

Repeatability is relative to non-repeatability, which means that the data values read at different times of the same thing may be inconsistent. Repeatable reading means that the transaction will not read the changes made by other transactions to the existing data, and when other transactions have committed, that is, what the existing data read at the beginning of the transaction is, at any time before the transaction commits, the values of these data are all the same. However, newly inserted data for other transactions can be read, which leads to the problem of phantom reading.

Similarly, you need to change the global isolation level to a repeatable readable level.

Set global transaction isolation level repeatable read

At this isolation level, two transactions are started and both transactions are started at the same time.

First of all, let's take a look at the effect of repeatable reading. transaction A modifies the data after starting and commits before transaction B. transaction B reads the same data at both time nodes after transaction start and transaction A commits. You can already see the effect of repeatable reading.

Repeatable reading is done, which is only valid for changes to existing rows, but not so lucky for newly inserted row records, and phantom reading occurs. Let's look at this process:

After transaction A starts, execute the update operation, changing the name of the record with age = 1 to "kite 2"

After transaction B starts, after the transaction finishes update, it performs the insert operation, inserts the record age = 1 MagneName = the ancient kite, which is the same as the record value modified by transaction A, and then commits.

After transaction B commits, transaction An executes select to query the data of age=1. At this time, you will find that there is an extra row, and you will find that there is also a name = ancient kite, a record of age=1, which is actually what transaction B has just inserted. This is phantom reading.

To be clear, when you test phantom reading in MySQL, the result shown above does not appear. Phantom reading does not occur. The repeatable isolation level of MySQL actually solves the phantom reading problem, which will be explained later.

Serialization

Serialization has the best isolation effect among the four transaction isolation levels, and solves the problems of dirty reading, repeatable reading and phantom reading, but the effect is the worst. It turns the execution of transactions into sequential execution, compared with the other three isolation levels. It is equivalent to a single thread, and the execution of the latter transaction must wait for the completion of the previous transaction.

How to realize transaction isolation in MySQL

First of all, read uncommitted, it is the best performance, it can also be said that it is the most savage way, because it is not locked at all, so there is no isolation effect at all, it can be understood that there is no isolation.

Let's talk about serialization. Add a shared lock when reading, that is, other transactions can be read at the same time, but not written. Add an exclusive lock when writing, and other transactions cannot be written or read at the same time.

Finally, read submitted and repeatable. These two isolation levels are relatively complex, which not only allow a certain degree of concurrency, but also want to solve the problem at the same time.

Achieve repeatable readability

To solve the problem of non-repeatable readability, or to achieve repeatable readability, MySQL adopts the MVVC (multi-version concurrency control) approach.

The row of records we see in the database table may actually have multiple versions, each version of the record in addition to the data itself, but also has a field that represents the version, marked as row trx_id, and this field is the id of the transaction generated by it, and the transaction ID is marked as transaction id, which is applied to the transaction system at the beginning of the transaction, increasing in chronological order.

According to the above figure, there are now three versions of a row of records, each of which records the transaction ID generated by it. For example, if the transaction id of transaction An is 100, then the row trx_id of version 1 is 100. similarly, version 2 and version 3.

In the above introduction to read commit and repeatable reading, a word is mentioned, which is called snapshot, and the scientific name is consistency view, which is also the key to repeatable and non-repeatable reading. repeatable reading generates a snapshot of the overall situation of the current transaction at the beginning of the transaction, while read commit regenerates the snapshot each time the statement is executed.

For a snapshot, it can read those versions of the data, following the following rules:

Updates within the current transaction, which can be read

The version has not been submitted and cannot be read

A version that has been submitted but submitted after the snapshot was created cannot be read

The version has been submitted, and was submitted before the snapshot was created, and can be read

Using the above rules, it is clear to go back and apply them to the two pictures that are read-submitted and repeatable. Again, the main difference between the two is that in snapshot creation, repeatable reads are created only once at the beginning of the transaction, while read commits are recreated each time the statement is executed.

Concurrent writing problem

In the case of this, two transactions modify the same piece of data. The final result should be the result of which transaction, certainly if the time is later, whether it is right or not. And read the data before updating. The reading mentioned here is different from the reading mentioned above. The read before the update is called "current read", which is always the data of the current version, that is, the latest submitted version of the multiple versions.

Suppose transaction A performs a update operation, and update adds a row lock on the modified row, which is not released until it is committed. Before transaction A commits, transaction B also wants to update this row of data, so it applies for a row lock, but because it has been occupied by transaction A, transaction B cannot be applied for. At this time, transaction B will be in a waiting state until transaction A commits, transaction B can continue to execute. If transaction A takes too long, transaction B is likely to have a timeout exception. This is shown in the following figure.

The locking process can be divided into indexed and non-indexed cases, such as the following statement

Update user set age=11 where id = 1

Id is the primary key of the table, and there is an index, so MySQL finds the row of data directly in the index, and then neatly adds a row lock.

And the following sentence

Update user set age=11 where age=10

There is no index set for the age field in the table, so MySQL cannot locate this row of data directly. What are we going to do? of course, it's not a watch lock. MySQL locks all rows in this table, yes, all rows. However, after adding a row lock, MySQL will filter again and release the lock if it finds that the rows are not satisfied, leaving only the rows that meet the criteria. Although in the end, only rows that meet the criteria are locked, the process of releasing the lock also has a great impact on performance. Therefore, if it is a large table, it is recommended to design a reasonable index, if this really happens, it is difficult to ensure the degree of concurrency.

Solve the illusion

When repeatable readability is introduced above, the picture indicates that phantom reading does not actually occur in MySQL, and MySQL has solved the problem of phantom reading at the repeatable isolation level.

As mentioned earlier, the solution to the problem of concurrent writing is row lock, and the solution to phantom reading is also lock, called gap lock. MySQL combines row lock and gap lock to solve the problem of concurrent write and phantom read. This lock is called Next-Key lock.

Suppose you now have two records in the table, and the age field has been indexed, with age values of 10 and 30, respectively.

At this point, a set of B+ trees are maintained in the database for the index to quickly locate row records. The B+ index tree is ordered, so the index of the table is divided into several intervals.

As shown in the figure, it is divided into three intervals, (negative infinity, 10], (10, 30), (30, positive infinity), in which gap locks can be added.

After that, I demonstrate the locking process with the following two transactions.

Before transaction A commits, the insert operation of transaction B can only wait, which is why the gap lock works. When transaction An executes update user set name=' kite No. 2 'where age = 10;, due to the condition where age = 10, the database not only adds row locks on the row of age = 10, but also adds gap locks on both sides of the record, that is, (negative infinity, 10] and (1010) 30], resulting in transaction B insert operation can not be completed, can only wait for transaction A to commit. Not only do you have to wait for transaction A to commit when you insert a record with age = 10, age

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.