use test;
drop table if exists t1;
create table t1 (id int primary key, value int, value2 int,
value3 int, index(value,value2)) engine=innodb;
insert into t1 values
(10,10,10,10),(11,11,11,11),(12,12,12,12),(13,13,13,13),(14,14,14,14),
(15,15,15,15),(16,16,16,16),(17,17,17,17),(18,18,18,18),(19,19,19,19),
(20,20,20,20);
connect  conn1, localhost, root,,;
connect  conn2, localhost, root,,;
connect  conn3, localhost, root,,;
connection conn1;
use test;
start transaction with consistent snapshot;
connection conn2;
use test;
CREATE PROCEDURE update_t1()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 5000) DO
update test.t1 set value2=value2+1, value3=value3+1 where id=12;
SET i = i + 1;
END WHILE;
END|
set autocommit=0;
CALL update_t1();
select * from t1;
id	value	value2	value3
10	10	10	10
11	11	11	11
12	12	5012	5012
13	13	13	13
14	14	14	14
15	15	15	15
16	16	16	16
17	17	17	17
18	18	18	18
19	19	19	19
20	20	20	20
set autocommit=1;
select * from t1;
id	value	value2	value3
10	10	10	10
11	11	11	11
12	12	5012	5012
13	13	13	13
14	14	14	14
15	15	15	15
16	16	16	16
17	17	17	17
18	18	18	18
19	19	19	19
20	20	20	20
connection conn1;
select * from t1 force index(value) where value=12;
connection conn3;
kill query @id;
connection conn1;
ERROR 70100: Query execution was interrupted
connection default;
disconnect conn1;
disconnect conn2;
disconnect conn3;
drop procedure if exists update_t1;
drop table if exists t1;