Description:
On some conditions UPDATE query uses index merge when both indexes expect to retrieve 1 row.
This behavior increases chances for deadlock.
(Corresponding SELECT doesn't show index merge)
How to repeat:
drop table if exists a;
CREATE TABLE `a` (
`ID` int AUTO_INCREMENT PRIMARY KEY,
`NAME` varchar(21),
`STATUS` int,
KEY `NAME` (`NAME`),
KEY `STATUS` (`STATUS`)
) engine = innodb;
set @N=0;
insert into a(ID,NAME,STATUS)
select
@N:=@N+1,
@N%1600000,
floor(rand()*4)
from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c
LIMIT 1600000;
update a set status=5 where rand() < 0.005 limit 1;
explain UPDATE a SET STATUS = 2 WHERE NAME = '1000000' AND STATUS = 5;
+-------------------------+--------------+------------------------------------------------------
| type key | key_len rows | Extra
+-------------------------+--------------+------------------------------------------------------
| index_merge NAME,STATUS | 24,5 1 | Using intersect(NAME,STATUS); Using where; Using temp
+-------------------------+--------------+------------------------------------------------------
Suggested fix:
Do not use index merge when single index is good enough
Try to avoid using index merge in UPDATE to not provoke deadlocks
