lock.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:2k
- #
- # Testing of table locking
- #
- --disable_warnings
- drop table if exists t1,t2;
- --enable_warnings
- CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) ENGINE=MyISAM;
- insert into t1 (id,id2) values (1,1),(1,2),(1,3);
- LOCK TABLE t1 WRITE;
- select dummy1,count(distinct id) from t1 group by dummy1;
- update t1 set id=-1 where id=1;
- LOCK TABLE t1 READ;
- --error 1099
- update t1 set id=1 where id=1;
- --error 1100
- create table t2 SELECT * from t1;
- create temporary table t2 SELECT * from t1;
- drop table if exists t2;
- unlock tables;
- create table t2 SELECT * from t1;
- LOCK TABLE t1 WRITE,t2 write;
- insert into t2 SELECT * from t1;
- update t1 set id=1 where id=-1;
- drop table t1,t2;
- #
- # Check bug with INSERT ... SELECT with lock tables
- #
- CREATE TABLE t1 (
- index1 smallint(6) default NULL,
- nr smallint(6) default NULL,
- KEY index1(index1)
- ) ENGINE=MyISAM;
- CREATE TABLE t2 (
- nr smallint(6) default NULL,
- name varchar(20) default NULL
- ) ENGINE=MyISAM;
- INSERT INTO t2 VALUES (1,'item1');
- INSERT INTO t2 VALUES (2,'item2');
- # problem begins here!
- lock tables t1 write, t2 read;
- insert into t1 select 1,nr from t2 where name='item1';
- insert into t1 select 2,nr from t2 where name='item2';
- unlock tables;
- check table t1;
- # Check error message
- lock tables t1 write;
- check table t2;
- --error 1100
- insert into t1 select nr from t1;
- unlock tables;
- lock tables t1 write, t1 as t1_alias read;
- insert into t1 select index1,nr from t1 as t1_alias;
- drop table t1,t2;
- #
- # Bug7241 - Invalid response when DELETE .. USING and LOCK TABLES used.
- #
- create table t1 ( a int(11) not null auto_increment, primary key(a));
- create table t2 ( a int(11) not null auto_increment, primary key(a));
- lock tables t1 write, t2 read;
- delete from t1 using t1,t2 where t1.a=t2.a;
- delete t1 from t1,t2 where t1.a=t2.a;
- --error 1099
- delete from t2 using t1,t2 where t1.a=t2.a;
- --error 1099
- delete t2 from t1,t2 where t1.a=t2.a;
- drop table t1,t2;
- # End of 4.1 tests