preface

Gap locking was mentioned in the previous MySQL series, but I felt I didn’t explain it clearly. It was just an introduction to gap locking, so let’s record it again.

Portal: juejin.cn/post/698622…

MVCC solves the phantom read problem of snapshot read, while the phantom read problem of current read needs Gap lock + row lock to solve.

A, Gap lock (Gap lock) concept

Innodb supports three row locking modes:

  1. Row lock: The lock is the index, if the SQL does not walk the index, then the full table scan, thereby upgrading to the table lock.

    • PS: What if a table has no index?

      • If there is a primary key, Innodb will use it as the cluster index.

      • If there is no primary key, Innodb selects the first unique index that does not contain a NULL value as the primary key index

      • If there is no primary key and no unique index, Innodb selects the built-in rowId (Innodb internally maintains an increasing rowId for each row of data) as the cluster index.

  2. Gap lock: When we request a shared or exclusive lock to retrieve data using a range condition rather than an equality condition, InnoDB will add a row lock to an index entry of an existing data record that meets this condition. Gap locks are applied to records whose key values are within the condition range but do not exist. Innodb introduced the locking mechanism to solve the magic Read problem, so it is only available in Read Repeatable and Serializable isolation levels. To cancel gap locking, switch the isolation level to read committed.

    • PS: InnoDB will also use gap locks when requesting a non-existent record with an equal condition!

  3. Next-key Lock: When a row Lock is combined with a gap Lock, it is called a next-key Lock.

Snapshot read and current read

Snapshot read: this is a normal select statement that does not include select statements for update or lock in share mode.

  • The current read is implemented through the MVCC mechanism (uoDO_log + readView), the previous part has said juejin.cn/post/698622…

Current read: reads the latest data, and needs to obtain the lock of the corresponding record, contains the following SQL types:

  • select … lock in share mode 

  • select … for update

  • Add/delete/modify (MySQL locks by default)

The current read is implemented by next-key Lock (row Lock + gap Lock)

Here’s an example:

The original data of the database is shown in the following figure

Open two things to operate at RR (repeatable Read) isolation level:

Yi. SQL1 is a snapshot read, SQL4 is a current read, and RR isolation level, still a magic read occurs in the same transaction.

MVCC solves the phantom read problem of snapshot read and gap lock solves the current read problem. Why do I still have a phantom reading problem here?

MySQL is currently reading the first SQL in a transaction. MySQL is currently reading the first SQL in a transaction. MySQL is currently reading the first SQL in a transaction. You must wait until the first transaction releases the lock before any other transaction can add or delete the corresponding record. The phantom read problem for the current read was resolved, but performance was affected.

Three, the clearance lock instance analysis

Create a user table with the following records:

MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; MySQL > delete from user where age=3; As shown in figure:

For another example, the raw data looks like this:

Update user set money=100 where id>=5 update user set money=100 where id>=5 update user set money=100 where id>=5 A row lock is applied to a row with id=5 and a gap lock is applied to a row with ID >5 that does not yet exist.

Deadlock caused by Gap lock

Historical data are shown in the following figure:

Open two transactions to operate:

Causes of deadlock:

  1. Data for age=5 does not exist, i.e. gap lock is applied to age (2,7).

  2. Data for age=8 does not exist, i.e. gap lock is applied to age (7,9).

  3. MySQL > insert age=8; MySQL > insert age=8; MySQL > insert age=8; MySQL > insert age=8; MySQL > insert age=8; MySQL > insert age=8;

  4. MySQL > insert age=5 (age=5); MySQL > insert age=5 (age=5); MySQL > insert age=5 (age=5); A deadlock occurs.