Introduction to related content of Mysql-InnoDB lock

This article brings you some information about Mysql-InnoDB locks. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Lock type

Row-level lock

The lock modes are only LOCK_S and LOCK_X, and other FLAGs are used for lock description, as mentioned above There are four descriptions: LOCK_GAP, LOCK_REC_NOT_GAP and LOCK_ORDINARY, LOCK_INSERT_INTENTION. (Related recommendations: MySQL Tutorial)

LOCK_REC_NOT_GAP

LOCK_GAP

LOCK_ORDINARY(Next-Key Lock)

LOCK_INSERT_INTENTION

static const byte lock_compatibility_matrix[5][5] = {
/** IS IX S X AI /
/ IS / { TRUE, TRUE, TRUE, FALSE, TRUE},
/ IX / { TRUE, TRUE, FALSE, FALSE, TRUE},
/ S / { TRUE, FALSE, TRUE, FALSE, FALSE},
/ X / { FALSE, FALSE, FALSE, FALSE, FALSE},
/ AI / { TRUE, TRUE, FALSE, FALSE, FALSE}
};

For the GAP type (the lock object is established on supremum or the lock type applied for is LOCK_GAP) and the application is not an insertion intention lock, there is no need to wait for any lock. This is because different Sessions may apply for different types of locks for the same GAP. , and GAP locks themselves are designed not to conflict with each other;
LOCK_ORDINARY or LOCK_REC_NOT_GAP type lock objects do not need to wait for LOCK_GAP type locks;
LOCK_GAP type locks do not need to wait for LOCK_REC_NOT_GAP type lock objects;
Any lock request There is no need to wait for the intent lock to be inserted.

Table-level locks

InnoDB table-level locks include five lock modes: LOCK_IS, LOCK_IX, LOCK_X, LOCK_S and LOCK_AUTO_INC locks, compatibility between locks Follow the definition in the array lock_compatibility_matrix.

The purpose of InnoDB table-level locks is to prevent concurrency problems between DDL and DML. However, after the introduction of MDL locks in version 5.5, the meaning of table-level locks in the InnoDB layer is not that great. The MDL lock itself has covered most of its functions.

Intention locks are table-level. IS and IX locks do not conflict with each other, but conflict with table-level S/X locks.
When adding S lock or X lock to a record, you must ensure that it has a corresponding intention lock or a table-level lock with higher lock strength on the same table.

The above is the detailed content of Introduction to related content of Mysql-InnoDB lock. For more information, please follow other related articles on the PHP Chinese website!