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

This article focuses on "analyzing the relationship between transaction isolation levels and locks in MySQL database Innodb". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "analyzing the relationship between transaction isolation levels and locks in MySQL database Innodb."

Where id=1, Class 3, Grade 3, update class_teacher set class_name=' junior high school

Commit

Select id,class_name,teacher_id from class_teacher where teacher_id=1

Idclass_nameteacher_id1 Grade 3, Class 3, Class 12, Grade 3, Class 1

Read the data modified by transaction B, which is different from the result of the first query and cannot be re-read.

Commit

After transaction B modifies the data commit of id=1, transaction A has the same query, and the result of the second query is different from that of the previous one, which is that it cannot be reread (the result of rereading is different). This is likely to cause some problems, so let's take a look at MySQL's performance at the RR level:

Transaction A transaction B transaction Cbegin

Begin

Begin

Select id,class_name,teacher_id from class_teacher where teacher_id=1

Idclass_nameteacher_id1 Grade 3, Class 2, Class 12, Grade 3, Class 1

Where id=1, Class 3, Grade 3, update class_teacher set class_name=' junior high school

Commit

Insert into class_teacher values (Class 3, Grade 3, null,'', 1); commit

Select id,class_name,teacher_id from class_teacher where teacher_id=1

Idclass_nameteacher_id1 Grade 3, Class 2, Class 12, Grade 3, Class 1

The data modified by transaction B is not read, which is the same as the one read by sql for the first time, which is repeatable.

The newly added data from transaction C was not read.

Commit

We notice that when teacher_id=1, transaction A makes a read first, transaction B modifies id=1 's data in the middle, and after commit, transaction A reads exactly the same data as the first time. So it's readable. So how does MySQL do it? Let's sell it here for a while. Let's look down.

# the difference between unrepeatable reading and phantom reading #

Many people are easy to confuse unrepeatable reading and phantom reading, but there are some similarities between the two. But unrepeatable readings focus on update and delete, while phantom readings focus on insert.

If the locking mechanism is used to achieve these two isolation levels, in repeatable reads, the data is locked after the sql reads the data for the first time, and other transactions cannot modify the data, so repeatable readings can be achieved. However, this method can not lock the data of insert, so when transaction A reads the data previously, or modifies all the data, transaction B can still commit insert data, and transaction A will find that there is an inexplicable extra piece of data that was not available before, which is illusory reading, which cannot be avoided by row locking. Serializable isolation level, read lock, write lock, read lock and write lock are required, which can effectively avoid the problems of phantom read, unrepeatable read and dirty read, but will greatly reduce the concurrency ability of the database.

So the biggest difference between unrepeatable reading and phantom reading lies in how to solve their problems through the locking mechanism.

As mentioned above, pessimistic locking mechanism is used to deal with these two problems, but mature databases such as MySQL, ORACLE and PostgreSQL all use MVCC (multi-version concurrency control) based on optimistic locking to avoid these two problems for performance reasons.

# pessimistic lock and optimistic lock #

Pessimistic lock

As the name suggests, it refers to a conservative attitude towards the modification of data by the outside world (including other current transactions of the system, as well as transactions from external systems), so that the data is locked during the whole data processing process. The realization of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can really ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify the data.

In the case of pessimistic locking, in order to ensure the isolation of transactions, consistent locking reads are needed. The data is locked when it is read and cannot be modified by other transactions. It is also locked when modifying and deleting data, which cannot be read by other transactions.

Optimistic lock

Compared with pessimistic locking, optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic locks are mostly realized by the locking mechanism of the database to ensure the maximum exclusivity of the operation. But it is followed by a large amount of database performance overhead, especially for long transactions, which is often unbearable.

The optimistic locking mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on data version (Version) recording mechanism. What is the data version? That is, to add a version identity to the data, which is generally achieved by adding a "version" field to the database table in the version solution based on the database table. When reading out the data, read out the version number together, and then add one to the version number when it is updated. At this point, the version data of the submitted data is compared with the current version information recorded in the corresponding database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it is regarded as out-of-date data.

To be clear, there is no fixed specification for the implementation of MVCC, and each database will have a different implementation. Here we are talking about InnoDB's MVCC.

# implementation of MVCC in InnoDB of MySQL

In InnoDB, two additional hidden values are added after each row of data to implement MVCC, one of which records when the row of data is created and the other records when the row of data expires (or is deleted). In practice, what is stored is not the time, but the version number of the transaction, which is incremented every time a new transaction is opened. Under the rereadable Repeatable reads transaction isolation level:

When SELECT, read the creation version number of the current transaction version number.

When INSERT, save the current transaction version number as the created version number of the row

When DELETE, save the deleted version number of the current transaction version number as the row

When UPDATE, insert a new record, save the current transaction version number to create the version number for the row, and save the current transaction version number to the original deleted row

With MVCC, although each row of records requires additional storage space, more row checking and some additional maintenance work, you can reduce the use of locks, most read operations do not need to be locked, read data operations are very simple, good performance, and can ensure that only the rows that meet the standard will be read, and only the necessary rows will be locked.

Whether we learn from the database teaching books, or from the network, most of the meaning listed in the module of the four isolation levels of transactions above, the RR level is repeatable, but can not solve the illusion, but only at the Serializable level can solve the illusion. So I added a transaction C to show the effect. There should be phantom readings in the data commit,RR level where a teacher_id=1 is added to transaction C. transaction A will read the new data added by transaction C when querying the data of teacher_id=1. However, after testing, it is found that this situation does not exist in MySQL, and transaction A will not read this data after transaction C commits. It can be seen that in the RR level of MySQL, the reading problem of phantom reading is solved. See the following figure

The read problem is solved. According to the definition of MVCC, conflicts occur when data is submitted concurrently, so how to resolve conflicts? Let's take a look at the processing of write data at the RR level in InnoDB.

# the difference between "read" and "read"

Some readers may wonder that the isolation level of the transaction is actually the definition of read data, but at this point, it is divided into read and write modules to explain. This is mainly because the reads in MySQL are different from those in the transaction isolation level.

Let's see, at the RR level, though the MVCC mechanism makes the data repeatable, the data we read may be historical data, not timely data, not the current data in the database! This is likely to go wrong in some businesses that are particularly sensitive to the timeliness of data.

This way of reading historical data is called snapshot read, while the way of reading data from the current version of the database is called current read (current read). Obviously, in MVCC:

Snapshot read: select

Select * from table....

Current read: special read operation, insert / update / delete operation, belongs to the current read, deals with the current data and needs to be locked.

Select * from table where? Lock in share mode

Select * from table where? For update

Insert

Update

Delete

The isolation level of the transaction actually defines the current read level. In order to reduce the time of lock processing (including waiting for other locks) and improve the concurrency ability, MySQL introduces the concept of snapshot read, so that select does not need to be locked. And update, insert these "current read", need another module to solve.

# write ("current read")

Although only the requirement for reading data is defined in the isolation level of the transaction, it can actually be said to be a requirement for writing data. The "reading" above is actually a snapshot reading, while the "writing" mentioned here is the current reading.

In order to solve the problem of phantom reading in current reading, MySQL transactions use Next-Key locks.

# Next-Key lock

Next-Key lock is a combination of row lock and GAP (gap lock). Row lock has been introduced above, and then we will talk about GAP gap lock.

Row locks can prevent data conflicts when different transaction versions of data are modified and committed. But how to prevent other transactions from inserting data becomes a problem. We can look at the comparison between RR level and RC level.

RC level:

Transaction A transaction Bbegin

Begin

Select id,class_name,teacher_id from class_teacher where teacher_id=30

Class 30, Class 2, Grade 3, idclass_nameteacher_id2 Junior High School

Where teacher_id=30, Class 3, 4, update class_teacher set class_name=' Junior High School

Insert into class_teacher values (Class', 30, Class 3, null,' Junior High School)

Commit

Select id,class_name,teacher_id from class_teacher where teacher_id=30

Idclass_nameteacher_id2 Grade 3, Class 4, Class 3010, Grade 3, Class 2, Grade 30

RR level:

Transaction A transaction Bbegin

Begin

Select id,class_name,teacher_id from class_teacher where teacher_id=30

Class 30, Class 2, Grade 3, idclass_nameteacher_id2 Junior High School

Where teacher_id=30, Class 3, 4, update class_teacher set class_name=' Junior High School

Insert into class_teacher values (Class', 30, Class 3, null,' Junior High School)

Waiting....

Select id,class_name,teacher_id from class_teacher where teacher_id=30

Class 30, Class 3, 4, idclass_nameteacher_id2 Junior High School

After commit; transaction Acommit, the insert of transaction B executes.

By comparison, we can find that at the RC level, transaction A modifies all teacher_id=30 data, but when transaction Binsert enters the new data, transaction A finds that there is an inexplicable extra row of teacher_id=30 data that has not been modified by the previous update statement, which is the illusion of "current read".

At the RR level, transaction An is locked after the update, and transaction B cannot insert new data, so that the data read by transaction A before and after the update is consistent, avoiding illusory reading. This lock is the Gap lock.

MySQL is implemented as follows:

In the class_teacher table, teacher_id is an index, so it maintains a set of B + tree data relationships. To simplify, we use a linked list structure (actually a tree structure, but the principle is the same).

As shown in the figure, InnoDB uses a clustered index. As a secondary index, teacher_id maintains a tree structure of index fields and primary key id (represented here as a linked list) and keeps it in order.

Innodb divides the data into several intervals

(negative infinity, 5)

(5pr 30)

(30pr positive infinity)

Where teacher_id=30; of Class 3 and 4 of update class_teacher set class_name=' not only used row locks to lock the corresponding data rows, but also added gap locks in the intervals on both sides, (5 and 30) and (30 positive infinity). In this way, transaction B cannot insert new data in these two intervals.

Limited by this implementation, Innodb often locks intervals that do not need to be locked. As follows:

Transaction A transaction B transaction Cbegin;begin;begin

Select id,class_name,teacher_id from class_teacher

Idclass_nameteacher_id1 Grade 3, Class 1, Class 52, Grade 3, Class 2, 30

Where teacher_id=20, Class 1, Grade 8, update class_teacher set class_name='.

Insert into class_teacher values (Class 5, Grade 3, null,'', 10)

Waiting.

Insert into class_teacher values (Class 3 and 5 of null,'', 40); after commit; transaction A commit, this statement was successfully inserted into commit.

Commit

The teacher_id=20 of update is in the interval (55030). Even if no data is modified, Innodb will add a gap lock in this interval, while other intervals will not be affected, and transaction C will be inserted normally.

If you use a field that does not have an index, such as update class_teacher set teacher_id=7 where class_name=' Class 8, Grade 8 (even if it does not match any data)', then the whole table will be locked with gap. At the same time, it cannot automatically release unqualified locks by MySQL Server filtering like the row lock above, because without an index, these fields will have no sorting and no interval. No other transaction can insert any data unless the transaction commits.

The row lock prevents other transactions from being modified or deleted, the GAP lock prevents other transactions from being added, and the Next-Key lock formed by the combination of row lock and GAP lock solves the problem of phantom reading when writing data at the RR level.

# Serializable

This level is very simple, read plus shared lock, write plus exclusive lock, read and write mutually exclusive. The pessimistic lock theory is easy to implement and the data is more secure, but the concurrency ability is very poor. You can use this model if your business has little or no concurrency and requires timely and reliable data.

Here, I would like to complain, do not see select and say that there will be no lock, at the level of Serializable, there will be lock!

At this point, I believe you have a deeper understanding of "analyzing the relationship between transaction isolation levels and locks in MySQL database Innodb". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.