####start server with --transaction-isolation=read-committed

drop table if exists t1;
create table t1(a tinyint not null,b tinyint not null,primary key(b),unique key(a))engine=innodb;


drop procedure if exists p1;
delimiter $
create procedure p1()
begin
  declare continue handler for sqlexception begin end;
  repeat
    set transaction isolation level repeatable read;
    set transaction read only;
    start transaction with consistent snapshot;
    select count(*) cnt,b into @cnt,@a from t1 group by a having cnt>1 limit 1;
    commit;
    if @cnt is not null then
     select * from t1;
    end if;
    set @cnt:=null,@a:=null;
  until 1=2 end repeat;

end $

delimiter ;

call p1();  # run this in 1 or more threads


drop procedure if exists p2;
delimiter $
create procedure p2()
begin
  declare continue handler for sqlexception begin end;
  
  repeat
    set session transaction isolation level read committed;
    start transaction;
    if rand() > 0.5 then insert ignore into t1 values(floor(3*rand()),floor(3*rand()));  end if;
    if rand() > 0.5 then delete from t1 where a=floor(3*rand());  end if;
    commit;
  until 1=2 end repeat;
end $

delimiter ;

call p2();   # run this in 2 or more threads