MySQL Bugs: #96013: Lock mode when there is no primary key or index in the table

Bug #96013	Lock mode when there is no primary key or index in the table
Submitted:	27 Jun 2019 9:03	Modified:	27 Jun 2019 15:28
Reporter:	hongyu dong (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi :
When testing MySQL lock, I found a problem:
When the transaction isolation level is RR, when there is no primary key or index in the table, the lock mode is more confused.

When you execute select * t for update, the results displayed in the performance_schema.data_locks table are:
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
| ENGINE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
| INNODB | dhy           | t           | NULL            | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000410         |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000411         |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000412         |
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
5 rows in set (0.00 sec)

From the above results, four next-key locks (lock_mode=x) are added to the records in the table to prevent the occurrence of phantom reads, but the gen_cluster_index generated internally by MySQL is incremented every time, in other words, every time in the table. The insertion is at the end, why id = 10, id = 20, id = 30 these three records, lock_mode is x? I think it should be x, rec_not_gap

How to repeat:
CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
)
insert into t values (10,'donghongyu'),(20,'lichun'),(30,'luoxiaobo');

session1 :
root@localhost:mysql-8.0-3316.sock  16:53:10 [dhy]>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql-8.0-3316.sock  16:54:01 [dhy]>select * from t for update;
+----+------------+
| id | name       |
+----+------------+
| 10 | donghongyu |
| 20 | lichun     |
| 30 | luoxiaobo  |
+----+------------+
3 rows in set (0.01 sec)

in performance_schema.data_locks:
root@localhost:mysql-8.0-3316.sock  16:54:38 [performance_Schema]>select ENGINE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
| ENGINE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
| INNODB | dhy           | t           | NULL            | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000410         |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000411         |
| INNODB | dhy           | t           | GEN_CLUST_INDEX | RECORD    | X         | GRANTED     | 0x000000000412         |
+--------+---------------+-------------+-----------------+-----------+-----------+-------------+------------------------+
5 rows in set (0.00 sec)

Hi Mr. Dong,

Thank you for your bug report.

However, I do not think that this is a bug.

First of all, you have no usable index, so all rows must be locked, since the table is scanned.

Second, since 8.0.16, locks on the pseudo clustered index are also presented in that table.

Third, since 8.0.13, locks are 100 % correctly presented, with the following explanation:

- "X" if the lock is on both record and gap (a.k.a. "Next Key Lock" in our documentation)
- "X,REC_NOT_GAP" if the lock is on record only (a.k.a. "Record Lock")
- "X,GAP" if the lock is on the gap only (a.k.a. "GAP Lock")
- "X,GAP,INSERT_INTENTION" if the lock is an insert intention lock

There are still some complications when the lock is on supremum, in which case we usually don't add ",GAP" suffix, even though the lock behaves like a ",GAP" lock.

As you can see there is no bug. Except for that Supremum behaviour, which is a known internal bug, which will be fixed in the near future.

Hi：
Thank you for your answer, but as you said:
- "X" if the lock is on both record and gap (a.k.a. "Next Key Lock" in our documentation)
- "X,REC_NOT_GAP" if the lock is on record only (a.k.a. "Record Lock")

Thank you for your answer, but as you said:
Although there is no index available, GEN_CLUSTER_INDEX is an incremental value, which is inserted at the end of the table each time. I don't think the next-key lock is needed because the inserted value does not appear in the existing data.

The lock_mode in the data_locks table should be "X, REC_NOT_GAP"

Hi,

When you do UPDATE on the existing index, then you get REC_NOT_GAP, but this is scanning operation. That is a completely different beat and described in our documentation.