| Bug #104054 | Unnecessary next-key lock for partition table | ||
|---|---|---|---|
| Submitted: | 18 Jun 2021 7:47 | Modified: | 24 Jun 2021 11:43 |
| Reporter: | Qingping Zhu | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | CentOS (centos 7.0) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | lock, partition table | ||
[18 Jun 2021 7:54]
Qingping Zhu
MTR test case
Attachment: unnecessary_lock_for_partition_table.test (application/octet-stream, text), 781 bytes.
[18 Jun 2021 12:30]
MySQL Verification Team
Hi Mr. Zhu, Thank you for your bug report. However, this is not a bug. The reason for this not being a bug is very simple. You have very few rows, so the optimiser correctly estimated that it is much faster to scan the table, than to perform the index lookup. It takes at least 20 to 100 rows (depending on the structure), to make index lookup more efficient than scanning all rows. You are also mentioning our internal bug report in this public forum. How have you got that number, when it is not accessible to the public ??? If you think that the mentioned internal bug is a solution for your report, than your report is a duplicate and not the original bug report. Not a bug.
[19 Jun 2021 1:33]
Qingping Zhu
Thanks for your response, I think your analysis maybe inaccurate, so I put more information here.
We have two tables(one normal table, one partition table) with same dataset.
For the same SELECT query, optimizer both choose RANGE scan on primary key, but partition table add an extra X Record Lock on Record "c1=2" compare with non-partition table, which will block other session modify this record. Below is the results on MySQL 8.0.25:
mysql> select version();
+--------------+
| version() |
+--------------+
| 8.0.25-debug |
+--------------+
1 row in set (0.01 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1_p\G
*************************** 1. row ***************************
Table: t1_p
Create Table: CREATE TABLE `t1_p` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 20 | 20 |
| 30 | 30 |
+----+------+
6 rows in set (0.01 sec)
mysql> SELECT * FROM t1_p;
+----+------+
| c1 | c2 |
+----+------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 20 | 20 |
| 30 | 30 |
+----+------+
6 rows in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE c1<=1 FOR UPDATE;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1_p WHERE c1<=1 FOR UPDATE;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1_p | p0 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM t1 WHERE c1<=1 FOR UPDATE;
+----+------+
| c1 | c2 |
+----+------+
| 0 | 0 |
| 1 | 1 |
+----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t1_p WHERE c1<=1 FOR UPDATE;
+----+------+
| c1 | c2 |
+----+------+
| 0 | 0 |
| 1 | 1 |
+----+------+
2 rows in set (0.00 sec)
mysql> SELECT index_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks WHERE object_name = 't1';
+------------+-----------+-----------+-------------+-----------+
| index_name | lock_type | lock_mode | lock_status | lock_data |
+------------+-----------+-----------+-------------+-----------+
| NULL | TABLE | IX | GRANTED | NULL |
| PRIMARY | RECORD | X | GRANTED | 0 |
| PRIMARY | RECORD | X | GRANTED | 1 |
+------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT index_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks WHERE object_name = 't1_p';
+------------+-----------+-----------+-------------+-----------+
| index_name | lock_type | lock_mode | lock_status | lock_data |
+------------+-----------+-----------+-------------+-----------+
| NULL | TABLE | IX | GRANTED | NULL |
| PRIMARY | RECORD | X | GRANTED | 0 |
| PRIMARY | RECORD | X | GRANTED | 1 |
| PRIMARY | RECORD | X | GRANTED | 2 |
+------------+-----------+-----------+-------------+-----------+
4 rows in set (0.00 sec)
The internal bug number that I get from MySQL 8.0.18 Release Notes. It is accessible to the public. Based on the description, I guess maybe it only fixed InnoDB non-partition table, InnoDB partition table still has this issue.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html
InnoDB: An unnecessary next key lock was taken when performing a SELECT...FOR [SHARE|UPDATE] query with a WHERE condition that specifies a range, causing one too many rows to be locked. The most common occurrences of this issue have been addressed so that only rows and gaps that intersect the searched range are locked. (Bug #29508068)
[24 Jun 2021 11:43]
Qingping Zhu
Hi, any progress for this discussion.
Description: For Bug #29508068, MySQL 8.0.18 already fixed the unnecessary next key lock issue, but for partition, this issue still not fixed. Below is the test for partition table on MySQL 8.0.25. CREATE TABLE t1_p(c1 INT NOT NULL PRIMARY KEY, c2 INT) PARTITION BY RANGE COLUMNS (c1) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (50), PARTITION p2 VALUES LESS THAN (MAXVALUE) ); INSERT INTO t1_p VALUES(0, 0); INSERT INTO t1_p VALUES(1, 1); INSERT INTO t1_p VALUES(2, 2); INSERT INTO t1_p VALUES(3, 3); INSERT INTO t1_p VALUES(20, 20); INSERT INTO t1_p VALUES(30, 30); Time session1 session2 T1 BEGIN; SELECT * FROM t1_p WHERE c1<=1 FOR UPDATE; T2 BEGIN; DELETE FROM t1_p WHERE c1=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction How to repeat: CREATE TABLE t1_p(c1 INT NOT NULL PRIMARY KEY, c2 INT) PARTITION BY RANGE COLUMNS (c1) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (50), PARTITION p2 VALUES LESS THAN (MAXVALUE) ); INSERT INTO t1_p VALUES(0, 0); INSERT INTO t1_p VALUES(1, 1); INSERT INTO t1_p VALUES(2, 2); INSERT INTO t1_p VALUES(3, 3); INSERT INTO t1_p VALUES(20, 20); INSERT INTO t1_p VALUES(30, 30); SET autocommit=OFF; connect (con1,localhost,root); --echo Connection: con1 connect (con2,localhost,root); --echo Connection: con2 connection con1; --echo Connection: con1 BEGIN; SELECT * FROM t1_p WHERE c1<=1 FOR UPDATE; connection con2; --echo Connection: con2 BEGIN; --error 1205 DELETE FROM t1_p WHERE c1=2; connection con1; ROLLBACK; disconnect con1; disconnect con2; Suggested fix: SHA-1: 4442ebadcad672b32d6680300e6379667afb7dc8 * Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN This commit should adapt partition table.