MySQL Bugs: #87855: INNODB PARTITION TABLE DEAD LOCK

Bug #87855	INNODB PARTITION TABLE DEAD LOCK
Submitted:	25 Sep 2017 13:37	Modified:	11 Apr 2018 13:58
Reporter:	zongfang zhang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.19，5.7.16	OS:	SUSE (suse11sp3)
Assigned to:		CPU Architecture:	Any
Tags:	INNODB PARTITION TABLE DEAD LOCK

Description:
	
The problem has become clear that when the secondary index of the partition table is in the DML operation, any transaction waiting for it (updated than the record it was inserted first) will hold the X lock. 
A deadlock occurs when you go back to session1, repeat execution, or execute secondary indexes to lock other rows of data.

How to repeat:
create table test31 (name varchar(30),age bigint,grade bigint,objectid bigint,date datetime not null,partition_id int not null, 
KEY (`name`,`PARTITION_ID`), 
KEY `IDX_DATE` (`date`,`partition_id`), 
KEY `IDX_AGE` (`age`,`partition_id`) ) PARTITION BY RANGE COLUMNS(PARTITION_ID) 
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB, 
PARTITION P1 VALUES LESS THAN (1) ENGINE = InnoDB, 
PARTITION P2 VALUES LESS THAN (2) ENGINE = InnoDB, 
PARTITION P3 VALUES LESS THAN (3) ENGINE = InnoDB, 
PARTITION P4 VALUES LESS THAN (4) ENGINE = InnoDB, 
PARTITION P5 VALUES LESS THAN (5) ENGINE = InnoDB, 
PARTITION P6 VALUES LESS THAN (6) ENGINE = InnoDB, 
PARTITION P7 VALUES LESS THAN (7) ENGINE = InnoDB, 
PARTITION P8 VALUES LESS THAN (8) ENGINE = InnoDB, 
PARTITION P9 VALUES LESS THAN (9) ENGINE = InnoDB); 

insert into test31 values('Tom1',20,1,1,'2017-08-25 12:12:12',1),('Tom2',20,1,2,'2017-08-25 12:12:12',1),('Tom3',20,1,3,'2017-08-25 12:12:12',1), 
('Tom4',21,1,3,'2017-08-25 12:12:12',1),('Tom5',22,1,3,'2017-08-25 12:12:12',1),('Tom6',23,1,3,'2017-08-25 12:12:12',1), 
('Tom7',24,1,3,'2017-08-25 12:12:12',1),('Tom8',25,1,3,'2017-08-25 12:12:12',1),('Tom9',26,1,3,'2017-08-25 12:12:12',1), 
('Tom10',27,1,3,'2017-08-25 12:12:12',1),('Tom11',28,1,3,'2017-08-25 12:12:12',1),('Tom12',29,1,3,'2017-08-25 12:12:12',1);

step1: 
session1: 
begin;update test31 force index(IDX_AGE) set grade=3,date=now() where age=20 and objectid=3 and partition_id=1; 

step2: 
session2: 
begin;update test31 force index(IDX_AGE) set grade=5,date=now() where age=20 and objectid=1 and partition_id=1; 

step3: 
back session1: 
update test31 force index(IDX_AGE) set grade=3,date=now() where age=20 and objectid=3 and partition_id=1; 

or 

update test31 force index(IDX_AGE) set grade=3,date=now() where age=20 and objectid=2 and partition_id=1;  

DEAD LOCK occurs！！！

MySQL Server 5.7.19 ，MySQL server 5.7.16，has this problem

transaction level:| 
tx_isolation  | READ-COMMITTED |

The same problem with partitioned tables and non partitioned tables.

Hello Zhang,

Thank you for the bug report.
As you know this is already logged internally, I'll just process this as duplicate of internal one Bug#26849141.

Thanks,
Umesh

Posted by developer:

[25 Sep 2017 18:36] Jimmy Yang

This is a typical deadlock scenario, which could be a text example:

In this case, the application scenario forces using a secondary INDEX, which
has two columns and would matches all rows in those update where clause. So
InnoDB will lock all matching INDEX rows at the search phase. So there could
be lock wait on locks placed on this secondary index, if multi-sessions are
searching with non-differential columns. And also easily deadlocks.

When hitting deadlocks frequently, DBA should check their schema design to
see if it is done properly. In this particular case, the force of IDX_AGE is
apparently problematic, when you start to update randomly. So the best is to
add object_id to the index.

There is not a bug from what we can see at the time being.