Window 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table1 where id > 99 FOR UPDATE;
+-----+--------+--------+-------+------+
| id | field1 | field2 | field | a |
+-----+--------+--------+-------+------+
| 101 | hola | NULL | NULL | NULL |
| 103 | yo | NULL | NULL | NULL |
| 107 | hey | NULL | NULL | NULL |
+-----+--------+--------+-------+------+
3 rows in set (0.00 sec)
Window 2:
mysql> SELECT thread_id, event_id,
-> object_schema, object_name, index_name,
-> lock_type, lock_mode, lock_status, lock_data
-> FROM performance_schema.data_locks
-> WHERE thread_id = 279\G;
*************************** 1. row ***************************
thread_id: 279
event_id: 28
object_schema: laravel_doc
object_name: table1
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_status: GRANTED
lock_data: NULL
*************************** 2. row ***************************
thread_id: 279
event_id: 28
object_schema: laravel_doc
object_name: table1
index_name: PRIMARY
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: supremum pseudo-record
*************************** 3. row ***************************
thread_id: 279
event_id: 28
object_schema: laravel_doc
object_name: table1
index_name: PRIMARY
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: 101
*************************** 4. row ***************************
thread_id: 279
event_id: 28
object_schema: laravel_doc
object_name: table1
index_name: PRIMARY
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: 103
*************************** 5. row ***************************
thread_id: 279
event_id: 28
object_schema: laravel_doc
object_name: table1
index_name: PRIMARY
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: 107
5 rows in set (0.00 sec)
Original table:
+-----+--------+--------+-------+------+
| id | field1 | field2 | field | a |
+-----+--------+--------+-------+------+
| 3 | hello | 1 | NULL | NULL |
| 97 | hi | 2 | NULL | NULL |
| 101 | hola | 3 | NULL | NULL |
| 103 | yo | 4 | NULL | NULL |
| 107 | hey | 5 | NULL | NULL |
+-----+--------+--------+-------+------+
Gap locks don't appear, they should be displayed because I checked and it prevents me from INSERT for example with id = 102 (in window 2)
"id" is the primary key
For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
Also, they should appear because is an unique index BUT A RANGE SEARCH, not a unique search condition
In fact they are, they are just not visible to the naked eye:
Record X = Record lock + gap lock = Next-key lock
https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
S → is like a combination of S,REC_NOT_GAP and S,GAP at the same time. So it is a shared access right to the row, and prevents insert before it.
X → is like a combination of X,REC_NOT_GAP and X,GAP at the same time. So it is an exclusive access right to the row, and prevents insert before it.
https://xhinliang.win/2021/09/backend/innodb-locks/
LOCK_MODE has several options
IX -> Intention Exclusive Lock IS -> Intention Share Lock X,REC_NOT_GAP -> Exclusive Record Lock X,GAP -> Exclusive Gap Lock X -> Exclusive Next-Key Lock S,REC_NOT_GAP -> Share Record Lock S,GAP -> Share Gap Lock S -> Share Next-Key Lock