Mysql database 5 --- database lock

Prodigal son Tang Shao 2022-02-13 07:29:03 阅读数:198

mysql database database lock

mysql database 3— The database lock

1. The concept of lock

In real life, it is a tool for us to hide in the outside world , In the computer , Is to coordinate multiple processes or threads to access a resource concurrently
A mechanism , In the database , In addition to traditional computing resources (CPU、RAM、I/O wait ) In addition to the dispute , Data is also a way for many users to share
Resources accessed , How to ensure the consistency of data concurrent access 、 effectiveness , It's a problem that all databases have to solve , Lock conflicts also affect data
An important factor in the performance of concurrent access to libraries . Locks are particularly important for databases .
When buying goods , There is only... In stock 1 Time , When two people buy at the same time , Who bought it , It uses transactions , First, take the data of the items from the inventory table , then
Insert order , After payment , Insert payment table information . Update the quantity of goods , In the process , Using locks can protect limited resources , Achieve isolation and integration
Hair contradiction

2. Classification of locks

1. According to the operation points

Read the lock ( Shared lock ): For the same data , Multiple read operations can be performed simultaneously without affecting each other
Write lock ( Exclusive lock ): Before the current write operation is completed , Will block other write and read locks

2. By granularity

(1) Table locks
(2) Row lock
(3) Page locks

PS: Different storage engines support different locking mechanisms . According to different storage engines ,MySQL The characteristics of the lock are divided into the following

Storage engine Row lock Table locks Page locks
MyISAM Support
InnoDB Support Support
BDB Support Support

3. Table locks

1. Concept

deviation MyISAM Storage engine , Low overhead , Locked fast , No deadlock , Large locking size , The highest probability of lock collisions , Concurrency is the bottom

PS: Table locks (MyISAM Storage engine ), In the use of SELECT Before the query statement , Will automatically lock all tables involved , After the query operation is completed, the lock will be released

Since the read lock is added and released by MySQL Done automatically , And it is impossible to capture the timing of locking and releasing the lock , Therefore, you need to manually command locking and unlocking to get the effect .

2. involve sql command

(1) View non temporary tables being opened in all databases

-- Query all tables 
show open tables;
-- Check whether the table is locked 
show OPEN TABLES where In_use > 0;
-- Query whether the specified table of the specified database is opened ( Use or lock )
show open tables from Data name where `Table` = ' Table name ';

Query results :

The meaning of query result column :

In_use: Number of times to open the table lock of the table , 0 Indicates that... Is not opened , 1 representative The watch is locked

Name_locked; Whether the table name is locked , 0 For unlocked , Only when deleting the table or renaming the table name , Only then 1

(2) Lock the watch

# Manually add a read lock to the table 
lock table Table name 1 read;
# Manually add write lock to the table 
lock table Table name 1 write;

(3) Release all locks

unlock tables;

3. How table lock and read lock affect operation and performance

(1) Counter table "user" Add read lock

lock table user read;( Shared lock )

Current connection ( conversation ) What can be done :

problem answer
Whether you can see yourself Sure
Whether the data can be updated or insert data Can not be
Can you read another watch Can not be , The current table has not been unlocked , You can't put down the current , Operate other contents

Another connection ( conversation ) What can be done :

problem answer
Whether you can see yourself Sure
Whether the data can be updated or insert data Handle blocking status , Wait for unlocking , To update
Can you read another watch Sure

4. How table lock and write lock affect operation and performance

(1) Counter table "user" Add write lock

lock table user write;( Exclusive lock )

Current connection ( conversation ) What can be done :

problem answer
Can you read your locked watch Sure
Can you change your locked watch Sure
Can I read another table Can not be

Another connection ( conversation ) What can be done :

problem answer
Can you operate a table that has not been locked Sure
Whether the locked table can be operated Handle blocking status , Wait for unlocking , To operate

summary :

Watch reading lock ( Shared lock ) It will only block other threads ( Including myself ) Write to this table , It will not block other threads from reading the table ;

Watch write lock ( Exclusive lock ) It will block the read and write operations of other threads on the table

4. Row lock

1. Concept

Row lock is InnoDB Engine implementation , There are also two types , Shared lock and exclusive lock . In order for row level locks and table level locks to coexist ,InnoDB The intention lock is also used ( Table level locking ) The concept of , There are two kinds of lock: intention sharing lock and intention exclusive lock .


Row locks are implemented by index entries on the index ,InnoDB This line lock implementation feature means : Data is retrieved only through index conditions ,InnoDB To use row level locks , otherwise ,InnoDB Table locks will be used !( Index conditions, such as primary key index 、 Unique index or common index )

2. Exclusive lock of row lock

Add... After query for update, Other operations will be blocked , Until the locked row is committed commit;

select * from surface WHERE id = xxx for update


select for update Usage scenarios of , In order to avoid that the data you see is not the latest data stored in the database, and the data you see can only be modified by yourself , Need to use for update To limit

3. Shared lock of row lock

Find the data plus a share lock , This is to say that other transactions can only be simple for these data select operation , It can't be done DML operation .

select * from surface WHERE id = xxx lock in share mode


In order to ensure that the data they find is not being modified by other transactions , That is to say, make sure that the data you find is the latest data , And no one else is allowed to modify the data . But you don't have to be able to modify the data , Because it's possible that other transactions are also on these data Used in share mode In the way of S lock .

summary :for update and lock in share mode The difference between : The last one is an exclusive lock (X lock ), Once a transaction acquires the lock , Other transactions cannot be performed on this data for update ; The second is shared lock , Multiple transactions can execute on the same data at the same time lock in share mode.

4. View the usage information of row lock

show status like 'innodb_row_lock%';

Query results :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-eNnvpGUl-1640272480992)(E:\ Learning materials \mysql\assets\image-20211223151959164.png)]

The description of each status is as follows :

Innodb_row_lock_current_waits: The number of locks currently waiting ;

Innodb_row_lock_time: The total length of time from system startup to lock up ;

Innodb_row_lock_time_avg: The average time it takes to wait ;

Innodb_row_lock_time_max: The longest waiting time from system startup to now ;

Innodb_row_lock_waits: The total number of times the system has been waiting since it was started ;

5. Pessimistic locking

1. Concept

Every time I go to get the data, I think others will modify it , So every time I get the data, I lock it , So people who want to take this data will block it until it gets the lock .( Shared resources are used by only one thread at a time , Other threads are blocking , Transfer resources to other threads after use )

PS: Pessimistic lock is not a specific “ lock ”, It's a basic concept of concurrent programming , From the perspective of concurrent synchronization .

2. Usage mode

1. The pessimistic lock is realized through the exclusive lock of row lock .

-- Lock to get data 
select * from table_xxx where id='xxx' for update;

Be careful :id Fields must be primary keys or unique indexes , Or lock the watch (id There must be index conditions to use row locks )

2. adopt Lock Interface implementation pessimistic lock

lock() The way to wait is to perform locking , and unlock() The method is to perform unlocking . You must lock and get the lock before processing resources , Wait until it's done before unlocking , This is a very typical pessimistic lock thought .

3. adopt synchronize Realize pessimistic lock

synchronize It is also a sign of pessimism

6. Optimism lock

1. Concept

Every time I go to get the data, I think other people won't modify it , So it won't lock , But when updating the data, you need to judge whether the data has been modified by others . If the data is modified by another thread , No data update , If the data is not modified by another thread , Then update the data . Because the data is not locked , During this period, the data can be read and written by other threads .

2. Usage mode

1. By version number

That is to add a version identifier to the data , In the database is to add a table version Add... To this field every time it is updated 1,
When reading data, put version Read it out , When updating, compare version. If you still start reading version You can update , If the present version Better than the old one version Big , Indicates that other transactions have updated the data , And added the version number , At this time, I get a notice that it can't be updated , The user decides what to do with the notification , Like starting over .

 Example :
-- Query the version number of the data to be modified 
select version from user where id =3;
-- At the time of revision , Verify whether the version number is consistent 
update user set name =' Kobayashi ' ,version = version + 1 where version = 1

7. Deadlock

Deadlock : Two or more processes in the process of execution , A phenomenon of waiting for each other caused by competing for resources , If there is no external force , They will not be able to move forward . At this point, the system is said to be in a deadlock state or the system has produced a deadlock .

The key to deadlock is : Two or more Session The order of locking is different . So the key to solving deadlock is : Make a difference session Lock in order

copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you.