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);
use test;
start transaction with consistent snapshot;
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
select * from t1 force index(value) where value=12;
kill query @id;
ERROR 70100: Query execution was interrupted
drop procedure if exists update_t1;
drop table if exists t1;