drop table if exists t1,t2; 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; dummy1 count(distinct id) NULL 1 update t1 set id=-1 where id=1; LOCK TABLE t1 READ; update t1 set id=1 where id=1; ERROR HY000: Table 't1' was locked with a READ lock and can't be updated create table t2 SELECT * from t1; ERROR HY000: Table 't2' was not locked with LOCK TABLES 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; 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'); 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; Table Op Msg_type Msg_text test.t1 check status OK lock tables t1 write; check table t2; Table Op Msg_type Msg_text test.t2 check Error Table 't2' was not locked with LOCK TABLES test.t2 check error Corrupt insert into t1 select index1,nr from t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES 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; create table t1 (c1 int); create table t2 (c1 int); create table t3 (c1 int); lock tables t1 write, t2 write, t3 write; drop table t2, t3, t1; create table t1 (c1 int); create table t2 (c1 int); create table t3 (c1 int); lock tables t1 write, t2 write, t3 write, t1 as t4 read; alter table t2 add column c2 int; drop table t1, t2, t3; 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; delete from t2 using t1,t2 where t1.a=t2.a; ERROR HY000: Table 't2' was locked with a READ lock and can't be updated delete t2 from t1,t2 where t1.a=t2.a; ERROR HY000: Table 't2' was locked with a READ lock and can't be updated drop table t1,t2; End of 4.1 tests. drop table if exists t1; create table t1 (a int); lock table t1 write; flush tables with read lock; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction unlock tables; drop table t1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT); LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ; UNLOCK TABLES; LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE; UNLOCK TABLES; LOCK TABLES mysql.time_zone READ, mysql.proc READ; UNLOCK TABLES; LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE; UNLOCK TABLES; LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ; ERROR HY000: You can't combine write-locking of system tables with other tables or lock types LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ; ERROR HY000: You can't combine write-locking of system tables with other tables or lock types LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE; ERROR HY000: You can't combine write-locking of system tables with other tables or lock types LOCK TABLES mysql.time_zone READ, mysql.proc WRITE; ERROR HY000: You can't combine write-locking of system tables with other tables or lock types DROP TABLE t1; Bug#5719 impossible to lock VIEW Just covering existing behaviour with tests. Consistency has not been found here. drop view if exists v_bug5719; drop table if exists t1, t2, t3; create table t1 (a int); create temporary table t2 (a int); create table t3 (a int); create view v_bug5719 as select 1; lock table v_bug5719 write; select * from t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES Allowed to select from a temporary talbe under LOCK TABLES select * from t2; a select * from t3; ERROR HY000: Table 't3' was not locked with LOCK TABLES select * from v_bug5719; 1 1 drop view v_bug5719; sic: did not left LOCK TABLES mode automatically select * from t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES unlock tables; create view v_bug5719 as select * from t1; lock tables v_bug5719 write; select * from v_bug5719; a Allowed to use an underlying table under LOCK TABLES <view> select * from t1; a Allowed to select from a temporary table under LOCK TABLES select * from t2; a select * from t3; ERROR HY000: Table 't3' was not locked with LOCK TABLES drop table t1; sic: left LOCK TABLES mode select * from t3; a select * from v_bug5719; ERROR HY000: View 'test.v_bug5719' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them unlock tables; drop view v_bug5719; When limitation to use temporary tables in views is removed, please add a test that shows what happens under LOCK TABLES when a view references a temporary table, is locked, and the underlying table is dropped. create view v_bug5719 as select * from t2; ERROR HY000: View's SELECT refers to a temporary table 't2' Cleanup. drop table t2, t3; End of 5.1 tests.