#start server with --binlog-format=mixed --log-bin --innodb_locks_unsafe_for_binlog --innodb_flush_log_at_trx_commit=0
#run this in multiple threads:  call p1(100000);

#create tables first:
set global innodb_flush_log_at_trx_commit=0;
set global sql_mode='';
drop table if exists t1,t2;
create table t1(a tinyint not null,b tinyint,primary key(b),key(a))engine=innodb;
create table t2(d tinyint not null,unique key(d))engine=innodb;


# the sp definition, using rand to simulate shuffling the order of statements
delimiter $
drop procedure if exists p1 $
create procedure p1(cnt int unsigned)
begin
        declare i int unsigned default 0;
        declare continue handler for sqlexception begin end;
        
        repeat
                set @a=floor(rand()*200);
                set @b=floor(rand()*200);
                set @d=floor(rand()*200);
                
                if floor(rand()*2) = 0 then
                        replace into t2 values (@d);
                end if;
                
                if (floor(rand()*2) = 0) then
                        replace into t1(a,b) values (@a,@b);
                end if;
                
                if floor(rand()*2) = 0 then
                        start transaction;
                end if;
                
                if floor(rand()*2) = 0 then
                        select * from t2 for update;
                end if;
                
                if floor(rand()*2) = 0 then 
                    select * from t2,t1 where t2.d=t1.a or d in(select a from t1 where t2.d > a);
                end if;
                
                if floor(rand()*2) = 0 then commit;
                end if;
                
                set i:=i+1;
        until i>cnt end repeat;

end $

delimiter ;

# mysql -uroot test -e "call p1(100000);" > out