编程知识 cdmana.com

All the MySQL & InnoDB locks you need to know are here

 

 

One 、 Preface

The database uses locks to support concurrent access to shared resources , At the same time, ensure the integrity and consistency of data . among ,MySQL stay Server Layer and the InnoDB The engine designs many types of locking mechanisms , It is used to realize concurrency control in different scenarios , Let's analyze the definitions and usage scenarios of these locks .

Two 、 The type of lock

Scope division

  • Global lock
    1. FTWRL(Flush tables with read lock)
  • Table lock
    1. Metadata lock MDL(meta data lock)
    2. Table locks
    3. Intent locks
    4. AUTO-INC Locks
  • Row-level locks
    1. Record Locks
    2. Gap Locks
    3. Next-Key Locks
    4. Insert Intention Locks

Permissions are mutually exclusive

  • Shared lock
    1. Intention sharing lock IS
    2. Table share lock
    3. Row sharing lock
  • Exclusive lock
    1. Intention exclusive lock IX
    2. It's a lock
    3. Row it lock

 

2.1 Global lock

 

FLUSH TABLES WITH READ LOCK: Closes all open tables and locks all tables for all databases with a global read lock.
This operation is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

Global locking means locking the entire database instance . A global read lock is usually used ——Flush tables with read lock (FTWRL).
Use this command , You can make the entire library read-only , Other threads, regardless of use DML、DDL Even the commit statement of the transaction will not execute normally .
 Insert picture description here
Use scenarios

Make a full library logical backup , Lock all table data , Ensure data consistency .

problem

But when doing a backup, use FTWRL The global locking scheme has serious defects :

  • If you are backing up on the primary database , During the whole backup period, the master database cannot perform any data update operation , Business can't go on , This is unacceptable ;
  • If you are backing up from the library , During the whole backup period, the slave library cannot perform the synchronization from the master library binlog, It will directly lead to master-slave delay .

This scheme is generally used in MyISAM This engine does not support transactions , And for InnodDB Come on , It can be used in master-slave backup mysqldump Parameters **–single-transaction** Start a transaction , utilize MVCC Characteristics of , Get the consistency view data , Ensure data consistency and normal business operation .

2.2 Table lock

2.2.1 Table locks

Watch lock usually refers to Table level S Lock and X lock , The order is  lock tables … read/write. When using lock tables … read when , Any thread can edit the table DDL and DML Will fail ; Use lock tables … write when , Only threads that currently hold a table lock are allowed to read and write to the table .
 Insert picture description here
 Insert picture description here

For those that support row locking InnoDB For the engine , Table level is generally not used S Lock and X lock , So it seems more “ Chicken ribs ”.
In the actual project process , There are often scenes like this , In a table DDL When the table structure changes , Adding, deleting, modifying and querying table records will be blocked ; On the contrary, when adding, deleting, modifying and querying table data , Table structure changes are also not allowed , How to implement it if you don't use table lock ? The answer is : Control through metadata lock .

 

2.2.2 Metadata lock (Meta Data Locks)

 

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).

Meta Data Lock abbreviation MDL, Is in MySQL server A table level lock used by layer , Not at all InnoDB Implemented in the engine . You don't need to explicitly declare

  • When adding, deleting, modifying and querying a table , Will automatically add MDL Read the lock ;
  • When you want to make structural changes to a table , Will automatically add MDL Write lock .

Read read share , Therefore, you can add, delete, modify and query a table at the same time ; Reading and writing are mutually exclusive , Writing mutually exclusive , When multiple threads modify the table structure at the same time , Need to queue for execution . Ensure the safety of table structure change operation .

The compatibility relationship of metadata lock is as follows :

Compatibility MDL Read the lock MDL Write lock
MDL Read the lock compatible Are not compatible
MDL Write lock Are not compatible Are not compatible

 

2.2.3 Auto increment lock (AUTO-INC Locks)

 

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

AUTO-INC Lock is a kind of special watch level lock , When the table uses AUTO_INCREMENT Column time , When inserting data, you need to get AUTO-INC lock .AUTO-INC The scope of a lock is at the statement level , That is, when the insert statement is executed , Even if the whole business is not over ,AUTO-INC The lock will also be released . So there will be : A transaction is holding AUTO-INC When the lock is inserted , The insertion of other transactions will be blocked , So as to ensure that the self increment is continuous .

problem

Use AUTO-INC Locks There will be such a problem : If an insert statement executes too long ( such as insert … select Large amount of data is inserted ), Will result in subsequent insert statements Long blocking time , Overall performance degradation .

Solution

therefore MySQL InnoDB The engine will also use another Lightweight lock ( The mutex ) The way , Obtain the lightweight lock before executing the insert statement , Generate AUTO_INCREMENT Release the lock after the value of , You don't need to wait until the insert statement is completed to release . This way will greatly improve AUTO_INCREMENT Performance of value insertion , But the problem is —— During concurrency, the self incrementing value of the transaction is discontinuous , Master-slave replication may be unsafe .

Use innodb_autoinc_lock_mode The system variable can control which lock is selected for AUTO_INCREMENT assignment

  • innodb_autoinc_lock_mode=0: Unified use AUTO-INC lock
  • innodb_autoinc_lock_mode=2: Unified use of lightweight locks
  • innodb_autoinc_lock_mode=1: When the number of inserted records is determined , Adopt lightweight lock ; Use when unsure AUTO-INC lock

 

2.2.4 Intent locks (Intention Locks)

 

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

  • An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
  • An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

Suppose there is such a scenario : We want to add... To a table X lock , At this time, you must first ensure that the records in the table are not added S Lock and X lock . So how to detect it ? You can loop through whether each record is locked , This method is obviously too inefficient . therefore InnoDB Another special table level lock is designed —— Intent locks . It is used so that the table is added later X Lock or S When locked , It can quickly judge whether the table is locked before recording , So as to avoid detecting the existence of row locks one by one through traversal .

Intention locks are also divided into Intention sharing lock (IS) And the intention to lock it (IX).

  • Intention sharing lock (IS): When the transaction is ready to add... To the table record S When locked , You need to add... To the table first IS lock
  • Intention exclusive lock (IX) : When the transaction is ready to add... To the table record X When locked , You need to add... To the table first IX lock

Table level lock compatibility is as follows :

Compatibility S lock IS lock X lock IX lock
S lock compatible compatible Are not compatible Are not compatible
IS lock compatible compatible Are not compatible compatible
X lock Are not compatible Are not compatible Are not compatible Are not compatible
IX lock Are not compatible compatible Are not compatible compatible

( surface 1)

among ,IS Lock and IX lock 、IS Lock and IS lock 、IX Lock and IX Locks are compatible with each other . How to understand this ?

Just mentioned , Intent lock is used to quickly determine whether the table record is locked , It is convenient to judge whether a transaction can lock a table . That means , Regardless of any transaction, add... To the table record S lock , Still added X lock , Just add the corresponding IS Lock and IX Just lock it , You don't need to care about other things, plus IS Lock or IX lock .

in other words ,IS Lock and IX The lock is only for subsequent addition to the table S Lock or X It only works when locked .

  • IS Locks are not compatible with table level X lock , Compatible table level S lock . It means that... Is added to the record in the table S The lock , It is only allowed to add... To the whole table S lock
  • IX Locks are not compatible with table level X Lock and S lock . The record in the table adds X The lock , It is not only allowed to add... To the whole table S Lock and X lock

 

2.3 Row-level locks

 

If table level locking is used to lock the entire table , As the name suggests, a row level lock is a mechanism for locking in behavioral units .

  • Table lock : The advantage is that the cost of locking is small , Fast , But the granularity of the lock is coarse , The disadvantage is low concurrency performance .
  • Row-level locks : Relatively expensive , Slower , But the granularity of the lock is fine , Higher concurrency performance , More suitable for OLTP Scene .

MySQL Row level locks are implemented by each engine at the engine level . Row level locks are also InnoDB Engine compared to traditional MyISAM One of the advantages of the engine . Let's focus on that InnoDB Type of bank level lock .

2.3.1 Record Locks

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

Record Lock Record lock . but Record Lock Locks are indexed records , Act on clustered index or secondary index . Even if a table has no index defined ,InnoDB A hidden cluster index will also be automatically created and used for record locking , therefore Record Lock Also known as Index record lock .

For the following example :

SELECT c1 FROM t WHERE c1 = 10

Use show engine innodb status Command view :

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;

Record locks are also divided into shared record locks and exclusive record locks , Also follow Read read share , Reading and writing are mutually exclusive , Writing mutually exclusive Principles .

 

2.3.2 Gap Locks

 

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Gap Lock Gap lock . The introduction of clearance lock is a supplement to the record lock . We know MySQL In repeatable reading RR Under isolation level , It can solve most of the unreal reading problems .

Fantasy reading : When a transaction queries the same range twice , The next query sees the rows that the previous query did not see

  • RR Below grade , If snapshot read is used in the transaction ( Also known as consistent reading ) Of , Such as : ordinary select Inquire about , Make use of MVCC A consistent view scheme to avoid unreal reading .
  • RR Below grade , If the current read is used in the transaction , Such as : The lock select Statement and update statement ( The updated data is read first and then written , At this time 【 read 】, You must read the current value , So it is called “ The current reading ”). We can only use the locking scheme to avoid unreal reading .

Suppose there is no gap lock ,MySQL Only use Record Lock Record lock to lock the data , however Record Lock Only works on index row data , There's no way to limit the range of data
For example, the following sentence :

select * from t where id>1 and id<5 for update
( notes : Only in the table id=1 and id=5 These two pieces of data )

stay RR Under isolation level , If only for id=1 and id=5 These two lines of records are locked , There's no way to limit other things in (1,5) Insert new records between this range , So we introduced Gap Lock Gap lock to index rows (1,5) The gap between , Also lock .


For row level locks , What conflicts with row locks is to lock the same row of data and another row lock , The compatibility relationship is as follows :

Compatibility S lock X lock
S lock compatible Are not compatible
X lock Are not compatible Are not compatible

But for clearance locks , There are also shared gap locks and exclusive shared locks between them , however There is no conflict between clearance locks , What conflicts with the clearance lock is : The operation of inserting data into the middle of the gap . This proves once again that the function of gap lock is only to prevent unreal reading problems .

2.3.3 Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Next-Key Lock Namely Record Lock+Gap Lock, Lock line records , And the gap in the middle .
Let's take the following statement as an example :

select * from t where id>1 and id<5 for update ( notes : Only in the table id=1 and id=5 These two pieces of data )

  • Record Lock The scope of the lock is id=1 and id=5
  • Gap Lock The scope of the lock is (1,5)
  • Next-Key Lock The scope of the lock is (1,5]
    ( The locking of record lock and clearance lock is more complex , And isolation level , Whether the index is a secondary index or a clustered index is directly related , Subsequent articles will further analyze )

problem

Clearance lock and next-key lock The introduction of , In order to solve RR Unreal reading occurs at the isolation level . But at the same time, due to locking a wider range , To some extent, it affects the concurrency performance .

Solution

although RR yes MySQL Default isolation level , But many online business systems choose to use RC Read commit as the default isolation level , At the same time binlog_format Set to row. because RC The level is to allow unreal reading to occur , So in most scenarios RC There will be no gap lock ( Foreign key scenarios may use ),binlog_format Set to row To prevent possible inconsistency between data and logs .

2.3.4 Insert intention lock (Insert Intention Locks )

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

When introducing the clearance lock , We know , In an index interval, such as (1,5) With clearance lock , Is unable to insert id=3 and id=4 The data of , Unless the gap lock is released .
When two transactions are executed separately id=3 and id=4 When recording , An intention lock will be inserted into the interval and the lock state is waiting (is_waiting=true), Wait until the clearance lock is released , Insert intent lock status is_waiting=false, Wake up two inserted transactions , And there is no blocking between the two transactions .

  • The intention lock is inserted in INSERT A special clearance lock set during insertion operation , Note that it does not belong to intention lock, but to gap lock .
  • Inserting intent locks is not mutually exclusive , When multiple transactions insert records in the same interval , Just the record itself ( primary key 、 unique index ) No conflict , Then there will be no blocking wait between transactions .

 

3、 ... and 、 Deadlock

 

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.
Deadlock means that each transaction between different transactions holds lock resources that other transactions need to obtain , The situation that makes the transaction unable to continue . Because transactions are waiting for resources to become available , But it won't release the lock it holds .

That is, when different threads execute concurrently, there is a resource dependent loop , The threads involved are waiting for other threads to release resources , It will cause these threads to enter the state of infinite waiting , Deadlock .

After deadlock , There are generally two strategies , The first is :

Do not deal with , Until the lock times out , After the timeout, the transaction will be rolled back to release the lock resources , Other transactions can continue . The lock timeout can be through the parameter innodb_lock_wait_timeout To set up .

innodb_lock_wait_timeout The default value of is 50s, This is for online business , It's hard to accept , If you reduce the timeout , It can also injure other normal operations .

So the second strategy is generally used :

  • Use wait-for graph The algorithm actively initiates deadlock detection , After deadlock is found , Actively roll back a transaction in the deadlock chain ( Generally, the transaction that has the least impact on the row is rolled back ), This releases the lock so that other transactions can continue . The parameter innodb_deadlock_detect Set to on( Default on), Indicates that the logic is turned on .

But if there is “ Hot line ” Updated situation —— Many transactions have to update the data of the same row , At this time, deadlock detection needs to consume a lot of CPU resources , At this point, we must Limit the number of concurrent transactions accessing the same resource .

MySQL Ways to avoid deadlock

1. Lock all required resources at once
2. Lock in the same order
3. Narrow the scope of lock conflicts

  • Avoid long affairs , Disassemble the transaction .
  • When a transaction needs to lock multiple rows , Try to put the lock application operations that are most likely to cause lock conflicts and affect concurrency behind .
  • When the business allows non repeatable reading and unreal reading , Can use RC Isolation level , Avoid deadlock caused by too large locking range of clearance lock .
  • by DML Statement with the appropriate index , Prevent adding locks to each row of the table when the index does not go .

 

Four 、 Summary

 

This paper systematically introduces MySQL&InnoDB Lock mechanism . According to the scope of the lock , It is mainly divided into global locks 、 Watch lock and row lock , Shared locks and exclusive locks define the mutual exclusion of locks . At the same time, the occurrence of deadlock 、 Detection mechanism and how to avoid deadlock .

  • Use shared lock , It can improve the concurrency of read operations ;
  • InnoDB Use row record lock and gap lock , In order to ensure RR Strong consistency resolution at the repeatable read level , The problem of unreal reading ;
  • InnoDB Use insert intent lock , It can improve the performance of insert concurrency ;

 Insert picture description here

版权声明
本文为[CodeMavs]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/10/20211002145410427f.html

Scroll to Top