Bug #87253 innodb partition table has unexpected row lock
Submitted: 31 Jul 2017 2:36 Modified: 22 May 14:53
Reporter: yx jiang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.7.18 OS:CentOS (6.4)
Assigned to: 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] Sinisa Milivojevic
Indeed.

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

Verified as reported.
[16 Apr 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 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 12:17] Sinisa Milivojevic
Thank you for your contribution.
[22 May 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.