当前位置：网站首页>MySQL lock

When in the case of a single thread , Is the probability of no lock , There is no need to lock . And in our daily system development , Nor can there be a single threaded program . When multiple threads or processes compete for resources , The appearance of the lock is very necessary .

When both Xiao Chen and Xiao Zhang want to go to the bathroom , But there is only one pit , So when Xiao Chen occupied the pit first , Be sure to lock the door , Xiao Zhang can only wait for Xiao Chen to go to the bathroom , Open the door , Xiao Zhang can go to the bathroom .

Lock can ensure that only one thread can occupy resources during concurrency , Ensure the consistency of access , effectiveness .

Locks are used to manage concurrency control over public resources .

At first, the lock of the computer was only pessimistic , And then later , Only then gradually had the optimistic lock , Biased locking , Clearance lock , Block lock and so on .

There are many locks in the computer , We Java Commonly used juc In the lock ,synchronize Medium jvm Lock of .

ACID

Atomicity (Atomicity)、 Uniformity (Consistency)、 Isolation, (Isolation)、 persistence (Durability)

1、 Atomicity refers to the fact that a transaction is an indivisible unit of work , Either do all of them , Either not . That is, the transfer is successful , Or transfer failed , There is no middle state ！

2、 Consistency refers to before and after transaction execution , Data is in a legal state , This state is semantic rather than grammatical .

3、 Isolation refers to the concurrent execution of multiple transactions , Operations within a transaction are isolated from other transactions , Transactions that execute concurrently cannot interfere with each other .

4、 Persistence is when a transaction is committed , Its changes to the database should be permanent . Subsequent operations or failures should not have any impact on it .

mysql Why locks are needed ？

A database is a resource that multiple users can access and use together , When multiple users access or modify the same resource at the same time , If there is no lock control for concurrent operations , It will lead to different data obtained by multiple users , The data may also be incorrect , It breaks the consistency principle of the database .

Locking can solve the consistency problem of database under concurrent operation .

According to the different directions of the lock , It can be roughly divided into the following categories , Let's talk about each of them （InnoDB）

Mode of operation ： Read the lock / Write lock

Read the lock （ Shared lock ,Share, S lock ）

Allow a transaction to read a row , Prevent other transactions from obtaining exclusive locks for the same dataset .Select * from table where id = 1 lock in share mode;

When a transaction 1 Got a read lock , Business 2 It is also the case to obtain the read lock , Then both transactions can access the same resource A, Business 3 And transaction 4 What you get is an exclusive lock , You can only wait for the transaction 1 And transaction 2 Only after processing can the resource A Update processing .

Be careful ： With read lock , Stop updating data , Otherwise, when modifying data, there are other lock reading transactions to obtain data , There may be a deadlock .

Write lock （ Exclusive lock ,exclusive, X lock ）

Allow exclusive lock transactions to update data , Prevent other transactions from obtaining the shared read lock and exclusive write lock of the same dataset . select * from table where id = 1 for update;

When a transaction 1 Got a lock , Regardless of business 2,3,4 Is it a shared lock or an exclusive lock , Can't get resources A, We have to wait for business 1 Only after processing can the resource A Take the next step .

Be careful ：insert, update, delete Will automatically add an exclusive lock to the transaction ,select Add a shared lock or exclusive lock to the query statement in addition to the displayed one （select ***lock in share mode / select *** for update）, It won't lock itself . Will not be blocked waiting for the lock , The data will be read normally .

Intent locks

Intent locks are designed to handle the performance between row locks and table locks , Intent locks are table level locks .

Intention sharing lock （IS）： Before requesting a shared lock , First obtain the intention sharing lock

Intention exclusive lock （IX）： Before requesting an exclusive lock , First get the intention exclusive lock

The function of intention lock ： Used to deal with the coexistence of multi granularity locks .

Let me give you an example ： Business 1 Got the data row 1 Exclusive lock , If the transaction 2 Get data row 2 Exclusive lock , Because no index is used , Causes row locks to become table locks , If the watch is locked, you will first check whether the watch intends to lock , Found something 1 Give the intention of exclusive lock , The transaction 2 We have to wait for business 1 Lock the table after processing . If there is no intention of exclusive lock , So when you want to lock the whole watch , You have to traverse the data , See if any data rows are locked , This kind of efficiency is very low .

Classification of locks

Pessimistic locking

When a transaction 1 When manipulating certain data , It is believed that other transactions will operate on the data at the same time , Therefore, lock in advance , Make sure that in business 1 The data will not change during execution .

For example, add... At the beginning of the transaction for update .

Optimism lock

When a transaction 1 When manipulating certain data , It will be considered that no other transaction will operate the data at the same time , So just when updating the data , Make a judgment based on the initial value .

Optimistic locks are not included in the database , We need to achieve it ourselves .

Update table set amount = 1 where version= 1;

Advantages and disadvantages

Pessimistic locking ：

advantage ： It can ensure that the data in the current transaction stage will not change , It can ensure that the acquisition and update of data are carried out in an orderly manner . It can guarantee the security of data . It is suitable for writing more and reading less scenes

shortcoming ： Because it needs to be locked , There will be locks waiting , Deadlock, etc , In the situation of reading more and writing less , The performance will be greatly reduced .

Optimism lock ：

advantage ： No need to lock , Greatly improved performance , Suitable for reading more and writing less

shortcoming ： If you write more and read less , It may cause multiple data updates to fail , Multiple retries are required , Instead, it reduces the performance of the program . Improper use may not guarantee the security of data , Such as ABA problem .

Lock granularity

Row lock

Transactions are allowed to read only one row of data , Granularity is in every row of data . Automatic locking

advantage ： The granularity of the lock is small , The probability of lock conflict will be very low , Strong ability to handle concurrency

shortcoming ： Spending big , Lock the slow , The performance of locking is low , There will be deadlock .

Row level locks are implemented only in the storage engine layer , and mysql The server layer is not implemented .

Row locking is achieved by locking the index entries on the index , Therefore, if a row lock is added, the search conditions are indexed , If one sql If the index is not used in the statement , It will be upgraded from row lock to table lock .

Whether using primary key index 、 The only index is the ordinary index ,InnoDB Row locks are used to lock data .

Using the index , It must be a row lock ？

Not necessarily , In some cases , Even if the index is used ,mysql There will be an optimizer to optimize before execution , If mysql Optimizer task full table scan will be faster , Then he won't use the index , No row lock , Use the watch lock directly . How to judge whether the index will go , Then use explain Check it out sql The implementation plan of , See if you can really go .

A row lock is a lock on an index , It's not a lock on records , Although accessing different data rows , But if you use the same index , There will also be conflicts .

example ：table in name It's the index

T1:Select * from table where name = ‘xuan’ and age = 18 for update;

T2:Select * from table where name = ‘xuan’ and age = 19 for update;

Business T2 Will be blocked , wait for T1 Execute play release lock .

Page locks

be applied to BerkeleyDB Storage engine , The granularity is between row lock and table lock . A deadlock occurs , Concurrency is also average

Table locks ：

Allow transactions to exist simultaneously at row level and table level , Is to lock the whole watch . Automatic locking .

advantage ： Low overhead , Locked fast , There will be no deadlock

shortcoming ： The size of the lock is big , It's easy to have lock conflicts , Low concurrency

Table lock is suitable for queried data , Less concurrency .

mysql There are two types of table level locks , Table lock and metadata lock .

Metadata lock （MDL）：

When operating on the table , The table will be locked with a metadata lock . Automatic locking , stay mysql5.5 Version above is supported .

When adding, deleting, modifying and querying a table, a read lock will be added , Write lock will be added when changing the structure of the table

Read lock is not mutually exclusive , Multi threaded colleagues can add, delete, modify and query a table

Read the lock , Write locks are mutually exclusive , If multiple threads change the structure of the table , To ensure safety , Will become serialization , One by one .

By default ,MDL The priority of write lock is higher than that of read lock , Can be set by max_write_lock_count System variable to set priority .

Global lock

Used to lock the entire database instance , After the lock , The whole instance will be in a read-only state . Other updated data , Transactions such as modifying the table structure will be blocked .

Lock command ：FLUSH TABLES WITH READ LOCK;

Unlock command ：UNLOCK TABLES;

It is generally used for backup , In order to ensure the integrity of the data .

notes ：mysql8.0 in the future , If you want to back up , Backup lock can be used :

LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE;

Learned about locks with different granularity , that mysql What is the granularity of locks supported by different storage engines in ？

Row-level locks ：InnoDB

Page level lock ：BerkeleyDB

Table lock ：MyISAM、InnoDB、MEMORY、CSV

Algorithm dimension

Record locks , Temporary key lock , Clearance locks are exclusive locks

Record locks

After the transaction is locked , What is locked is a record in the table . That is, the line lock

Conditions ： Accurate condition hit , And the field of the hit condition is the only index

Select * from table where id = 1 for update;

If no index is set when creating the table , that InnoDB The storage engine will use an implicit primary key to lock .

effect ： Adding record lock avoids the problem of repeated reading caused by data modification of other transactions during query , It also avoids the problem of uncommitted dirty reading

Clearance lock

The query criteria are range query , And used an exclusive lock （for update）, The storage engine will lock the index of existing data .

The gap lock must be in RR Under the condition of transaction isolation

Use normal index
Use multi column unique index
Lock multiple rows with unique index

The above three situations will produce clearance lock .

Example data ：

If a unique index is used to query the existing data , There is no gap lock , It's a record lock

example ：select * from test where id = 4 for update;

At this time, the record lock is used

Use unique index ：

Pictured above , There are... In the table 5 The gaps are as follows ：

（-∞,1] (1,4] (4,8] (8,13] (13,+∞]

Then, if I query the data in one of these gaps that does not exist in the table , Then there will be a gap lock ;

example ：select * from test where id = 2 for update;

At this time, the clearance lock is used , Locked 1-4 Data between , If you are inserting id 2,3 It will be blocked .

example ：selelct * from test where id > 2 and id < 7 for update;

Will lock 1-8 Data between , If you operate 1-8 The data of , Will be blocked

example ：select * from test where id > 4 and id < 8 for update;

Will lock 4-8 Data between , If you operate 4-8 The data of , Will be blocked

Use normal index ：

to test Tabular age Add a normal index

Data as shown ：

There are... In the table 5 The gaps are as follows ：

（-∞,10] (10,16] (16,20] (20,25] (25,+∞]

example ： Give the general index age Of (10,16] Add a gap lock in the gap , So the operation 10-16 Data between will be blocked , Data outside this range can be inserted normally .

On the normal index column , No matter what kind of query , Just lock it , There will be a gap lock , This is not the same as the only index ;

example ： Give the general index age Of (10,16] Add a gap lock in the gap , So the operation 10-16 Data between will be blocked , So for inserting 16 This data , Such as business 2, When id=3 Is blocked , Such as business 3 When id=6 when , Then it can be inserted successfully .

In general index and unique index , Analysis of data gaps , Data rows are prioritized by normal index , Sort by unique index .

Temporary key lock ：

It's a combination of recording lock and clearance lock , Its blockade area , Include both index records , It also includes index range

1、 Record locks 、 Clearance lock 、 Temporary key lock , All belong to exclusive lock ;

2、 Record lock is to lock a line of records ;

3、 Gap locks are only available at the transaction isolation level RR That's what happens ;

4、 The only index can only lock multiple records or a nonexistent record , There will be a gap lock , When you lock an existing record , Only a record lock , There will be no gap lock ;

5、 A common index, whether it's a single lock , Or multiple records , There will be a gap lock ;

6、 The gap lock will block the blank area between the two adjacent keys of the record , Prevent other transactions from inserting 、 modify 、 Delete data , This is to prevent Fantasy reading The phenomenon ;

7、 The gap of common index , Sort by ordinary index first , Then sort by primary key index （ The situation of multiple general indexes has not been studied ）;

8、 The transaction level is RC（ Read submitted ） Level words , The gap lock will fail .

版权声明
本文为[Xuanguo Guo]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204210558502647.html