Description:
On page "Locks Set by Different SQL Statements in InnoDB" (https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)
It said that
> DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
The second half sentence is not accurate enough, it takes effect only when applied to the clustered index and secondary not-null unique index. Moreover, delete-marked record is also a special case.
If InnoDB encounter a delete-marked version record or a secondary unique index could contains null, it still set next-key lock. I found some comments in source code(https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/row/row0sel.cc#L4866), as a side proof.
> Note that in a unique secondary index there may be different delete-marked versions of a record where only the primary key values differ: thus in a secondary index we must use next-key locks when locking delete-marked records
> Note above that a UNIQUE secondary index can contain many rows with the same key value if one of the columns is the SQL null. A clustered index under MySQL can never contain null columns because we demand that all the columns in primary key are non-null.
By the way, I am not clear why there may be different delete-marked versions of a record?
How to repeat:
In MySQL 8.0.2
CREATE TABLE `t` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32),
PRIMARY KEY (`id`),
unique KEY `p_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8;
insert into t (name) value ('A'), ('C'), ('D');
### Case 1: secondary unique index contains null
prepare two null records
> insert into t (name) value (null), (null);
> begin;
> delete from t where name is null;
We could see it still acquire next-key lock
> select object_name, index_name, lock_type, lock_mode, lock_status, lock_data from data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| t | NULL | TABLE | IX | GRANTED | NULL |
| t | p_name | RECORD | X | GRANTED | NULL, 11 |
| t | p_name | RECORD | X | GRANTED | NULL, 10 |
| t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 11 |
| t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| t | p_name | RECORD | X,GAP | GRANTED | 'A', 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
### Case 2: delete-marked record
change the type of `name` to NOT NULL
> alter table t modify name varchar(32) not null;
Session 1
> begin;
> delete from t where name = 'C';
Session 2
> begin;
> delete from t where name = 'C'; -- BLOCKED!
From the below we could know that the second delete is waiting next-key lock
> select engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_transaction_id | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 2223 | t | NULL | TABLE | IX | GRANTED | NULL |
| 2223 | t | p_name | RECORD | X | WAITING | 'C', 6 |
| 2222 | t | NULL | TABLE | IX | GRANTED | NULL |
| 2222 | t | p_name | RECORD | X,REC_NOT_GAP | GRANTED | 'C', 6 |
| 2222 | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
