Bug #87253 innodb partition table has unexpected row lock

• Submitted: 31 Jul 2017 2:36
• Modified: 22 May 2018 14:53
• Reporter: yx jiang
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S2 (Serious)
• Version: 5.7.18
• OS: CentOS (6.4)
• CPU Architecture: Any
• Tags: innodb partition lock

[31 Jul 2017 2:36] yx jiang

Description:
Table has identical defination without partition part, but on update operation it has different reaction.

Partion table will lock additional row, while non partition table work properly.

How to repeat:
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');

session 1
begin;update t2 set data = '12' where id = 1;

session 2
begin;update t2 set data = '21' where id = 2;

session 2 locked by session 1

If remove partition defination, sessiont 2 will not be locked.

Suggested fix:
N/A

[31 Jul 2017 5:16] yx jiang

This happened when isolation level is set to repeatable read

[31 Jul 2017 15:59] MySQL Verification Team

Indeed.

I changed several parameters, but there is still difference in behaviour between partitioned and non-partitioned table.

Verified as reported.

[16 Apr 2018 6:23] Zhenghu Wen

this bug is very similar with https://bugs.mysql.com/bug.php?id=87619，and the bugfix commit is https://github.com/mysql/mysql-server/commit/2d4bb76bf09df2901cef9324e2264bc2678b32aa。

what different is bug#87619 using no unique key。 and this bug use primary key prefix。both should hold a gap lock，but not next-key lock for next record。

so it seem that we could modify Partition_helper::handle_ordered_next just like Partition_helper::handle_unordered_next in 2d4bb76bf09df2901cef9324e2264bc2678b32aa

[16 Apr 2018 6:27] Zhenghu Wen

this code and mysqltest works  in  my  test

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 87235.patch (application/octet-stream, text), 4.03 KiB.

[16 Apr 2018 12:17] MySQL Verification Team

Thank you for your contribution.

[22 May 2018 14:53] Jon Stephens

Documented fix in the MySQL 5.7.23 and 8.0.13 changelogs as follows:

    An extraneous row lock was imposed by an update to a partitioned
    InnoDB table.

Closed.