Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the InnoDB lock modes and lock types in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The composition of ⒈ InnoDB transaction lock

INNODB lock type format is: lock_mode | lock_type

For example: LOCK_X | LOCK_GAP | LOCK_REC, which means that this is a write lock. It locks a gap (what we usually call GAP lock) and locks a record (LOCK_REC).

⒉ InnoDB Lock Mode (lock_mode)

⑴ lock mode type

Lock mode is mainly used to judge the compatibility of locks.

/ * Basic lock modes * /

Enum lock_mode {

LOCK_IS = 0, / * intention shared * /

LOCK_IX, / * intention exclusive * /

The value of LOCK_S, / * shared LOCK_S is 2 seconds /

The value of LOCK_X, / * exclusive LOCK_X is 3 seconds /

LOCK_AUTO_INC, / * locks the auto-inc counter of a table

In an exclusive mode * /

LOCK_NONE, / * this is used elsewhere to note consistent read * /

LOCK_NUM = LOCK_NONE, / * number of lock modes * /

LOCK_NONE_UNSET = 255,

}

⑵ lock mode compatibility matrix

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, FALSE}

/ * AI * / {TRUE, TRUE, FALSE, FALSE, FALSE}

}

For clarity, draw the following matrix diagram (complain: the ITPUB indentation function is so poor that I can't indent the image below):

⒊ InnoDB Lock Type (lock_type)

⑴ InnoDB lock types include the following (lock type represents the scope of the lock, whether it is LOCK_GAP, LOCK_REC_NOT_GAP or LOCK_ORDINARY, and whether the object is record (LOCK_REC), table (LOCK_TABLE) or LOCK_WAIT)

# define LOCK_WAIT 256 / * for lock conflict waiting * /

# define LOCK_ORDINARY 0 / * next-key lock*/

# define LOCK_GAP 512 / * gap Lock * /

# define LOCK_REC_NOT_GAP 1024 / * record lock*/

# define LOCK_INSERT_INTENTION 2048 / * insert intention lock * /

# define LOCK_TABLE 16 / *! < table lock * /

# define LOCK_REC 32 / *! < record lock * /

# define LOCK_MODE_MASK 0xFUL / * Lock mode mask * /

# define LOCK_TYPE_MASK 0xF0UL / * Lock type mask * /

⑵ InnoDB Lock Type conflict Analysis and Matrix

① source code analysis notes:

If ((lock_is_on_supremum | | (type_mode & LOCK_GAP))

& & (type_mode & LOCK_INSERT_INTENTION)) {

/ * GAP locks do not need to wait for any lock type (record lock,next-key lock,insert-intention) * /

Return (FALSE)

}

/ / it cannot be LOCK_GAP, then only LOCK_ORDINARY and LOCK_REC_NOT_GAP do not wait for LOCK_GAP

/ * next-key lock or record lock do not need to wait for the Gap lock * /

If (! (type_mode & LOCK_INSERT_INTENTION) & & lock_rec_get_gap (lock2)) {

Return (FALSE)

}

/ / LOCK_GAP&LOCK_INSERT_INTENSION does not wait for LOCK_REC_NOT_GAP

/ / * GAP lock does not need to wait for LOCK_REC_NOT_GAP lock * /

If ((type_mode & LOCK_GAP) & & lock_rec_get_rec_not_gap (lock2)) {

Return (FALSE);}

/ / * No lock type will wait for the intended lock *

If (lock_rec_get_insert_intention (lock2)) {

Return (FALSE);}

② InnoDB lock type conflict matrix (note that lock type conflicts are usually lock type conflicts in the same lock mode, which is plotted here):

This is the end of the content of "what are the InnoDB lock modes and lock types in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.