MySQL 5.7 Partitioned InnoDB Tables Need More Row Locks Than MySQL 5.6 (Doc ID 2123498.1)

Last updated on MARCH 28, 2017

Applies to:

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

Goal

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, 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