Description:
We cannot use FORCE INDEX for Single-table Syntax of DELETE, but Multiple-table syntax allows
to use single-table, so we can specify DELETE statement with FORCE INDEX like this,
DELETE t FROM t FORCE INDEX (c1) WHERE c1 < 30;
(or DELETE FROM t USING t FORCE INDEX (c1) WHERE c1 < 30;)
When I use this statement only, DELETE statement use an index properly.
But under some condition, DELETE statement don't use an index even if 'FORCE INDEX' is specified.
I report the bad case with rollback.
[Good case]
create table t (c1 int, c2 varchar(10), key (c1)) engine = innodb;
insert into t (c1, c2) values (10, 'a');
insert into t (c1, c2) values (15, 'a');
insert into t (c1, c2) values (20, 'a');
insert into t (c1, c2) values (25, 'a');
insert into t (c1, c2) values (30, 'a');
insert into t (c1, c2) values (35, 'a');
insert into t (c1, c2) values (40, 'a');
insert into t (c1, c2) values (45, 'a');
insert into t (c1, c2) values (50, 'a');
(1) T1(Transaction1)
set autocommit=0;
begin;
mysql> DELETE t FROM t FORCE INDEX (c1) WHERE c1 < 30;
Query OK, 4 rows affected (0.00 sec)
(2) T2(Transaction2)
set autocommit=0;
begin;
mysql> update t set c2 = 'c' where c1 = 35;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Process of (1) use a proper index, so Process of (2) works well.
[Bad case]
After Process of (1),(2). Continue to these processes.
(3) T1
rollback;
begin;
(4) T2
rollback;
begin;
(5) T1
mysql> DELETE t FROM t FORCE INDEX (c1) WHERE c1 < 30;
Query OK, 4 rows affected (0.00 sec)
(6) T2
mysql> update t set c2 = 'c' where c1 = 35;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
I checked about lock status of InnoDB by using innoddb_lock_monitor, Process of (5)
don't use an index and scan a table, so Process (6) wait for the lock to be granted.
[Note]
This problem doesn't occur if INDEX(c1) is Primary Key.
How to repeat:
See Description:
Suggested fix:
[Work around]
At that case, DELETE statement without FORCE INDEX works well. (The optimizer choose a proper index for DELETE statement)
[Suggested fix]
DELETE statement with FORCE INDEX have to use the index specified anytime.
