--disable_warnings drop table if exists t1; --enable_warnings # basic tests of semi-consistent reads connect (a,localhost,root,,); connect (b,localhost,root,,); connection a; set binlog_format=mixed; set session transaction isolation level repeatable read; set autocommit=0; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); # this should lock the entire table select * from t1 where a=3 lock in share mode; connection b; set binlog_format=mixed; set session transaction isolation level repeatable read; set autocommit=0; # Test single-table UPDATE -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=10 where a=5; commit; connection a; commit; connection b; # perform a semi-consistent read (and unlock non-matching rows) set session transaction isolation level read committed; update t1 set a=10 where a=5; connection a; -- error ER_LOCK_WAIT_TIMEOUT select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; # semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 set a=11 where a=6; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=12 where a=2; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=13 where a=1; connection a; commit; connection b; update t1 set a=14 where a=1; commit; connection a; select * from t1; # Restore initial content, and test multi-table UPDATE delete from t1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); create table t2 as select 1 as col; commit; # this should lock the entire table select * from t1 where a=3 lock in share mode; connection b; -- error ER_LOCK_WAIT_TIMEOUT # updated table must be first in plan for it to do semi-consistent read: update t1 straight_join t2 set a=10 where a=5; commit; connection a; commit; connection b; flush status; # perform a semi-consistent read (and unlock non-matching rows) set session transaction isolation level read committed; update t1 straight_join t2 set a=10 where a=5; connection a; -- error ER_LOCK_WAIT_TIMEOUT select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; # semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 straight_join t2 set a=11 where a=6; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=12 where a=2; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=13 where a=1; connection a; commit; connection b; update t1 straight_join t2 set a=14 where a=1; commit; # 0 tmp tables: multi-table # UPDATE didn't use any temporary table, which proves that it # updated t1 on the fly. show status like "created_tmp_tables"; connection a; select * from t1; # Restore initial content, and test multi-table UPDATE again, # this time with the internal use of a tmp table to # delay row updates (this exercises different calls to # try_semi_consistent_read()). delete from t1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); commit; # this should lock the entire table select * from t1 where a=3 lock in share mode; connection b; -- error ER_LOCK_WAIT_TIMEOUT # updated table must be first in plan for it to do semi-consistent read. # To force the use of a tmp table for storing rowids of t1, # we make the updates to t2 dependent on the value of t1.a, # which itself is updated. The dummy "+1" in "a+1=5+1" is to avoid constant # propagation which would cancel this dependency. update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a; commit; connection a; commit; connection b; flush status; # perform a semi-consistent read (and unlock non-matching rows) set session transaction isolation level read committed; update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a; connection a; -- error ER_LOCK_WAIT_TIMEOUT select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; # semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 straight_join t2 set a=11,t2.col=t2.col+1 where a+1=6+1 and t2.col>0*a; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=12,t2.col=t2.col+1 where a+1=2+1 and t2.col>0*a; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=13,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a; connection a; commit; connection b; update t1 straight_join t2 set a=14,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a; commit; # 10 tmp tables (5 stmts, 2 tmp tables each): no update was done on the fly. show status like "created_tmp_tables"; connection a; select * from t1; drop table t1,t2; if (0) { # gbi todo delete this portion. # Test different access methods to t1 in the multi-table UPDATE # (table scan, ref, eq_ref, range), to see if they all # do semi-consistent read. create table t1(a int not null, b int not null, primary key(a,b)) engine=innodb; insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1); insert into t1 select a,2 from t1; commit; # this should lock two rows (3,1) and (3,2). select * from t1 where a=3 and b=1 lock in share mode; connection b; # show the access method explain update t1 straight_join t2 set a=10 where a=3 and b=1; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=10 where a=3 and b=1; update t1 straight_join t2 set a=10 where a=3 and b=3; commit; connection a; commit; connection b; # perform a semi-consistent read (and unlock non-matching rows) set session transaction isolation level read committed; update t1 straight_join t2 set a=10 where a=5; connection a; -- error ER_LOCK_WAIT_TIMEOUT select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; # semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 straight_join t2 set a=11 where a=6; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=12 where a=2; -- error ER_LOCK_WAIT_TIMEOUT update t1 straight_join t2 set a=13 where a=1; connection a; commit; connection b; update t1 straight_join t2 set a=14 where a=1; commit; connection a; select * from t1; # clean up drop table t1; } connection default; disconnect a; disconnect b; # Bug 39320 create table t1 (a int, b int) engine=myisam; create table t2 (c int, d int, key (c)) engine=innodb; insert into t1 values (1,1); insert into t2 values (1,2); connect (a,localhost,root,,); connection a; set session transaction isolation level read committed; delete from t1 using t1 join t2 on t1.a = t2.c where t2.d in (1); connection default; disconnect a; drop table t1, t2;