mysql-(innodb engine)-lock

Article Directory


lock and latch

  • latch
  • Lightweight lock, the required time is very short
  • Divided into mutex (mutual exclusion), rwlock (read-write lock)
  • The purpose is to ensure the correctness of concurrent threads operating critical resources, usually there is no deadlock detection mechanism
  • lock
  • Objects are transactions, used to lock objects in the database
  • Has a deadlock mechanism
  • Compare
Insert picture description here

locks in innodb

Type of lock

Shared lock S: allows transactions to read a row of data

Exclusive lock X: allows transactions to delete or update a row of data


Insert picture description here

Intent lock


  • The InnoDB storage engine supports the design of intent locks which is relatively concise, and its intent locks are table-level locks.
  • Since InnoDB supports row-level locks, intent locks will not actually block any requests other than full table scans


  • In order to reveal the type of lock that will be requested in the next row in a transaction
  • In order to support locking operations on different granular locks

Intentional shared lock IS: A transaction wants to acquire a shared lock on certain rows in a table

Intentional exclusive lock IX: A transaction wants to acquire an exclusive lock on certain rows in a table


Insert picture description here

Consistent non-locking read

  • concept
  • Refers to reading the line data in the point-to-money execution time database through multi-version control
  • If the read row is in del or update operation, the read operation will read the snapshot data of the row without waiting for the release of the lock on the row
  • The read mode is different under each transaction isolation level, even if it is a consistent non-locking read, the definition of the snapshot data is different
  • advantage
  • Greatly improve the concurrency of the database

Consistency lock read

  • In some cases, the user needs to display the lock on the database read operation to ensure the consistency of the data logic
  • Two types of consistent lock read operations supported by innodb for select statements
  • select… for update (X)
  • select… lock in share mode (S)
  • The two statements must be in a transaction, when the transaction is committed, the lock is also released

Self-growth and lock

AUTO-INC Locking

  • Concept: There is an auto-increment timer for each self-incrementing table. When inserting, select MAX(auto_inc_col) from t for update is assigned to the auto-increment column by adding one to this counter
  • A special table lock mechanism is adopted. In order to improve the insert performance, the lock is not released after a transaction is completed, but is released immediately after the SQL inserted into the self-increasing value is completed.
  • problem:
  • The performance of concurrent inserts for columns with self-increasing values ​​is poor, and the transaction must wait for the completion of the previous insert
  • For insert… select a large amount of data insertion will affect the insert performance, because the insert in another transaction will be blocked

Lightweight mutex auto-increment implementation mechanism

Introduce innodb_autoinc_lock_mode to control the self-growth mode

Insert picture description here

Self-increment insert type

Insert picture description here


  • The column of self-increasing value under innodb must be an index, and it must be the first column of the index

Foreign key and lock

  • For foreign keys, if you do not explicitly increase the index, the index will be automatically added, so that table locks can be avoided
  • When inserting or updating foreign keys, you need to select the parent table. When selecting the parent table, use select… lock in share mode to lock the read consistency to ensure data consistency

Lock algorithm

Three algorithms for row locks

  • Record Lock A lock on a single row record
  • Always lock the index record, if no index is set when the table is built, the implicit primary key will be used for indexing
  • Gop Lock gap lock, lock a range, but does not include the record itself
  • Next-Key Lock Gop Lock + Record Lock, lock a range, and lock the record itself
  • Combining the Gop Lock + Record Lock locking algorithm, in order to solve the Phantom Problem (phantom problem)
  • When the query index contains unique attributes, InnoDB will optimize next-key lcok and downgrade it to Record Lock to improve application concurrency

Solve the Phantom Problem

  • concept
  • Phantom Problem means that under the same transaction, executing the same SQL statement twice in a row may lead to different results, and the second SQL statement may return rows that did not exist before.
  • solve
  • Under repeatable read, innodb uses Next-Key Locking mechanism to avoid

Lock problem



Lock upgrade