Bug #102287 Miss some corner cases on Locking of DELETE FROM ... WHERE...
Submitted: 19 Jan 3:07 Modified: 20 Jan 13:12
Reporter: Jacky Jacky Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: 5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Jan 3:07] Jacky Jacky
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         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
[19 Jan 16:03] MySQL Verification Team
Hi Mr. Jacky,

Thank you for your documentation bug report.

We must note that your report is quite unclear.

Can you summarise where exactly our documentation is wrong and how should it be exactly worded ???

Thank you very much, in advance.
[20 Jan 1:38] Jacky Jacky
The second half sentence "However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row." is a little misleading. 

It should be

> However, only an index record lock is required for statements that lock non delete-marked rows using a unique index to search for a unique row.  

  Note that next-key lock is still required when locking delete-marked rows on secondary unique index; Moreover, when one of the columns in a unique secondary index is null, next-key lock is also required as there may be many rows with the same key value.
[20 Jan 13:12] MySQL Verification Team
Hi Mr. Jacky,

Thank you for your feedback.

Your correction of our description is very much welcome.

Verified as reported.