MySQL 5.7 Partitioned InnoDB Tables Need More Row Locks Than MySQL 5.6
(Doc ID 2123498.1)
Last updated on MARCH 24, 2020
Applies to:MySQL Server - Version 5.7 and later
Information in this document applies to any platform.
As of MySQL 5.7 series, implementation of partitioning has changed, and storage engines natively implement partitioning with support of common code included in MySQL Server. InnoDB native partitioning is excellent in resource consumption point of view, it consumes less memory and CPU than MySQL 5.6's non native partitioning. However, there is a problem on versions prior to MySQL 5.7.21 that more rows may be locked due to its implementation. To illustrate this issue, please look at the following steps:
1. Create partitioned and non-partitioned tables and populate them
X lock (both record and gap) has held on the rows where a = 3 and a = 4. Other transactions cannot acquire lock the row where a = 4.
The definitive symptom of this issue is that X lock (both record and gap) is acquired on rows which is not accessed by the query but the previous row is accessed.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document