MySQL Bugs: #87619: InnoDB partition table will lock into the near record as a condition in the use

Bug #87619	InnoDB partition table will lock into the near record as a condition in the use
Submitted:	31 Aug 2017 8:54	Modified:	2 Oct 2017 13:41
Reporter:	zongfang zhang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.16 ,5.7.19	OS:	SUSE (SUSE11SP3)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, innodb, lock, partition table, regression, reverse

Description:
When a partition table is updated or deleted according to an index, it will lock the records in the index and the small records adjacent to the index，while non partition table work properly.

How to repeat:
step1:
 create table test12 (name varchar(30),age bigint,grade bigint,date datetime not null,partition_id int not null,  
  PRIMARY KEY (`name`,`PARTITION_ID`),
  KEY `IDX_DATE` (`date`,`name`,`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);
 
step2:
  insert into test12 values('Tom1',20,1,'2017-08-25 12:12:12',1),('Tom2',21,1,'2017-08-25 12:12:12',1),('Tom3',23,1,'2017-08-25 12:12:12',1);

step3:  
  session1:
  begin;update test12 set grade=3,date=now()  where age=23 and partition_id=1;
  
  session2:
  begin;update test12 set grade=1,date=now()  where age=21 and partition_id=1;
  
  session3:
  begin;update test12 set grade=0,date=now()  where age=20 and partition_id=1;
  
  session 2 locked by session 1,session3 locked by session2.
 
  The lock reads as follows 
  _____________________________________________________________
  mysql> select * from information_schema.innodb_lock_waits;
+-------------------+---------------------+-----------------+---------------------+
| requesting_trx_id | requested_lock_id   | blocking_trx_id | blocking_lock_id    |
+-------------------+---------------------+-----------------+---------------------+
| 434783100         | 434783100:12837:4:3 | 434783097       | 434783097:12837:4:3 |
| 434783097         | 434783097:12837:4:4 | 434783092       | 434783092:12837:4:4 |
+-------------------+---------------------+-----------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_locks;
+---------------------+-------------+-----------+-----------+--------------------------------------+------------+------------+-----------+----------+---------------+
| lock_id             | lock_trx_id | lock_mode | lock_type | lock_table                           | lock_index | lock_space | lock_page | lock_rec | lock_data     |
+---------------------+-------------+-----------+-----------+--------------------------------------+------------+------------+-----------+----------+---------------+
| 434783100:12837:4:3 | 434783100   | X         | RECORD    | `test`.`test12` /* Partition `P2` */ | IDX_AGE    |      12837 |         4 |        3 | 21, 'Tom2', 1 |
| 434783097:12837:4:3 | 434783097   | X         | RECORD    | `test`.`test12` /* Partition `P2` */ | IDX_AGE    |      12837 |         4 |        3 | 21, 'Tom2', 1 |
| 434783097:12837:4:4 | 434783097   | X         | RECORD    | `test`.`test12` /* Partition `P2` */ | IDX_AGE    |      12837 |         4 |        4 | 23, 'Tom3', 1 |
| 434783092:12837:4:4 | 434783092   | X         | RECORD    | `test`.`test12` /* Partition `P2` */ | IDX_AGE    |      12837 |         4 |        4 | 23, 'Tom3', 1 |
+---------------------+-------------+-----------+-----------+--------------------------------------+------------+------------+-----------+----------+---------------+
4 rows in set, 1 warning (0.00 sec)
 _______________________________________________________________
  
  I tested 5.7.16-enterprise-commercial-advanced, and also tested 5.7.19-enterprise-commercial-advanced .
  
  This happened when isolation level is set to READ-COMMITTED or REPEATABLE-READ.
  
  Whether the index is a unique index or an ordinary index,whether the command is delete or update, you run into the same problem. 

  The key point of this bug is the index field partition table (unique index, general index) by the reverse method to update, 
an index record with its adjacent record of the corresponding will be locked.

Hi!

What you report is actually a known problem. This is what I get from the InnoDB status:

TRANSACTIONS
------------
Trx id counter 11171
Purge done for trx's n:o < 11170 undo n:o < 0 state: running but idle
History list length 37
Total number of lock structs in row lock hash table 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 11170, ACTIVE 18 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 123145568452608, query id 42 localhost sinisa updating
update test12 set grade=0,date=now()  where age=20 and partition_id=1
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87 page no 5 n bits 72 index IDX_AGE of table `bug`.`test12` /* Partition `P2` */ trx id 11170 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000000015; asc         ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 546f6d32; asc Tom2;;

------------------
---TRANSACTION 11165, ACTIVE 94 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 123145568174080, query id 36 localhost sinisa updating
update test12 set grade=1,date=now()  where age=21 and partition_id=1
------- TRX HAS BEEN WAITING 94 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87 page no 5 n bits 72 index IDX_AGE of table `bug`.`test12` /* Partition `P2` */ trx id 11165 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000000017; asc         ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 546f6d33; asc Tom3;;

-----------------------------------------------------------

This kind of locking is done in the cases where search is done by composite index, while the table is partitioned by a column that is a segment of that index.

However, this makes a fully valid feature requests, so  I am verifying it as a feature request.

Hi,
Thank you for your reply. 
Since it is a known problem, why is it a request feature? 
Do you think it is normal for an index to lock two records? 
 
 "This kind of locking is done in the cases where search is done by composite index, while the table is partitioned by a column that is a segment of that index.
"
What you say is not accurate, whether it is a single index or a composite index, as long as the partition table, in accordance with the reverse access index will encounter the same problem. 

for example:
 
 create table test15 (name varchar(30),age bigint,grade bigint,date datetime not null,partition_id int not null,  
  PRIMARY KEY (`name`,`PARTITION_ID`),
  KEY `IDX_DATE` (`date`),
  KEY `IDX_AGE` (`age`) ) 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 test15 values('Tom1',20,1,'2017-08-25 12:12:12',1),('Tom2',21,1,'2017-08-25 12:12:12',1),('Tom3',23,1,'2017-08-25 12:12:12',1);

 
  session1:
  begin;update test15 set grade=3,date=now()  where age=23 and partition_id=1;
  
  session2:
  begin;update test15 set grade=1,date=now()  where age=21 and partition_id=1;
  
  session3:
  begin;update test15 set grade=0,date=now()  where age=20 and partition_id=1;
  
  You will run into the same problem:session 2 locked by session 1,session3 locked by session2.

In the case of large concurrent business, a large number of lock waiting can result in partial service loss, and serious cases are blocked. We just ran into this problem when business lost and blocked.

Posted by developer:
 
The test case does not reproduce in MySQL 5.6.36.

Jesper,

I agree, it is a bug. I did not test 5.6.

I have also updated our internal database.

Posted by developer:
 
Fixed as of the upcoming 5.7.21, 8.0.4 release, and here's the changelog entry:

 Multiple updates from different clients on a partitioned table caused an
unexpected lock wait timeout due to an incorrectly set lock type.

I checked the fix on 5.7.21 and it seems to be working fine for this example, but in another example where there is an auto increment id to the partitioned table and I try to update without including the partition column in the where clause (but only where id=), the update locks forever.