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:47]
Qingping Zhu
[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.