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 :
unlock tables
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
t_stuent wirte;
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 :
unlock tables
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 .

<>AUTO-INC lock

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 ~

Technology
©2019-2020 Toolsou All rights reserved,
java Comparing attribute values between two objects utilize Python handle Excel data ——xlrd,xlwt library Bidirectional linked list Why? Python Not a future oriented programming language ?Python【 Assignment statement 】 Special lecture , You can't just a=b ah ! Suggest mastering ! utilize Python handle Excel data ——pandas library see SQL-SERVER Data volume and occupied space of database and each table PID The algorithm finally figured out the principle , It was so simple web Two front-end practical games ( Attached source code ) Beginners learn Python Be sure to know what his basic algorithms are ? What is the function ?