Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Innodb transaction isolation implementation principle, do you understand? Innodb also has Gap locks and next-key locks in addition to row locks. What is the relationship between these locks? We often hear about global locking, table locking, and row locking. What are these? This article will take you through it. Unless otherwise specified in this article, the isolation level is repeatable.

The lock level

There are three types of MySQL locks: global locks, table locks, and row locks.

Global lock

Locks the entire database instance

  • Flush tables with read lock (FTWRL)

  • Unlock command:

  • unlock tables

  • Disconnect the lock session connection

  • Purpose: To make the entire library read-only

  • Rule: If the global lock is successfully locked, all write locks at all levels are blocked. Read locks are not affected

  • Usage scenario: For example, to perform full logical backup

  • Impact: The main library is locked, so any update operation will be blocked and services will stop. The slave library is locked, resulting in master/slave delay

Table level lock

To lock a table, there are three types:

  • Meta Data Lock (META Data Lock, MDL)

  • Innodb: Intentional shared lock (IS) and intentional exclusive lock (IX)

Table locks

  • Lock tables… read/write

  • Unlock command:

  • unlock tables

  • Disconnect the lock session connection

  • Destination: Make the specified table in read-only, write – only state

  • Rules:

Read lock Write lock
Read lock Don’t conflict conflict
Write lock conflict conflict

  • Usage scenario: For example, a storage engine without row locks can be used to handle concurrency problems

  • Impact: The Lock tables syntax limits the ability of other threads to read and write, and also limits the ability of the thread to operate on subsequent objects if the table is set to read-only

MDL

  • Add unlock: MDL does not need to be used explicitly and is added automatically when accessing a table

  • Objective: To ensure read and write correctness and handle conflicts between DDL and DML operations

  • Rule: Automatically add MDL read lock when adding, deleting, modifying or querying a table; MDL write lock is automatically added to tables when structural changes are to be made. MDL read lock and we usually understand the read lock is not the same, add delete change check add is read lock.

MDL read lock MDL write lock
MDL read lock Don’t conflict conflict
MDL write lock conflict conflict

Intent locks

  • Add Unlock: Intent locks do not need to be used explicitly and are automatically added when accessing a table

  • If a transaction wants to assign a shared lock to some rows of the database, it needs to assign an intended shared lock first

  • If a transaction wants to mutex some rows of the database, it must first add the intended mutex

  • Purpose: To implement multi-granularity locking, and table locking conflicts. When row lock is added, intention lock is added at the same time, so that when table lock is added, according to the situation of intention lock, we can quickly determine whether table lock can be added.

  • Rule: Intent locks are not mutually exclusive with row-level shared/exclusive locks

Intent shared lock Intent exclusive lock
Intent shared lock Don’t conflict conflict
Intent exclusive lock conflict conflict

Row-level locks

Locks row records in a table. MySQL row locking is implemented by the engines themselves at the engine level. However, not all engines support row locking, such as MyISAM engine does not support row locking.

  • Select lock in share mode, select for update; Update, INSERT,delete(exclusive lock X)

  • Unlock: Released after the transaction commit

  • Purpose: to do concurrent control

  • Rules:

A Shared lock Exclusive lock
A Shared lock Don’t conflict conflict
Exclusive lock conflict conflict

conflict

I wanted to put all lock conflicts together, but some locks are symbiotic, so I didn’t know how to sort them out. If you have a good suggestion can leave a message to say.

FTWRL Table read lock Table write lock MDL read lock MDL write lock Intent shared lock Intent exclusive lock Line Shared lock Exclusive lock
FTWRL
Table read lock
Table write lock
MDL read lock
MDL write lock
Intent shared lock
Intent exclusive lock
Line Shared lock
Exclusive lock

Lock algorithm

How do you calculate which rows need to be locked? This involves the algorithm of locking.

Three kinds of

A table may have multiple indexes. Depending on the situation, a lock created by a single statement may be placed on one or more indexes.

There are three lock algorithms, which are:

Row lock: A lock on a single row record

Gap Lock: a Gap Lock that locks a range but does not contain the record itself; No conflicts between gap locks; Gap locks are only effective at repeatable read isolation levels

Next key Lock: Gap Lock+Record Lock, locks a range, and locks the Record itself

Locking rules

The following rules summarized by Lin Xiaobin apply to 5.X series <=5.7.24, 8.0 series <=8.0.13.

For two “principles”, two “optimizations” and one “bug”.

  1. Principle 1: The basic unit of locking is next-key lock. Next-key lock is the open before closed interval.

  2. Principle 2: Only objects accessed during lookup are locked.

  3. Optimization 1: equivalent query on index, lock primary key/unique index, find specified row, next-key lock degenerates to row lock.

  4. Optimization 2: the next key lock degrades to a gap lock when the last value does not meet the equivalence condition.

  5. One bug: Range queries on unique indexes access up to the first value that does not meet the criteria.

Even if you don’t look at the code, these rules fit the problem.

  1. Use next-key lock as the basic unit, because it is the widest lock range, if the lock range is large, then delete

  2. If the query is an equivalent query on a primary key/unique index, if the corresponding row is found, it means that there is only this row at most, and it has no problem to degenerate into a row lock, and can also reduce the scope of the lock

  3. If an index is queried for equivalence and the last value does not meet the equivalence condition, the lock is definitely not necessary

  4. Only the accessed objects are locked, because you only need to ensure that the data obtained by the current statement is unchanged when the statement is executed repeatedly. If the statement does not use other indexes, it does not need to worry about other objects

  5. Two things I can’t figure out

  • One is that a range query on a unique index will reach the first value that does not satisfy the condition, and by the nature of a unique index theoretically no further lookups are needed

  • The second is to search the range on the index, such as C >10 && C <15, if the last value does not meet the conditions, theoretically can also degenerate into a gap lock

The instance

Let’s verify the locking rule with an example.

Create table structure and data:

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
 KEY `c` (`c`)
) ENGINE=InnoDB;
 
 insert into t values(0.0.0), (5.5.5), (10.10.10), (15.15.15), (20.20.20), (25.25.25);

Copy the code

Perform equivalent query and range query according to primary key/unique index, normal index, and none index to check the locking status:

Equivalent query Range queries
Primary key/unique index Finally at the primary key, the lock range is (5,10). Select * from (5,10); select * from (5,10); And because it is equivalent query, the last value is 10, not equal to 7. According to optimization 2, it degenerates into gap lock. Finally, the primary key is locked [10,15]. Because from >=10, lock (5,10), according to optimization 1 lock 10, for <11, lock (10,15).It is eventually locked on the primary key (10,20). Because >10, lock (10,15); According to the bug, the first value is accessed until the condition is not met, so the lock continues (15,20).
Normal index Finally at index C, the range (0,10) is locked. Because lookup on index C, according to principle 1, lock (0,5), continue traversing right, lock (5,10); According to optimization 2, degradation is (5,10), and the final locking range is (0,10); Because session A only selects ID, C is an overwrite index, so there is no need to traverse the primary key index. According to principle 2, the primary key index is not locked, so the effect of session B and session C is achieved. Final index C is locked with primary key index (5,15). Because C>=10, lock (5,10), go to the right, lock (10,15), and lock the primary key.
There is no index Think of lock [minus infinity, plus infinity] Think of lock [minus infinity, plus infinity]

According to the analysis of the example, when people lock, try to add the primary key/unique index, and ensure that the value exists. In this case, row locks are added with minimal conflict.

Lock view

The locking schemes of different versions are different. For version 8.0,

  1. Run the select * from performance_schema.datA_locks command to query the performance_schema.datA_locks command.

  2. Lock logs can be viewed:

    show variables like ‘innodb_status_output’;

    set GLOBAL innodb_status_output=ON;

    set GLOBAL innodb_status_output_locks=ON;

    show engine innodb status ;

Phantom read

Gap locks are only effective at repeatable read isolation levels. The Next Key Lock solves the phantom problem. Without these locking algorithms, the number of lines read at the repeat read isolation level may be different, violating the meaning of repeatable reads.

In the official MySQL documentation, unrepeatable reads are defined as Phantom Problem.

Under the next-key Lock algorithm, the index scan not only locks the scanned index, but also locks the gap of the index coverage. Therefore, insertions within this range are not allowed. This avoids the problem of non-repeatable reads caused by other transactions inserting data in this range.

There are two important points that need to be explained:

  1. At the repeatable read isolation level, normal queries are snapshot reads and do not see data inserted by other transactions. Therefore, illusion only appears under “current read”.

  2. Phantom only refers to “newly inserted rows”.

data

  1. Mysql InnoDB engine has 7 lock types.

  2. MySQL global locks and table locks

  3. MySQL database intent locks and metadata locks

  4. Global locks for the mysql lock family

  5. Inside MySQL: InnoDB storage engine

  6. Query lock information about SQL statements in mysql

  7. Mysql sys query lock information _mysql8.0

  8. Query Mysql lock table logs

The last

If you like my article, you can follow my public account (Programmer Malatang)

My personal blog is shidawuhen.github. IO /

Review of previous articles:

  1. Design patterns

  2. recruitment

  3. thinking

  4. storage

  5. The algorithm series

  6. Reading notes

  7. Small tools

  8. architecture

  9. network

  10. The Go