lock.test 1.85 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
#
# Testing of table locking
#

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`)) TYPE=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;
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55


#
# Check bug with INSERT ... SELECT with lock tables
#

CREATE TABLE t1 (
  index1 smallint(6) default NULL,
  nr smallint(6) default NULL,
  KEY index1(index1)
) TYPE=MyISAM;

CREATE TABLE t2 (
  nr smallint(6) default NULL,
  name varchar(20) default NULL
) TYPE=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;
unlock tables;
drop table t1,t2;
56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

#test to see if select will get the lock ahead of low priority update
connect (locker,localhost,root,,);
connect (reader,localhost,root,,);
connect (writer,localhost,root,,);

connection locker;
create table t1(n int);
insert into t1 values (1);
lock tables t1 write;
connection writer;
send update low_priority t1 set n = 4;
connection reader;
send select n from t1;
connection locker;
unlock tables;
connection writer;
reap;
connection reader;
reap;
drop table t1;