Description:
Refer to the MySQL 8.0 document "17.7.1 InnoDB Locking"
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks:~:text=In%20t...
In the "Gap Locks" section, it states
"Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking."
In earlier versions of MySQL, foreign key (FK) constraint checking held gap locks when deleting records from the parent table. However, the patch titled
"Bug#25082593 FOREIGN KEY VALIDATION DOESN'T NEED TO ACQUIRE GAP LOCK IN READ COMMITTED" has changed this behavior. With this update, FK constraint validation no longer requires gap locks under the Read Committed isolation level.
See https://bugs.mysql.com/bug.php?id=82400
How to repeat:
current version is:
storage/innobase/row/row0ins.cc:1660
if (!skip_gap_lock) {
err = row_ins_set_rec_lock(LOCK_S, LOCK_GAP, block, rec, check_index,
offsets, thr);
}
It skips gap locks in RC.
Suggested fix:
When searching for "InnoDB RC Gap locks" online, I have observed that most responses reference section 17.7.1 InnoDB Locking. These sources indicate that gap locks are necessary for Unique Key (UK) checks and Foreign Key (FK) checks under the Read Committed (RC) isolation level. However, this information appears to be outdated. I strongly recommend that the official documentation be updated to accurately reflect the current behavior of InnoDB gap locking.
