hello everyone , I'm Kobayashi .
this time , Say MySQL Lock of , Mainly Q&A Form of , It looks easier .
Not much BB Yes , start !
stay MySQL in , According to the scope of locking , It can be divided into global locks , Table lock and row lock .
<> Global lock
How is the global lock used ?
To use a global lock , You have to carry out this life ：
flush tables with read lock
After execution , The entire database is read-only , At this time, other threads perform the following operations , Will be blocked ：
* Add, delete, query and modify data , such as insert,delete,update Equal statement ;
* Changes to table structure , such as alter table,drop table Equal statement .
If you want to release the global lock , Then execute this command ：
of course , When the session is disconnected , The global lock is automatically released .
What is the global lock application scenario ?
The global lock is mainly used for full database logical backup , This is done during the backup of the database , Not because of the update of data or table structure , The data of the backup file is not the same as expected .
For example, you will know .
During a full library logical backup , Suppose there is no global lock , See what happens .
If during a full library logical backup , A user bought a product , Generally, the business logic of purchasing goods involves more details of multiple database tables , For example, update the balance of the user in the user table , Then, the inventory of the purchased goods is updated in the goods table .
that , This order is possible ：
* The data of the user table is backed up first ;
* Then a user initiated the purchase of goods ;
* Then back up the data of the commodity table .
That is, between the backup user table and the product table , Some users have purchased goods .
In this case , The result of the backup is that the balance of the user in the user table has not been deducted , Instead, the inventory of the commodity in the commodity table is reduced , If you use this backup file to restore the database data later , The user has no less money , And the inventory is low , Equal to the user whoring a commodity in vain .
therefore , During a full library logical backup , Add global lock , This would not have happened .
What disadvantages will global locking bring ?
Add global lock , This means that the entire database is read-only .
So if there's a lot of data in the database , Backup takes a lot of time , The key is during backup , Business can only read data , Data cannot be updated , This will cause business stagnation .
Since when backing up database data , Using global locks will affect the business , Is there any other way to avoid it ?
yes , we have , If the transaction supported by the database engine supports a repeatable read isolation level , Then start the transaction before backing up the database , Will be created first Read View, This is then used throughout the transaction
Read View, And because MVCC Support of , During backup, the business can still update the data .
Because at the repeatable read isolation level , Even if other transactions update the data of the table , It also does not affect the performance when backing up the database Read
View, This is the isolation of the four characteristics of transactions , In this way, the data backed up during the backup is always the data when the transaction is started .
The tool for backing up the database is mysqldump, in use mysqldump Add when –single-transaction
Parameter , The transaction will be started before backing up the database . This approach applies only to support 「 Repeatable read isolation level transactions 」 Storage engine for .
InnoDB The default transaction isolation level of the storage engine is readability , Therefore, the database can be backed up in this way .
however , about MyISAM This engine does not support transactions , When backing up the database, you should use the global lock method .
<> Table lock
MySQL What are the table level locks ? How to use it .
MySQL There are several table level locks ：
* Watch lock ;
* metadata lock （MDL）;
* Intent lock ;
* AUTO-INC lock ;
<> Watch lock
Let's talk about it first *** Watch lock ***.
If we want to make a list of students （t_student） Add watch lock , You can use the following command ：
// Table level shared locks , That is, read lock ; lock tables t_student read; // Table level exclusive lock , That is, write lock ; lock tables
It should be noted that , In addition to restricting the reading and writing of other threads, table locks , It will also restrict the next read and write operations of this thread .
That is, if this thread adds 「 Shared table lock 」, Next, if this thread wants to execute a write operation on the student table , It will be blocked , Of course, other threads will also be blocked when they write to the student table , Until the lock is released .
To release the table lock , You can use the following command , All table locks of the current session will be released ：
in addition , When the session exits , All table locks will also be released .
However, try to avoid using InnoDB The engine's tables use table locks , Because the granularity of the watch lock is too large , Will affect concurrency performance ,InnoDB The key point is to realize the row level lock with finer granularity .
<> metadata lock
Let's talk about it again *** metadata lock （MDL）***.
We don't need to show the use of MDL, Because when we operate on database tables , Will automatically add MDL：
* To a table CRUD During operation , Plus MDL Read lock ;
* When changing the structure of a table , Plus MDL Write lock ;
MDL This is to ensure that when the user executes on the table CRUD During operation , Prevent other threads from making changes to the table structure .
When a thread is executing select sentence （ plus MDL Read lock ） Period of , If another thread wants to change the structure of the table （ apply MDL Write lock ）, Then it will be blocked , Until the execution is completed
select sentence （ release MDL Read lock ）.
conversely , When a thread changes the table structure （ plus MDL Write lock ） Period of , If another thread executes CRUD operation （ apply MDL
Read lock ）, Then it will be blocked , Until the table structure change is completed （ release MDL Write lock ）.
MDL There is no need to display the call , When was it released ?
MDL Is released only after the transaction is committed , This means that during transaction execution ,MDL It's always held .
What if the database has a long transaction （ The so-called long affair , Is to start the transaction , But it hasn't been submitted yet ）, When changing the table structure , Unexpected things may happen , For example, the following sequence of scenarios ：
* first , thread A Transaction enabled first （ But never submitted ）, Then execute one select sentence , At this point, add the MDL Read lock ;
* then , thread B The same procedure was carried out select sentence , It is not blocked at this time , because 「 Read 」 No conflict ;
* next , thread C Table fields modified , At this time, due to thread A The transaction was not committed , that is MDL The read lock is still occupied , At this time, the thread C You can't apply MDL Write lock , Will be blocked ,
So in the thread C After blocking , There are subsequent changes to this table select sentence , Will be blocked , If there are a large number of the table at this time select
Statement request arrival , A large number of threads will be blocked , At this time, the threads of the database will soon be full .
Why threads C Because I can't apply MDL Write lock , The subsequent query operations that cause the read lock application will also be blocked ?
This is because of the application MDL Lock operations form a queue , In the queue, write lock acquisition priority is higher than read lock , Once it appears MDL Write lock wait , Will block all subsequent of the table CRUD operation .
Therefore, in order to safely change the table structure , Before changing the table structure , First look at the long transactions in the database , Has any transaction been added to the table MDL Read lock , If you can consider kill
Drop this long transaction , Then change the table structure .
<> Intent lock
next , say something *** Intent lock ***.
* in use InnoDB Some records are added to the engine's table 「 Shared lock 」 before , You need to add one at the table level first 「 Intent sharing lock 」;
* in use InnoDB Some records are added to the engine's table 「 Exclusive lock 」 before , You need to add one at the table level first 「 Intent exclusive lock 」;
that is , When inserting , to update , Delete operation , You need to add to the table first 「 Intent exclusive lock 」, Then apply an exclusive lock to the record .
And ordinary select Row level locks will not be added , ordinary select Statement is to use MVCC Achieve consistent read , It's unlocked .
however ,select You can also add shared locks and exclusive locks to records , The specific methods are as follows ：
// First, add the intention sharing lock to the table , Then apply an exclusive lock to the read record select ... lock in share mode;
// First, add the intent exclusive lock to the table , Then apply an exclusive lock to the read record select ... for update;
Intentional shared locks and intentional exclusive locks are table level locks , Does not conflict with row level shared and exclusive locks , And there will be no conflict between intention locks , Only table locks are shared with （lock tables … read
） And exclusive table locks （lock tables … write） Conflict .
Table lock and row lock meet the requirements of reading and sharing , Read write mutual exclusion , Write mutually exclusive .
without 「 Intent lock 」, Then add 「 Exclusive table lock 」 Time , You need to traverse all the records in the table , Check whether there are records with exclusive locks , This will be slow .
So there is 「 Intent lock 」, Because before applying an exclusive lock to the record , An intent exclusive lock at the table level will be added first , So what are you doing 「 Exclusive table lock 」 Time , Directly check whether the table intends to exclusive lock , If yes, it means that there are records in the table that have been locked exclusively , So you don't have to traverse the records in the table .
therefore , The purpose of intent lock is to quickly judge whether records in the table are locked .
last , say something AUTO-INC lock .
Declare for a field AUTO_INCREMENT Property time , You can then insert data , You may not specify a value for this field , The database automatically assigns an incremental value to this field , This is mainly through
AUTO-INC Lock implementation .
AUTO-INC Locking is a special table locking mechanism , The lock is not released after another transaction is committed , Instead, it will be released immediately after the insert statement is executed .
When inserting data , A table level will be added AUTO-INC lock , Then for being AUTO_INCREMENT The decorated field is assigned an increasing value , After the insert statement is executed , Will put
AUTO-INC The lock is released .
that , A transaction is holding AUTO-INC In the process of locking , Other transactions will be blocked if they want to insert statements into the table , This ensures that when inserting data , cover AUTO_INCREMENT
The value of the decorated field is incremented continuously .
however , AUTO-INC When the lock inserts a large amount of data , Will affect insertion performance , Because inserts in another transaction are blocked .
therefore , stay MySQL 5.1.22 Version start ,InnoDB The storage engine provides a lightweight lock to realize self increment .
The same is true when inserting data , Will be AUTO_INCREMENT Decorated fields with lightweight locks ,
Then assign a self increasing value to the field , Just release this lightweight lock , Instead of waiting for the entire insert statement to execute before releasing the lock .
InnoDB The storage engine provides innodb_autoinc_lock_mode System variables for , It is used to control selection AUTO-INC lock , Or a lightweight lock .
* When innodb_autoinc_lock_mode = 0, Just adopt AUTO-INC lock ;
* When innodb_autoinc_lock_mode = 2, Lightweight locks are used ;
* When innodb_autoinc_lock_mode = 1, This is the default , Mixed use of two locks , If the number of records inserted can be determined, a lightweight lock is used , Use when uncertain
AUTO-INC lock .
however , When innodb_autoinc_lock_mode = 2
Is the highest performance way , But it will bring some problems . Because of the existence of concurrent inserts , At each insertion , Self increasing values may not be continuous , This is not safe in scenarios with master-slave replication .
What are row level locks ?
InnoDB The engine supports row level locks , and MyISAM The engine does not support row level locks .
There are three types of row level locks ：
* Record Lock, Record lock , That is, just lock a record ;
* Gap Lock, Clearance lock , Lock a range , But not the record itself ;
* Next-Key Lock：Record Lock + Gap Lock Combination of , Lock a range , And lock the record itself .
As mentioned earlier , ordinary select Statement does not lock records , If you want to add row locks to records during query , You can use the following two methods ：
// Apply a shared lock to the read record select ... lock in share mode; // Exclusive lock on read records select ... for update;
The above two statements must be in another transaction , When the transaction is committed , The lock will be released , Therefore, when using these two statements , To add begin,start transaction perhaps set
autocommit = 0.
Which records are locked , Just like the specific select The statement has something to do with it , More complex , I'll leave this to the next chapter .
reference material ：《MySQL Technical insider ：innodb》,《MySQL actual combat 45 speak 》,《 Understand from the root MySQL》.
See you next time ～