-- source include/have_ndb.inc -- source include/not_embedded.inc connect (con1,localhost,root,,); connect (con2,localhost,root,,); --disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7; --enable_warnings # # Transaction lock test to show that the NDB # table handler is working properly with # transaction locks # # # Testing of scan isolation # connection con1; create table t1 (x integer not null primary key, y varchar(32)) engine = ndb; insert into t1 values (1,'one'), (2,'two'); select * from t1 order by x; connection con2; select * from t1 order by x; connection con1; start transaction; insert into t1 values (3,'three'); select * from t1 order by x; connection con2; start transaction; select * from t1 order by x; connection con1; commit; connection con2; select * from t1 order by x; commit; drop table t1; ### # Bug#6020 create table t1 (pk integer not null primary key, u int not null, o int not null, unique(u), key(o)) engine = ndb; insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); lock tables t1 write; delete from t1 where pk = 1; unlock tables; select * from t1 order by pk; insert into t1 values (1,1,1); lock tables t1 write; delete from t1 where u = 1; unlock tables; select * from t1 order by pk; insert into t1 values (1,1,1); lock tables t1 write; delete from t1 where o = 1; unlock tables; select * from t1 order by pk; insert into t1 values (1,1,1); drop table t1; # Lock for update create table t1 (x integer not null primary key, y varchar(32), z integer, key(z)) engine = ndb; insert into t1 values (1,'one',1), (2,'two',2),(3,"three",3); # PK access connection con1; begin; select * from t1 where x = 1 for update; connection con2; begin; select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; connection con1; commit; # table scan connection con1; begin; select * from t1 where y = 'one' or y = 'three' for update; connection con2; begin; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks # Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans #select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; connection con1; commit; # index scan connection con1; begin; select * from t1 where z > 1 and z < 3 for update; connection con2; begin; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks select * from t1 where x = 1 for update; --error 1205 select * from t1 where x = 2 for update; rollback; connection con1; commit; # share locking # PK access connection con1; begin; select * from t1 where x = 1 lock in share mode; connection con2; begin; select * from t1 where x = 1 lock in share mode; select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; connection con1; commit; # table scan connection con1; begin; select * from t1 where y = 'one' or y = 'three' lock in share mode; connection con2; begin; select * from t1 where y = 'one' lock in share mode; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks # Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans #select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; connection con1; commit; # index scan connection con1; begin; select * from t1 where z > 1 and z < 3 lock in share mode; connection con2; begin; select * from t1 where z = 1 lock in share mode; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks select * from t1 where x = 1 for update; --error 1205 select * from t1 where x = 2 for update; rollback; connection con1; commit; drop table t1; # End of 4.1 tests