MySQL 5.7 Partitioned InnoDB Tables Need More Row Locks Than MySQL 5.6

(Doc ID 2123498.1)

Last updated on SEPTEMBER 21, 2017

Applies to:

MySQL Server - Version 5.7 and later
Information in this document applies to any platform.

Goal

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.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms