Bug #88600 Update use index merge (unique and index)--deadlock

• Submitted: 22 Nov 2017 7:50
• Modified: 23 Nov 2017 13:32
• Reporter: xu wen
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.6.29-76.2-log
• OS: Any
• CPU Architecture: Any

[22 Nov 2017 7:50] xu wen

Description:

On some conditions , UPDATE query uses index_merge when one key is unique and  another key is index;
eg:

explain  update order_test set last_updated=now() where 
order_no = "a8fb22d3cf4c11e7be620" and  last_updated="2018-05-17 14:46:42";

When the same 'last_updated' value, increases chances for deadlock.

How to repeat:

//create table
drop table if exists order_test;
CREATE TABLE `order_test` (
  `id` int  AUTO_INCREMENT PRIMARY KEY,
  `order_no` varchar(32),
  `last_updated` datetime,
  unique `order_no_idx` (`order_no`),
  index `last_update_idx` (`last_updated`)
) engine = innodb;

//insert data
DELIMITER ;; 
CREATE PROCEDURE batch_insert() 
BEGIN 
DECLARE y int DEFAULT 1;
WHILE y<6000000
DO
insert into order_test(order_no,last_updated)  values (replace(uuid(), '-', ''),"2011-05-17 14:46:42.0"); 
SET y=y+1; 
END WHILE ; 
commit; 
END;; 
CALL batch_insert();

//test
explain  update order_test set last_updated=now() where 
order_no = "a8fb22d3cf4c11e7be620" and  last_updated="2018-05-17 14:46:42";

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'order_test', 'index_merge', 'order_no_idx,last_update_idx', 'order_no_idx,last_update_idx', '99,6', NULL, '1', 'Using intersect(order_no_idx,last_update_idx); Using where; Using temporary'

Suggested fix:

When index is unique,use unique replace index_merge;

[22 Nov 2017 14:49] MySQL Verification Team

Hi!

I have tested bug in the latest 5.7 and it is not there. Here is the extended EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	order_test	NULL	range	order_no_idx,last_update_idx	order_no_idx	35	const	1	100.00	Using where

Changes made in the optimiser are too large to be backported into 5.6. Hence, you can use 5.7 instead, which is also production quality version.