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

This article mainly introduces the example analysis of MySQL sentence locking, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Prepare beforehand

Create a hero table that stores the heroes of the three Kingdoms:

CREATE TABLE hero (number INT, name VARCHAR, country varchar, PRIMARY KEY (number), KEY idx_name (name)) Engine=InnoDB CHARSET=utf8

Then insert a few records into the table:

INSERT INTO hero VALUES (1,'l Liu Bei', 'Shu'), (3,'z Zhuge Liang', 'Shu'), (8,'c Cao Cao', 'Wei'), (15,'x Xun Xun', 'Wei'), (20, 'Sun Quan','Wu')

Then the hero table now has two indexes (a secondary index and a clustered index), as shown in the following diagram:

Statement locking analysis

In fact, "what lock should be added to the XXX statement" itself is a false proposition, and the lock that a statement needs to add is restricted by many conditions, such as:

Isolation level of the transaction

The index used when the statement is executed (such as clustered index, unique secondary index, ordinary secondary index)

Query conditions (for example, =, =, etc.)

The type of statement executed specifically

Before we continue to analyze the locking process of statements in detail, we must have a global concept: locking is only a solution to the problems of dirty writing, dirty reading, unrepeatable reading and phantom reading caused by the execution of concurrent transactions (MVCC is a solution to the problems of dirty reading, unrepeatable reading and phantom reading). We must be aware that the starting point of locking is to solve these problems. The problems to be solved in different situations are different, which leads to different locks. Do not add locks for the sake of locking. It is easy to wrap yourself in. Of course, sometimes locking in some scenarios is difficult to understand because of the specific implementation of MySQL, so we have to memorize it.

Here we divide statements into three categories: ordinary SELECT statements, locked read statements, and INSERT statements. Let's take a look at them respectively.

Ordinary SELECT statement

The normal SELECT statement is as follows:

Under the READ UNCOMMITTED isolation level, the latest version of the record is read directly without locking, which may lead to dirty reading, non-repeatable reading and phantom reading.

Under the READ COMMITTED isolation level, without locking, a ReadView is generated every time an ordinary SELECT statement is executed, which solves the problem of dirty reading, but does not solve the problem of unrepeatable reading and phantom reading.

Under the REPEATABLE READ isolation level, there is no lock, and only a ReadView is generated when the ordinary SELECT statement is executed for the first time, which solves the problems of dirty reading, unrepeatable reading and phantom reading.

But here's an interlude:

# transaction T2 executes: INSERT INTO hero VALUES (30,'g Guan Yu', 'Wei') under mysql > BEGIN;Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM hero WHERE number = 30 sec) # transaction T2 executes: INSERT INTO hero VALUES (30,'g Guan Yu', 'Wei'); and submit mysql > UPDATE hero SET country = 'Shu' WHERE number = 30 scene query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT * FROM hero WHERE number = 30 | | number | name | country | | 30 | g Guan Yu | Shu | 1 row in set (0.01 sec) |

At the REPEATABLE READ isolation level, T1 generates a ReadView the first time a normal SELECT statement is executed, and then T2 inserts a new record into the hero table and commits it. ReadView does not prevent T1 from executing UPDATE or DELETE statements to change the newly inserted record (because T2 has been committed, changing the record does not cause blocking), but the trx_id hidden column of the new record becomes the transactional id of T1. After that, when T1 uses the ordinary SELECT statement to query the record, you can see the record and return the record to the client. Because of this special phenomenon, you can also think that MVCC in InnoDB cannot completely prohibit phantom reading.

Under the SERIALIZABLE isolation level, there are two situations to discuss:

When the system variable autocommit=0, when autocommit is disabled, ordinary SELECT statements are converted to SELECT. Statements such as LOCK IN SHARE MODE, that is, you need to obtain the S lock of the record before reading the record. The specific locking situation is the same as under the REPEATABLE READ isolation level, which we will analyze later.

When the system variable autocommit=1, that is, when autocommit is enabled, the normal SELECT statement does not lock, but uses MVCC to generate a ReadView to read the record.

Why not lock it? Because enabling autocommit means that there is only one statement in a transaction, there is no problem of unrepeatable reading or phantom reading.

Lock the read statement

Let's put the following four sentences together to discuss:

Statement 1: SELECT... LOCK IN SHARE MODE

Sentence 2: SELECT. FOR UPDATE

Sentence 3: UPDATE...

Sentence 4: DELETE.

We say that statement one and statement two are the two grammatical formats of lock reading specified in MySQL, while statement three and statement four can also be regarded as a lock read because they need to locate the changed record and lock the record first during execution.

Under the READ UNCOMMITTED/READ COMMITTED isolation level

The locking method of statements under READ UNCOMMITTED is basically the same as that of statements under READ COMMITTED isolation level, so we put it together. It is worth noting that when locking is used to solve the problems caused by concurrent transactions, dirty reads and non-repeatable reads do not occur at any isolation level (because read-write operations need to be queued).

For the case of using primary keys for equivalent queries

Use SELECT... LOCK IN SHARE MODE to lock the record, for example:

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE

When this statement is executed, you only need to access the record in the clustered index with a number of 8, so you just need to add an S-type formal record lock to it, as shown in the figure:

Use SELECT... FOR UPDATE to lock the record, for example:

SELECT * FROM hero WHERE number = 8 FOR UPDATE

When this statement is executed, you only need to access the record in the clustered index with a number of 8, so you just need to add an X-type formal record lock to it, as shown in the figure:

Tip: in order to distinguish between S-lock and X-lock, we then dye the S-locked record blue and the X-locked record purple in the diagram.

Use UPDATE... To lock the record, for example:

UPDATE hero SET country = 'Han' WHERE number = 8

This UPDATE statement does not update the secondary index column, the locking method, and the above mentioned SELECT. The FOR UPDATE statement is consistent.

If the secondary index column is updated in the UPDATE statement, for example:

UPDATE hero SET name = 'cao Cao Cao' WHERE number = 8

The actual execution step of this statement is to first update the corresponding clustered index record with a number of 8, and then update the corresponding secondary index record, so the locking step is:

Add an X-type normal record lock to the clustered index record with a number value of 8 (corresponding to that record).

The idx_name secondary index record corresponding to the clustered index record (that is, the secondary index record with a name value of'c Cao Cao 'and a number value of 8) is added with an X-type normal record lock.

Draw a picture like this:

Tip: we use circled numbers to indicate the order in which records are locked.

Use DELETE... To lock the record, for example:

DELETE FROM hero WHERE number = 8

What we usually call "a record in the DELETE table" actually means doing DELETE operations on the clustered index and all the corresponding records in the secondary index. In this example, we should first perform the DELETE operation on the clustered index record with a number value of 8, and then delete the corresponding idx_name secondary index record, so the locking steps are consistent with the above update statement with a secondary index column, so there is no drawing.

For the case of a range query using a primary key

Use SELECT... LOCK IN SHARE MODE to lock the record, for example:

The first record of SELECT * FROM hero WHERE number ='c Cao Cao', that is, the record with a name value of c Cao Cao, can then be found all the way back along the linked list of this record. As can be seen from the schematic diagram of the secondary index idx_name, all user records meet the condition of name > ='c Cao Cao', so all secondary index records are locked with S-shaped formal records. Their corresponding clustered index records will also be locked by S-shaped formal records. However, you need to pay attention to the locking order. After locking a secondary index record, it will then lock its corresponding clustered index record, and then lock the next secondary index record. And so on ~ draw a diagram to show that this is it:

Let's look at the following sentence:

SELECT * FROM hero FORCE INDEX (idx_name) WHERE name

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.