An overview of the

To support concurrent access to shared resources, ensure data integrity and consistency

Ensure the isolation of the transaction, through table lock, row lock and other ways to achieve the transaction of each isolation level.

classification

The locks in MySQL can be roughly divided into DB-level locks, table-level locks and row-level locks according to the level of the database. Different database engines support different types of locks

Global lock

Locking the entire database instance at the DB level, after locking:

  • The database is read-only
  • Block the addition, deletion and DDL of data

Lock Flush tables with read lock Unlock tables(automatically unlocked when exceptions occur)

The global lock is used for logical backup of the entire database. Compared with set Global Readonly =true, the global lock is automatically released when exceptions occur

MyISAM and InnoDB both support global locking, but InnoDB generally does not use the implementation of multi-version concurrency based on InnoDB transaction support and MVCC. InnoDB can choose mysqldump and add — single-transaction parameter. Do a logical backup of the full library without blocking write operations

Table level lock

Table level locks the entire table. The granularity of the lock is large, the resource consumption is low, the deadlock does not occur, but the concurrency is low. Table level locks have two modes:

  • Table shared lock: operations on the same table do not block read, block write
  • Table exclusive lock: Block read/write operations on the same table

In a database, read and write locks are mutually exclusive, and read and write operations are serial.

  • If a process wants to acquire a read lock and another process wants to acquire a write lock. In mysql, write locks take precedence over read locks
  • The issue of write and read lock priorities can be adjusted with parameters:max_write_lock_countandlow-priority-updates

MyISAM engine supports table level locking by default

  • There are two types of table level locks: table locks
  • Metadata Locking (MDL)

Table locks

  • Lock tables {tb_name} read/write
  • Unlock table {tb_name}

Implicit lock in MyISAM:

  • Execute SELECT query to automatically add shared lock (read lock)
  • INSERT, UPDATA, DELETE automatically add an exclusive lock (write lock)

MyISAM Read/write lock priority: By default, the write lock has a higher priority than the read lock. The write lock is inserted before the read lock even if the read request reaches the wait queue first. However, MyISAM can change the read/write priority based on the production environment by modifying the parameter Settings

Metadata Locking (MDL)

Implicit lock, mainly for DDL operations on table structure change (DDL), no display of lock mode, automatic lock on table access:

  • Executing DML (SELECT, INSERT…). Operation plus shared lock (read lock)
  • DDL (ALTER, DROP…). Operation plus exclusive lock (write lock)

When an implicit MDL write lock is added to a table, all DML operations on the table are blocked

MySQL introduced online DDL after 5.6, that is, MDL write lock will be degraded to read lock during DDL operation, online DML operation will not be blocked, DDL operation will be upgraded back to MDL write lock and released

Check table lock contention: SHOW STATUS LIKE ‘table%’

In general, because of the large granularity of the lock, if the execution time of an item is too long, it is likely to cause all subsequent requests to the table to block

Row-level locks

InnoDB supports row-level locking, with small lock granularity and high concurrency, but high lock overhead and possibly deadlocks. Lock mode:

  • Shared lock (read lock) S: Does not block read operations on the same row, but blocks write operations
  • Exclusive lock (write lock) X: Both read and write operations on the same row block
  • Intentional shared lock IS: an object must obtain the IS of the table before adding an S lock
  • Intentional exclusive lock IX: an object that wants to lock X must first acquire IX of the table

An intent LOCK is a TABLE level LOCK used to indicate that data on the TABLE is being locked or about to be locked. , can directly determine whether there is a lock conflict, without the need to check the lock state line by line

Row-level locking algorithm

Record locks

Only indexes are locked to lock a row

Clearance lock

Lock a closed interval within a range

Next-Key Locks

Combined with record lock and gap lock, innoDB default lock unit (left open and right closed interval).

A deadlock

Lock in the InnoDB

Global lock

Lock Flush tables with read lock Unlock tables(automatically unlocked when exceptions occur)

Table locks

Lock tables {tb_name} Read /write Unlock table {tb_name} unlock table {tb_name}

Row locks

InnoDB’s default isolation level is RR(repeatable read). There are two ways to read data in RR:

  • Snapshot read: under MVCC, a snapshot of the data is taken after the first SELECT statement is executed. The data read is consistent until the end of the transaction. All queries are snapshot reads
  • Current read: The latest version of the data read, and while reading does not allow other things to modify the current record
  • The select… Lock in share mode
  • The select… For update(write lock)

Locking mode:

  • Ordinary select… Query (unlocked)
  • Insert, update, delete… (Implicit write lock)
  • The select… Lock in share mode
  • The select… For update (write lock)

By default, next-key lock is added to the row-level lock. Indexes accessed during the query process will be locked, and there are different locking rules according to different indexes:

  • Unique index equivalent query: next-key lock degenerates to Record lock when the index entry exists; When the index entry does not exist, the default next-key lock is used. When the first value that does not meet the condition is accessed, the next-key lock is degraded to a gap lock

  • Unique index range queries: default next-key lock, (special ‘<=’ range queries until accessing the first value that does not satisfy the condition)

  • Non-unique index equivalent query: default next-key lock. If an index item exists or does not exist, the next-key lock degenerates into a gap lock after the first value that does not meet the condition is accessed

  • Non-unique index range queries: default next-key lock, right access until the first value of the condition is not met

source

MySQL > insert row lock on index Guozizi1718’s blog -CSDN blog

Inside InnoDB technology