Shulou(Shulou.com)06/01 Report--

The editor will share with you an example analysis of Mysql-InnoDB locks. I hope you will gain a lot after reading this article. Let's discuss it together.

Lock type

Row level lock

The lock mode is only LOCK_S and LOCK_X, and the other FLAG is used to describe the lock, such as LOCK_GAP, LOCK_REC_NOT_GAP, LOCK_ORDINARY and LOCK_INSERT_INTENTION. (recommended: 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, FALSE, TRUE}, / IX / {TRUE, TRUE, FALSE, FALSE, TRUE}, / S / {TRUE, FALSE, TRUE, FALSE, FALSE}, / X / {FALSE, / AI / {TRUE, TRUE, FALSE, FALSE, FALSE}}

For the GAP type (the lock object is established on the supremum or the lock type applied for is LOCK_GAP) and the intended lock is not applied for, there is no need to wait for any lock, this is because different Session may apply for different types of locks for the same GAP, while the GAP lock itself is designed not to conflict with each other

Lock object of type LOCK_ORDINARY or LOCK_REC_NOT_GAP, no need to wait for lock of type LOCK_GAP

Locks of type LOCK_GAP do not need to wait for lock objects of type LOCK_REC_NOT_GAP

Any lock request does not need to wait for the intention lock to be inserted.

Table level lock

InnoDB's table-level locks contain five lock modes: LOCK_IS, LOCK_IX, LOCK_X, LOCK_S, and LOCK_AUTO_INC locks. The compatibility between locks follows the definition in the array lock_compatibility_matrix.

The purpose of InnoDB table-level locking is to prevent concurrency between DDL and DML. However, since the introduction of MDL locks in version 5.5, table-level locks in the InnoDB layer have become less meaningful, and MDL locks themselves have covered most of its functions.

The intention lock is table-level, and the IS and IX locks do not conflict with each other, but conflict with the table-level Splink X lock.

When adding an S lock or an X lock to a record, it must ensure that it has a corresponding intention lock or a table-level lock with higher lock strength on the same table.

After reading this article, I believe you have a certain understanding of the example analysis of Mysql-InnoDB locks, want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.