Why MySQL 5.7 Partition Tables Slow Down Updates: Uncovering an InnoDB Lock Regression

Author: Jiang Yuxiang, 10 years of core database development at Ctrip, focusing on MySQL internals.

Background

MySQL 5.7 introduced many performance improvements, but after upgrading a test environment from 5.6.21 to 5.7.18 the team observed a noticeable slowdown, especially on partitioned tables, manifested as frequent lock‑timeout errors.

Problem Description

In the upgraded 5.7.18 cluster, updates on partitioned tables caused many lock‑wait events, while the same tables performed normally on 5.6.21. The symptoms were reproduced with the following observations:

Version 5.7.18 + partitioned tables → performance degradation.

Version 5.7.18 + non‑partitioned tables → normal performance.

Version 5.6.21 + partitioned tables → normal performance.

Reproduction Steps

A minimal test case was built to reproduce the issue:

CREATE TABLE `t2` (
  `id` INT(11) NOT NULL,
  `dt` DATETIME NOT NULL,
  `data` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`dt`),
  KEY `idx_dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p20170218 VALUES LESS THAN (736744) ENGINE=InnoDB,
 PARTITION p20170219 VALUES LESS THAN (736745) ENGINE=InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE=InnoDB) */;

INSERT INTO t2 VALUES (1, NOW(), '1');
INSERT INTO t2 VALUES (2, NOW(), '2');
INSERT INTO t2 VALUES (3, NOW(), '3');

Two concurrent sessions were started:

-- Session 1
BEGIN;
UPDATE t2 SET data='12' WHERE id=1;
-- (transaction left open)

-- Session 2
BEGIN;
UPDATE t2 SET data='21' WHERE id=2;

Session 2 blocked indefinitely, even though the rows have different primary‑key values.

Lock Inspection

Querying information_schema.innodb_locks revealed two lock records, showing that the first transaction held a lock on page 3, row 2, preventing the second transaction from proceeding. Rolling back to MySQL 5.6.21 eliminated the blockage.

Root‑Cause Analysis

Debugging the InnoDB source code traced the problem to the function RecLock::lock_add, which adds row locks to the transaction’s lock list. The lock information originates from a cache populated by fill_innodb_locks_from_cache. Further investigation identified the function Partition_helper::handle_ordered_index_scan as the point where the number of rows to lock is determined by m_part_spec.end_part.

Analysis of get_partition_set showed that for each single‑row UPDATE on a partitioned table, InnoDB incorrectly locked a number of rows equal to the total number of partitions, not just the target row. This excessive locking caused the observed lock‑wait cascade.

Validation

Additional rows were inserted to increase the partition count:

INSERT INTO t2 VALUES (4, NOW(), '4');
INSERT INTO t2 VALUES (5, NOW(), '5');

Repeating the concurrent updates on id=1 and id=4 showed that the update on id=4 proceeded without blocking, because the row fell outside the range of partitions locked by the first transaction. In real‑world schemas with dozens or hundreds of partitions, this bug would dramatically increase lock contention.

Conclusion

The investigation confirms a regression in MySQL 5.7 where updates on partitioned tables acquire locks on all partitions, leading to severe performance degradation. The team reported the issue to the MySQL open‑source community, and Oracle acknowledged it as a bug that requires further investigation.