The lock in MySQL Innodb

The lock in MySQL Innodb

Lock is a necessary means to resolve concurrency conflicts, concurrency in MySQL mainly refers to the simultaneous operation of multiple threads on the same database, where different threads may represent different transactions, essentially the same time access to different transactions of the shared resources.

INNODB supports row-level locks and intent locks. Row-level locks are locks on Row records, and row-level locks are divided into two types: a shared lock (S-Lock) and an exclusive (X-lock) lock. An intent lock is a table-level lock, which divides the locked object into layers, meaning that the transaction wants to lock on a finer granularity, and in general, the intent lock is locked from the top down (database, table, page, and so on), any part of which causes the wait, The operation will then need to wait for a coarse-grained lock to complete.

Read Lock operation consistency non-lock read

The default transaction isolation level for MySQL is repeatable read, which is a consistent, non-locked read by default when transaction isolation is repeatable read and read Committed levels. A consistent, non-locking read is the need to wait for an X lock on the row to be released when the row record is accessed. Innodb's consistent non-locking read is implemented in multi-versioning, where a snapshot of the data before the modification is saved as a recovery use.

It is important to note that the repeatable read and read Committed are also different under the two levels of consistency, non-locking reads, repeatable the Read isolation level, always reads the row data at the beginning of the transaction, while the read The most recent snapshot on the row version is always read in Committed mode.

Consistency Lock Read

By default, in repeatable read and read Committed isolation, the SELECT statement reads a row with consistent, non-locked read, and the user can specify the SELECT statement to use a consistent lock read, for example:

1. SELECT ... For UPDATE
2. SELECT ... LOCK in SHARE MODE

These two methods can add an X-lock and an S-lock to the row record, respectively.

Foreign keys and locks

For updates and insertions of a foreign key, you first need to query the records in the parent table, which is to actively add an S lock to the parent table, which must be read with a consistency lock. If you do not do this, when another transaction a deletes the row record of the parent table, and transaction B intends to add a row to the child table that has a foreign key relationship to the parent table, transaction B reads the presence of the foreign key, which is added (read from the snapshot), but the primary key does not exist in the parent table after transaction a commits. This causes the integrity of the parent-child table to be corrupted.

Three ways to lock a row

record lock: A lock on a single row record.

GapLock: Gap locks, which do not contain the record itself.

Next-keyLock: Locks a range and also locks the record itself.

when the queried column is a unique index, Next-key Lock is downgraded to Record lockto improve concurrency efficiency.

The method of using row locks in repeatable read and read Committed is different, under repeatable read, using Next-key Lock, while in the case of Read Committed, Record Lock is used.

The lock in MySQL Innodb