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 .

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 .

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 ;

You need to know MySQL&InnoDB Locks are here for more related articles

  1. You should know MySQL Lock of

    background Database lock is a mechanism used to ensure data stability and consistency in the case of multithreading and high concurrency .MySQL Depending on the underlying storage engine , Lock support granularity and implementation mechanism are also different .MyISAM Only table locks are supported ,InnoDB Support row lock and table lock . ...

  2. MySQL InnoDB Locking mechanism

    summary : Lock mechanism is one of the most commonly used mechanisms in programs , When a program needs multiple threads to access the same resource in parallel , To avoid consistency issues , Lock mechanism is usually used to handle . There are the same problems in database operation , When two threads operate on a piece of data at the same time , In order to guarantee the number ...

  3. mysql: About MySQL InnoDB Lock row or lock table ?

          baidu zone - About MYSQL Innodb Lock row or lock table , I delve

  4. [ turn ] About MYSQL Innodb Lock row or lock table

    About mysql Lock row or lock table , This problem , I have a little clue today ,mysql in innodb It's the lock line , But there was a deadlock in the project , Lock the watch . Why? ? Take a look at this article first . At present, due to the need of business logic , It has to be logarithmic ...

  5. You need to know MySQL Open source storage engine TokuDB

    In April Percona Live MySQL The meeting , TokuDB Celebrate the first anniversary of becoming an open source storage engine . I can still remember the official statement and expectation when it was founded a year ago . It was very interesting , Because it has help MySQ ...

  6. mysql innodb Analysis of lock (2)

    Go ahead with yesterday's innodb Lock analysis : notes : Please refer to the address for this blog , It's also very detailed .http://xm-king.iteye.com/blog/770721 mysql There are four levels of transaction isolation , Higher isolation level , Count ...

  7. mysql InnoDB View and analysis of lock waiting

    explain : I've learned before InnoDB About when there is a lock waiting , According to the parameters innodb_lock_wait_timeout Configuration of , Determine whether to proceed timeout The operation of , This document describes the viewing and analysis when a lock wait occurs ...

  8. RDS MySQL InnoDB Processing of lock wait and lock wait timeout

    https://help.aliyun.com/knowledge_detail/41705.html 1. Innodb Engine table row lock waiting and waiting timeout 2.Innodb Processing of engine row lock waiting ...

  9. MySQL InnoDB One of the lock mechanisms Gap Lock、Next-Key Lock、Record Lock analysis

    MySQL InnoDB Three row locking modes are supported : l    Row lock (Record Lock): The lock is applied directly to the index record , What's locked is key. l    Clearance lock (Gap Lock): Lock index record gap , Make sure that the gaps in index records ...

  10. mysql——InnoDB lock

    https://www.cnblogs.com/leedaily/p/8378779.html 1.InnoDB How locks are implemented : Lock index entries , Data is retrieved only through index conditions ,InnoDB To use row level locks , otherwise ,I ...

Random recommendation

  1. css Border shadow

    <style type="text/css">.mydiv{width:250px;height:auto;border:#909090 1px solid;backg ...

  2. AngularJS A profound

    I always watch people say argularjs,knockoutjs,mvvm These patterns , You can't practice the fake trick just by watching , So I made a simple page application on my own small website argularjs , Please refer to  http://www.591j ...

  3. Use WebView Show web page

    Simple page Jump package com.example.webtest; import java.security.PublicKey; import android.support.v7.app.A ...

  4. Android JSON Reading and creating data

    A prepared period of JSON data { "languages":[ {"id":1,"ide":"Eclipse"," ...

  5. hive RegexSerDe View

    EXTERNALkeyword It allows users to create an external table . Construct the actual data in the specified path at the same time in the table (LOCATION).Hive When creating an internal table . Will move the data to the path the data warehouse points to : To create an external table , Record only the data ...

  6. QTP What if the script can't be recorded ?

    QTP Is based on VBS Script language , Most of the VBS Scripts can be found in QTP Up operation , It's just a little bit different in some details , for instance VBS Stop using sleep,QTP On the use of wait.QTP The power of the program lies in the operation of the program window , There are many classes for forms ...

  7. ( turn )Java Developing 23 Detailed explanation of design patterns

    Original text :http://blog.csdn.net/zhangerqing One . Classification of design patterns Generally speaking, design patterns are divided into three categories : Create pattern , Five kinds in total : Factory method model . Abstract factory pattern . The singleton pattern . Builder pattern . Prototype ...

  8. Vue-admin Work arrangement ( eighteen ):Mock How to use

    # Mock Concise documentation           ## Mock.mock()           - Mock.mock( requestUrl?, requestType?, template|funct ...

  9. python Threads 、 coroutines 、I/O Multiplexing

    Catalog : Concurrent multithreading coroutines I/O Multiplexing ( Hang in the air , To be continued ) One . Concurrent multithreading 1. Thread description : The execution of a pipeline is a thread , A production line must belong to a workshop , The running process of a workshop is a process ( At least one in a process ...

  10. oracle Fallback table space cleanup

    1. View existing table spaces , Find the fallback table space SELECT * FROM DBA_TABLESPACES WHERE CONTENTS='UNDO' 2. Create a new fallback table space create undo tables ...