1eval set session autocommit=$autocommit; 2let $is_gaplock_target = `SELECT @@autocommit = 0 && '$expect_gap_lock_errors' = 1`; 3 4if ($is_gaplock_target) 5{ 6## single-table insert,update,delete 7insert into gap1 (id1, id2, id3) values (-1,-1,-1); 8insert into gap1 (id1, id2, id3) values (-1,-1,-1) 9 on duplicate key update value=100; 10--error ER_UNKNOWN_ERROR 11update gap1 set value=100 where id1=1; 12update gap1 set value=100 where id1=1 and id2=1 and id3=1; 13--error ER_UNKNOWN_ERROR 14delete from gap1 where id1=2; 15delete from gap1 where id1=-1 and id2=-1 and id3=-1; 16commit; 17 18## multi-table statements (preventing all gap locks with autocommit) 19# insert into select 20--error ER_UNKNOWN_ERROR 21insert into gap2 select * from gap1; 22--error ER_UNKNOWN_ERROR 23insert into gap2 select * from gap1 where id1=1; 24insert into gap2 select * from gap1 where id1=1 and id2=1 and id3=1; 25 26# create table select 27create table t4 select * from gap1 where id1=1 and id2=1 and id3=1; 28drop table t4; 29--error ER_UNKNOWN_ERROR 30create table t4 select * from gap1; 31--error ER_UNKNOWN_ERROR 32create table t4 select * from gap1 where id1=1; 33 34# update join 35update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3 36 and gap2.id2=3 and gap2.id3=3; 37--error ER_UNKNOWN_ERROR 38update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3; 39--error ER_UNKNOWN_ERROR 40update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 join gap3 on gap1.id1=gap3.id 41 set gap1.value=100 where gap2.id1=3; 42--error ER_UNKNOWN_ERROR 43update gap1 set gap1.value= (select count(*) from gap2); 44 45# delete join 46delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3 47 and gap2.id2=3 and gap2.id3=3; 48--error ER_UNKNOWN_ERROR 49delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3; 50 51# select join / self join 52--error ER_UNKNOWN_ERROR 53select * from gap1, gap2 limit 1 for update; 54--error ER_UNKNOWN_ERROR 55select * from gap1 a, gap1 b limit 1 for update; 56 57# unique secondary key 58create table u1( 59 c1 int, 60 c2 int, 61 c3 int, 62 c4 int, 63 primary key (c1, c2, c3), 64 unique key (c3, c1) 65); 66begin; 67insert into u1 values (1,1,1,1); 68commit; 69begin; 70insert into u1 values (1,2,1,1) on duplicate key update c4=10; 71commit; 72begin; 73select * from u1 where c3=1 and c1 = 1 for update; 74--error ER_UNKNOWN_ERROR 75select * from u1 where c3=1 for update; 76commit; 77drop table u1; 78} 79 80if (!$is_gaplock_target) 81{ 82# autocommit doesn't prevent single table operations 83insert into gap1 (id1, id2, id3) values (-1,-1,-1); 84insert into gap1 (id1, id2, id3) values (-1,-1,-1) 85 on duplicate key update value=100; 86update gap1 set value=100 where id1=1; 87update gap1 set value=100 where id1=1 and id2=1 and id3=1; 88delete from gap1 where id1=2; 89delete from gap1 where id1=-1 and id2=-1 and id3=-1; 90commit; 91} 92