1. MySQL Lock overview

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently . In the database , In addition to traditional computing resources （CPU、RAM、I/O） Beyond contention , Data is also a resource that is Shared by many users . How to ensure the consistency of data concurrent access 、 Validity is a problem that all databases must solve , Lock conflicts are also an important factor affecting the performance of concurrent database access . From this perspective , Locks are especially important for databases , It's more complicated .

MySQL In the lock , According to the granularity of the lock , It can be divided into three categories ：

• Global lock ： Lock all tables in the database .
• Table lock ： Lock the whole table with each operation .
• Row-level locks ： Each operation locks the corresponding row data .

---

2. Global lock

---

2.1 Introduction to global lock

Global lock is to lock the whole database instance , After locking, the whole instance is in Read only status （ Only... Can be executed DQL）, Follow up DML Write statement ,DDL sentence , Transaction commit statements that have been updated will be blocked .

Its typical use scenario is to make a logical backup of the whole database , Lock all tables , To get a consistent view , Ensure data integrity .

Why full library logical backup , It needs to be fully locked ？

Let's first analyze without global lock , Possible problems .

Suppose there are three tables in the database ：tb_stock An inventory statement ,tb_order The order sheet ,tb_orderlog Order log table .

• During data backup , Back up first tb_stock An inventory statement .
• And then next , In the business system , An order was placed , Deducting the inventory , Generate order （ to update tb_stock surface , Insert tb_order surface ）.
• Then perform a backup tb_order Table logic .
• Insert order log in business .
• Last , Back up again tb_orderlog surface .

The data backed up at this time , There are problems . Because the data backed up ,tb_stock Table and tb_order Tabular Data inconsistency （ Order information with the latest operation , But the inventory has not decreased ）.

So how to avoid this problem ? At this point, you can use MySQL Global lock to solve .

Let's analyze the situation after adding global lock

Before the logical backup of the database , First, add a global lock to the entire database , Once the global lock is added , Other clients DDL、DML All blocked , But it can be done DQL sentence , That is, it is in a read-only state , And data backup is query operation . In the process of logical backup of data , The data in the database will not change , So you It ensures the consistency and integrity of data .

---

2.2 Global lock syntax

---

2.2.1 Add global lock

flush tables with read lock;

2.2.2 Release global lock

unlock tables;

2.2.3 The data backup

First connect MySQL：

mysql -h 192.168.135.130 -P 3306 -u root -p

Open global lock ：

flush tables with read lock;

After locking, you will find that the execution is except DQL Statements other than will block ：

Let's back up now codejiao database ：

# mysqldump  yes MySQL  Tools provided ,  This command is not sql sentence ,  Not in MySQL Execute... From the command line of ,  Directly in windows Just execute the command line 
#  here 192.168.135.130 Is the server IP 3306  yes MySQL Service port  
# root Is said root user  317525 It's the user password  codejiao  Is the database name 
# D:\codejiao.sql  Is the name of the file backed up locally   This file may not exist 
mysqldump -h 192.168.135.130 -P 3306 -u root -p317525 codejiao > D:\codejiao.sql

The warning given here is that it is not safe to expose the password directly in the command , But it does not affect the backup operation

What the backup looks like ：

Then you can release the global lock

#  Release global lock 
unlock tables;

---

2.3 Global lock features

Add a global lock to the database , It's a heavy operation , There are the following problems ：

• If it is backed up on the primary database , The update cannot be performed during the backup , Business basically has to stop .
• If you are backing up from a library , During the backup, the slave database cannot synchronize the binary logs from the master database （binlog）, Will cause master-slave delay .

stay InnoDB In the engine , We can add parameters during backup --single-transaction Parameter to complete the consistent data backup without lock （ The bottom is snapshot Realized ）.

#  For example, this can 
mysqldump --single-transaction -h 192.168.135.130 -P 3306 -u root -p317525 codejiao > D:\codejiao2.sql

---

3. Table lock

---

3.1 Introduction to table lock

Table lock , Lock the whole table with each operation . Large locking size , The highest probability of lock collisions , Lowest degree of concurrency . Apply to MyISAM、InnoDB、BDB Wait for the storage engine .

For table lock , It is mainly divided into the following three categories ：

• Table locks
• Metadata lock （meta data lock, MDL）
• Intent locks

---

3.2 Table locks

For table locks , There are two kinds of ：

• Table share read lock （read lock）
• Table Write Lock （write lock）

grammar ：

• Lock ：lock tables Table name ... read / write.
• Release the lock ：
  • The first way ：unlock tables
  • The second way ： Client disconnected .

---

3.2.1 Table share read lock （read lock）

On the left is the client side , A read lock is added to the specified table , It will not affect the reading of the second client on the right , But it will block the writing of the client on the right .（ Any client can only write to this table ）

test :

---

3.2.2 Table Write Lock （write lock）

On the left is the client side , A write lock is added to the specified table , It will block the reading and writing of the client on the right . But this client can read and write .

test :

---

3.2.3 Summary

Read locks do not block reads from other clients , But it will block writing . The write lock will block the reading of other clients , It will block the writing of other clients .

---

3.3 Metadata lock （ Automatic addition , Once the transaction is committed, the metadata lock will be automatically released ）

meta data lock , Metadata lock , Abbreviation MDL.

MDL The locking process is automatically controlled by the system , No explicit use is required , When accessing a table, it will automatically add .MDL The main function of the lock is Maintain data consistency of table metadata , When there are active transactions on the table , You cannot write metadata . for fear of DML And DDL Conflict , Ensure the correctness of reading and writing .

Metadata can be simply understood as the table structure of a table . in other words , When a table involves uncommitted transactions , You cannot modify the table structure of this table .（MySQL The default is transaction commit ）

stay MySQL5.5 Introduced in MDL, When adding, deleting, modifying and querying a table , Add MDL Read the lock ( share ); When changing the table structure , Add MDL Write lock ( Exclusive ).

common SQL In operation , Metadata lock added ：

Corresponding SQL	Lock type	explain
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select …lock in share mode	SHARED_READ	And SHARED_READ、SHARED_WRITE compatible , And EXCLUSIVE Mutually exclusive
insert 、update、delete、select … for update	SHARED_WRITE	And SHARED_READ、SHARED_WRITE compatible , And EXCLUSIVE Mutually exclusive
alter table …	EXCLUSIVE	With other MDL Are mutually exclusive

In general SHARED_READ and SHARED_WRITE compatible , And they're not compatible EXCLUSIVE.

demonstration ：

When executed SELECT、INSERT、UPDATE、DELETE When waiting for a statement , The metadata sharing lock is added （SHARED_READ / SHARED_WRITE）, Is compatible with .

When executed SELECT When the sentence is , The metadata sharing lock is added （SHARED_READ）, Will block the metadata exclusive lock （EXCLUSIVE）, They are mutually exclusive .

We can go through the following SQL, To view the metadata lock in the database （ In fact, the query is the system table performance_schema.metadata_locks The data in it ）：

select object_type, object_schema, object_name, lock_type, lock_duration
from performance_schema.metadata_locks;

---

3.4 Intent locks （ Automatic addition , Once the transaction is committed, the intent lock will be automatically released ）

---

3.4.1 Introduction to intention lock

for fear of DML When executed , Conflict between row lock and table lock , stay InnoDB in The intention lock is introduced , So that the table lock does not need to check whether each row of data is locked , Use intent lock to reduce the checking of table lock .

If there is no intention lock , The client adds a pair of rows after locking the table , How does client 2 lock the table , Let's make a simple analysis through the schematic diagram ：

1. First, the client side , Start a transaction , And then execute DML operation , In execution DML When the sentence is , Yes, it will involve locking .
   
2. When client 2 , I want to add... To this table Table locks when , It will check whether the current table has a corresponding row lock , without , Add table lock , At this time, the data from the first row , Check to the last row of data , Low efficiency （ Full table scan ）.
   

After having intention lock :

Client 1 , In execution DML In operation , Row locks will be added to the rows involved , At the same time, the intention lock will be added to the table .

2. And other clients , When locking this watch , It will judge whether the table lock can be successfully added according to the intention lock added on the table , Instead of judging the line lock line by line （ If you want to lock, you can't add a watch lock ）.
   

---

3.4.2 Intent lock classification

• Intention sharing lock （IS）: By statement select ... lock in share mode add to . Share lock with table lock （read） compatible , Exclusive lock with watch lock （write） Mutually exclusive .
• Intention exclusive lock （IX）： from insert、update、delete、select...for update add to . Share lock with table lock （read） And exclusive lock （write） Are mutually exclusive , Intent locks are not mutually exclusive .

Once the transaction is committed , Intention sharing lock 、 Intention exclusive lock , Will automatically release .

You can use the following SQL, Check the locking of intention lock and row lock ：

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

demonstration ：

---

4. Row-level locks （ Automatic addition , Once the transaction is committed, the row level lock is automatically released ）

Row-level locks , Each operation locks the corresponding row data . Locking granularity minimum , The lowest probability of lock collisions , Highest concurrency . Apply to InnoDB In the storage engine .

InnoDB The data is organized based on indexes , Row locking is achieved by locking the index items on the index , Instead of locking records . For row level locks , It is mainly divided into the following three categories ：

• Row lock （Record Lock）： Lock a single row record , Prevent other transactions from... On this line update and delete. stay RC（ Read submitted ）、RR（ Repeatable ） Isolation level All of you support .
  

• Clearance lock （Gap Lock）： Lock index record gap （ This record is not included ）, Ensure that the index record gap remains unchanged , Prevent other transactions from going on in this gap insert, It produces unreal reading . stay RR（ Repeatable ） Isolation level All of you support .
  

• Temporary key lock （Next-Key Lock）： Combination of row lock and clearance lock , Lock the data at the same time , And lock the gap in front of the data Gap. stay RR（ Repeatable ） Support at isolation level .
  

---

4.1 Row lock

---

4.1.1 Introduction to line lock

InnoDB Two types of row locks are implemented ：

• Shared lock （S）： Allow a transaction to read a line , Prevent other transactions from obtaining exclusive locks on the same dataset .
• Exclusive lock （X）： Allow to get transaction update data of exclusive lock , Prevent other transactions from obtaining shared and exclusive locks of the same dataset .

The compatibility of the two row locks is as follows :

common SQL sentence , When executed , The row locks added are as follows ：

---

4.1.2 Row lock Demo

By default ,InnoDB stay REPEATABLE READ Transaction isolation level running ,InnoDB Use next-key Lock for search and index scan , To prevent unreal reading .

• When searching for a unique index , When performing equivalence matching on existing records , It will be automatically optimized as row lock .
• InnoDB The row lock of is a lock added to the index , Retrieve data without index criteria , that InnoDB Lock all records in the table , At this point, it will be upgraded to table lock .

You can use the following SQL, Check the locking of intention lock and row lock ：

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

Presentation data preparation : Just id There is a primary key index , rest 2 Fields have no index

CREATE TABLE `stu`
(
    `id`   int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `age`  int NOT NULL
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4;

INSERT INTO `stu`
VALUES (1, 'tom', 1);
INSERT INTO `stu`
VALUES (3, 'cat', 3);
INSERT INTO `stu`
VALUES (8, 'rose', 8);
INSERT INTO `stu`
VALUES (11, 'jetty', 11);
INSERT INTO `stu`
VALUES (19, 'lily', 19);
INSERT INTO `stu`
VALUES (25, 'luci', 25);

ordinary select sentence , Execution time , No locks .

select…lock in share mode, Add shared lock

Shared locks are compatible with shared locks .

Shared locks and exclusive locks are mutually exclusive .

As soon as the client gets id by 1 This line of shared locks , Second, the client can obtain id by 3 The exclusive lock of this line , Because it's not the same row of data . And if client 2 wants to get id by 1 Exclusive locks in this line , It's going to be blocked , Think that shared locks and exclusive locks are mutually exclusive .

explain ： The lock I write here refers to the exclusive lock .

Exclusive lock and exclusive lock are mutually exclusive

When a client , perform update sentence , Would be id by 1 We'll lock our records ; Client 2 , If so update Statement update id by 1 The data of , For id by 1 Data with exclusive lock , But client 2 will be blocked , Because exclusive locks are mutually exclusive . Until the client one , Commit the transaction to , Will release the row lock of this row , At this point, client 2 , unblocked .

No index row lock upgraded to table lock ：

stu The data in the table is as follows :

We perform the following operations in two clients :

In client 1 , Open transaction , And implement update sentence , to update name by Lily The data of , That is to say id by 19 The record of . Then update... In client 2 id by 3 The record of , But not directly , It's going to be blocked , Why? ？ The reason is because at this time , Client 1 , according to name When the field is updated ,name The field has no index , If there is no index , At this point, the row lock will be upgraded to a table lock ( Because row locks are locks on index entries , and name No index ).

Next , Let's focus on name Field indexing , After indexing , Do another test ：

 create index idx_stu_name on stu(name);

Now we can see , Client 1 , Open transaction , Then it's still based on name updated . And client 2 , Updating id by 3 When the data is , The update is successful , Not blocked . This shows that , We update according to the index field , You can avoid upgrading row locks to table locks .

---

4.2 Clearance lock & Temporary key lock （next-key lock ）

By default ,InnoDB stay REPEATABLE READ Transaction isolation level running ,InnoDB Use next-key lock （ Temporary key lock ） Search and index scan , To prevent unreal reading .

• Equivalent query on Index （ unique index ）, When locking records that do not exist , Optimized for clearance lock .
• Equivalent query on Index （ It's not the only common index ）, When traversing to the right, the last value does not meet the query requirements ,next-key lock Degenerate to clearance lock .
• Range query on Index （ unique index ） Access to the first value that does not satisfy the condition .

Be careful ： The only purpose of a gap lock is to prevent other transactions from inserting gaps . Gap locks can coexist , A gap lock adopted by one transaction will not prevent another transaction from adopting a gap lock on the same gap .

Demonstration of examples ：

Equivalent query on Index （ unique index ）, When locking records that do not exist , Optimized for clearance lock

Only one client , Transaction committed （ Release the clearance lock ）, To insert id by 7 The data of

Equivalent query on Index （ It's not the only common index ）, When traversing to the right, the last value does not meet the query requirements ,next-key lock Degenerate to clearance lock .

Introduce and analyze ：

We know InnoDB Of B+ Tree index structure , Leaf nodes are ordered two-way linked lists . If , According to this secondary index, the query value is 18 The data of , And add a shared lock , We're just locking 18 Is this all right ？ Not at all , Because it's a non unique index , There may be more than one... In this structure 18 The existence of , therefore , Will continue to look back when locking , Find a value that does not meet the condition （ In the current case, that is 29）. It will be right at this time 18 Add temporary key lock , Also on 29 Lock the previous gap .

Range query on Index （ unique index ） Access to the first value that does not satisfy the condition .

The query criteria are id>=19, And add a shared lock . At this point, we can use the existing data in the database table , Divide the data into three parts ：

• [19]
• (19,25]
• (25,+∞]

So the database data is locked , Will be 19 With a row lock ,25 The key lock of （ contain 25 And 25 The gap before ）, Positive infinite critical lock （ Positive infinity and the gap before ）.

---

5. Summary

---

---