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

MYSQL optimistic lock implementation method, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

What is MVCC?

MVCC is Multi-Version Concurrency Control, which is translated into Chinese as multi-version concurrency control.

What problem did MVCC solve?

As we all know, in MYSQL, MyISAM uses table locks and InnoDB uses row locks. The transaction of InnoDB is divided into four isolation levels, among which the default isolation level REPEATABLE READ requires that two different transactions can not affect each other and can support concurrency, which is beyond the reach of pessimistic locks, so REPEATABLE READ uses optimistic locks, and the implementation of optimistic locks uses MVCC. It is because of MVCC that InnoDB has a strong transaction processing ability.

Analysis of concrete implementation of MVCC

InnoDB's MVCC is achieved by saving two hidden columns after each row of records, each of which holds the creation time of the row, and one saves the deletion time of the row. What is stored here is not the actual time value, but the system version number (which can be understood as the ID of the transaction). Each time a new transaction is started, the system version number is automatically incremented, and the system version number at the beginning of the transaction is used as the ID of the transaction. Let's take a look at how MVCC works under the REPEATABLE READ isolation level.

First create a table:

Create table yang (id int primary key auto_increment, name varchar (20)

Suppose the version number of the system starts with 1.

INSERT

InnoDB saves the current system version number as the version number for each newly inserted row. * the ID of each transaction is 1:

Start transaction; insert into yang values (NULL,'yang'); insert into yang values (NULL,'long'); insert into yang values (NULL,'fei'); commit

The corresponding table in the data is as follows (the next two columns are hidden columns, which we can't see through the query statement)

SELECT

InnoDB checks each row of records against the following two criteria:

InnoDB only looks for rows whose version is earlier than the current transaction version (that is, the system version number of the row is less than or equal to the system version number of the transaction), which ensures that the rows read by the transaction either exist before the transaction starts, or are inserted or modified by the transaction itself.

The deleted version of the row is either undefined or greater than the current transaction version number (this ensures that the row read by the transaction is not deleted before the transaction starts), and only records that meet conditions 1 and 2 at the same time can be returned as query results.

DELETE

InnoDB saves the current system version number (transaction ID) as the deletion identity for each line deleted.

Take a look at the following specific example: for the second transaction, the ID is 2:

Start transaction; select * from yang; select * from yang; commit

Hypothesis 1:

Suppose that during the execution of this transaction with an ID of 2, when (1) is executed, another transaction ID inserts a piece of data into the table for 3, and the third transaction ID is 3.

Start transaction; insert into yang values (NULL,'tian'); commit

The data in the table is as follows:

Then execute (2) in transaction 2. Because the creation time of the data of id=4 (transaction ID is 3), the ID of the current transaction is 2, and InnoDB will only find the data rows with transaction ID less than or equal to the current transaction ID, the data rows of id=4 will not be retrieved in transaction 2 (2). The data retrieved by the two select statements in transaction 2 is as follows:

Hypothesis 2

Suppose that in the process of executing the transaction with an ID of 2, the transaction has just been executed (1), and that the transaction has finished executing transaction 3, followed by transaction 4

Fourth transaction:

Start transaction; delete from yang where id=1; commit

The tables in the database are as follows:

Then execute the transaction (2) with a transaction ID of 2. According to the SELECT retrieval condition, it can be known that it will retrieve rows whose creation time (the ID of the transaction was created) is less than the current transaction ID, and the delete time (the ID of the delete transaction) is greater than the row of the current transaction, while the row of id=4 has been mentioned above, and the row of id=1 is greater than the ID of the current transaction due to the delete time (the ID of the delete transaction). So transaction 2's (2) select * from yang will also retrieve the id=1 data. Therefore, the data retrieved by the two select statements in transaction 2 is as follows:

UPDATE

When InnoDB executes UPDATE, it actually inserts a new row of records and saves its creation time as the ID of the current transaction, while saving the ID of the current transaction to the deletion time of the row to be UPDATE.

Hypothesis 3:

Suppose that after executing transaction 2 (1), another user executes transaction 3p4, and then another user performs a UPDATE operation on the table:

Fifth transaction:

Start transaction; update yang set name='Long' where id=2; commit

According to the update principle of update: a new row is generated, and the transaction ID is added to the delete time column of the column to be modified, and the table is as follows:

Continue with transaction 2 (2) and get the following table according to the retrieval conditions of the select statement:

Or get the same result as transaction 2 (1) select.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.