Classification and algorithm of MySQL lock

The MySQL lock contains table-level locks and row-level locks in two large categories. Table-level locks are intent locks, have ix,is, are generally used before row-level locks, and row-level locks are traditional s and x locks. The compatibility features of these locks are as follows:

is

		IX	S	X
Is	Compatible	Compatible	Compatible	Not compatible
Ix	Compatible	Compatible	Not compatible	Not compatible
S	Compatible	Not compatible	Compatible	Not compatible
X	Not compatible	Not compatible	Not compatible	Not compatible

There are 3 types of row lock algorithms in the InnoDB storage engine, namely:
* Record lock single Row Records locked

• Gap Lock gap Lock, locks a range but does not contain the record itself
• Next-key Lock Gaplock+recordlock

Record lock is easier to understand, gaplock need to be aware that it locks the range, including both the left and right gaps. The role of GAP lock is to prevent multiple transactions from inserting records into the same range, resulting in phantom reads.

Classification and algorithm of MySQL lock