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

This article mainly introduces "introduction of InnoDB lock mechanism of MySQL". In daily operation, I believe many people have doubts about the introduction of InnoDB lock mechanism 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 "introduction to InnoDB lock mechanism of MySQL". Next, please follow the editor to study!

A background

MySQL lock mechanism is an extremely complex implementation, which provides guarantee for database concurrent access and data consistency. Here is only an introduction to the three locks used by MySQL to access data to deepen your mastery of locks.

Two common locking mechanisms

We know that for the InnoDB storage engine, MySQL's row locking mechanism locks the desired data rows by placing locks on the index. The following three lock types are common, all of which are described under the RR transaction isolation level when not declared in this article.

2.1 Record Locks

A record lock is actually a lock on an index that locks a specific row or multiple rows of records. When no index is created on the table, InnoDB creates an implicit clustered index and uses it to lock the data. Usually we can use show innodb status to see information about row locks.

2.2 Gap Locks

A gap lock is a specific range of locks, but does not include the row lock itself. such as

Select * from tab where id > 10 and idselect * from lck_primarkey

+-+ +

| | id | val |

+-+ +

| | 2 | 3 |

| | 4 | 5 |

| | 9 | 8 |

| | 14 | 13 | |

+-+ +

4 rows in set (0.00 sec)

[session1] > begin

Query OK, 0 rows affected (0.00 sec)

[session1] > select * from lck_primarkey where id=9 for update

+-+ +

| | id | val |

+-+ +

| | 9 | 8 |

+-+ +

1 row in set (0.00 sec)

Session 2

[session2] > begin

Query OK, 0 rows affected (0.00 sec)

[session2] > insert into lck_primarkey values (7 and 6)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_primarkey values (5BI 5)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_primarkey values (13pc13)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_primarkey values (10jue 9)

Query OK, 1 row affected (0.00 sec)

Analysis.

For example, when the where field of the access table is a primary key or a unique key, the insertion in session2 is not affected by the id=8 in session1. Official statement

"Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

Select * from tab where id=100 for update "

That is, when a statement accesses data through a primary key or a unique key, Innodb uses Record lock to lock the record itself, rather than a gap lock to lock the range.

You need to pay attention to the following two situations:

1 accessing a value that does not exist through the primary key or the unique index also produces a GAP lock.

[session1] > begin

Query OK, 0 rows affected (0.00 sec)

[session1] > select * from lck_primarkey where id=7 for update

Empty set (0.00 sec)

[session2] > insert into lck_primarkey values (8513)

^ CCtrl-C-sending "KILL QUERY 303042481" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_primarkey values (5pc13)

^ CCtrl-C-sending "KILL QUERY 303042481" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_primarkey values (3Pol 13)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_primarkey values (1013)

Query OK, 1 row affected (0.00 sec)

2 access the data through a part of the fields in the unique index, such as unique key, select * from tab where axix and data; readers can do this example by themselves.

3.2 non-unique key

CREATE TABLE `lck_ secondkey` (

`id`int (11) NOT NULL

KEY `idx_ id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into lck_secondkey values (2), (4), (9), (14)

Session 1

[session1] > begin

Query OK, 0 rows affected (0.00 sec)

[session1] > select * from lck_secondkey

+-- +

| | id |

+-- +

| | 2 |

| | 3 |

| | 4 |

| | 9 |

| | 14 |

+-- +

5 rows in set (0.00 sec)

[session1] > select * from lck_secondkey where id=9 for update

+-- +

| | id |

+-- +

| | 9 |

+-- +

1 row in set (0.00 sec)

Session 2

[session2] > begin

Query OK, 0 rows affected (0.00 sec)

[session2] > insert into lck_secondkey values (3)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_secondkey values (4)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (5)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (6)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (7)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (8)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (9)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (10)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (11)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (12)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (13)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_secondkey values (14)

Query OK, 1 row affected (0.00 sec)

Analysis.

Transaction 1 made for update access to id=9, and session2 failed to insert the value of [4jue 13]. According to the lock principle of MySQL, the Innodb range index or table is through the Next-key locks algorithm. Under the RR transaction isolation level, accessing data rows through non-unique indexes is not locking unique rows, but a range. It can be seen from the example that MySQL locks the records between [4Jing 9] and (9JI 14] to prevent other transactions from modifying values in the range of 4-14. Some readers may not be able to modify the id=4, but the value of id=14 can be inserted? You can see the next example.

[session1] > select * from lck_primarkey

+-+ +

| | id | val |

+-+ +

| | 2 | 3 |

| | 4 | 5 |

| | 9 | 8 |

| | 14 | 13 | |

+-+ +

4 rows in set (0.00 sec)

[session1] > begin

Query OK, 0 rows affected (0.00 sec)

[session1] > select * from lck_primarkey whereval=8 for update

+-+ +

| | id | val |

+-+ +

| | 9 | 8 |

+-+ +

1 row in set (0.00 sec)

Session 2

[session2] > begin

Query OK, 0 rows affected (0.00 sec)

[session2] > insert into lck_primarkey values (3pr 5)

Query OK, 1 row affected (0.00 sec)

[session2] > insert into lck_primarkey values (155.13)

Query OK, 1 row affected (0.00 sec)

[session2] > select * from lck_primarkey

+-+ +

| | id | val |

+-+ +

| | 2 | 3 |

| | 3 | 5 |

| | 4 | 5 |

| | 9 | 8 |

| | 14 | 13 | |

| | 15 | 13 | |

+-+ +

6 rows in set (0.00 sec)

[session2] > insert into lck_primarkey values (16,512)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_primarkey values (16pr 6)

^ CCtrl-C-sending "KILL QUERY 303040567" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

[session2] > insert into lck_primarkey values (16pr 5)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[session2] >

[session2] > insert into lck_primarkey values (1BI 5)

Query OK, 1 row affected (0.00 sec)

Analysis.

Because session1 adds a gap lock to the non-unique key val=8 [4je 5]-[14pr 13], records other than this interval can be inserted into the table. The val values in the records (1meme 5), (15recovery13) are not in this gap lock range, and cannot be inserted because the val values in the recordings (16meme12), (16recovery6) and (16recovery5) are within the locked range.

At this point, the study of "introduction to the InnoDB lock mechanism 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.