1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
drop table if exists t1,t2;
create table t1(n int);
insert into t1 values (1);
lock tables t1 write;
update low_priority t1 set n = 4;
select n from t1;
unlock tables;
n
4
drop table t1;
create table t1(n int);
insert into t1 values (1);
lock tables t1 read;
update low_priority t1 set n = 4;
select n from t1;
n
1
unlock tables;
drop table t1;
create table t1 (a int, b int);
create table t2 (c int, d int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t2 values(1,2);
lock table t1 read;
update t1,t2 set c=a where b=d;
select c from t2;
c
2
unlock tables;
drop table t1;
drop table t2;
create table t1 (a int);
create table t2 (a int);
lock table t1 write, t2 write;
insert t1 select * from t2;
drop table t2;
ERROR 42S02: Table 'test.t2' doesn't exist
drop table t1;
create table t1 (a int);
create table t2 (a int);
lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write;
insert t1 select * from t2;
drop table t2;
ERROR 42S02: Table 'test.t2' doesn't exist
drop table t1;
End of 4.1 tests
create table t1(a int);
lock tables t1 write;
show columns from t1;
Field Type Null Key Default Extra
a int(11) YES NULL
unlock tables;
drop table t1;
use mysql;
LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE;
FLUSH TABLES;
use mysql;
SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1;
OPTIMIZE TABLES columns_priv, db, host, user;
Table Op Msg_type Msg_text
mysql.columns_priv optimize status OK
mysql.db optimize status OK
mysql.host optimize status OK
mysql.user optimize status OK
UNLOCK TABLES;
Select_priv
N
use test;
use test;
CREATE TABLE t1 (c1 int);
LOCK TABLE t1 WRITE;
FLUSH TABLES WITH READ LOCK;
CREATE TABLE t2 (c1 int);
UNLOCK TABLES;
UNLOCK TABLES;
DROP TABLE t1, t2;
CREATE TABLE t1 (c1 int);
LOCK TABLE t1 WRITE;
FLUSH TABLES WITH READ LOCK;
CREATE TABLE t2 AS SELECT * FROM t1;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
UNLOCK TABLES;
UNLOCK TABLES;
DROP TABLE t1;
CREATE DATABASE mysqltest_1;
FLUSH TABLES WITH READ LOCK;
DROP DATABASE mysqltest_1;
DROP DATABASE mysqltest_1;
ERROR HY000: Can't execute the query because you have a conflicting read lock
UNLOCK TABLES;
DROP DATABASE mysqltest_1;
ERROR HY000: Can't drop database 'mysqltest_1'; database doesn't exist
create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb;
lock tables t1 write;
alter table t1 auto_increment=0;
alter table t1 auto_increment=0;
unlock tables;
drop table t1;
End of 5.0 tests
create table t1 (i int);
lock table t1 read;
update t1 set i= 10;;
select * from t1;;
kill query ID;
i
ERROR 70100: Query execution was interrupted
unlock tables;
drop table t1;
drop table if exists t1;
create table t1 (a int) ENGINE=MEMORY;
--> client 2
handler t1 open;
ERROR HY000: Table storage engine for 't1' doesn't have this option
--> client 1
drop table t1;
drop table if exists t1;
create table t1 (i int);
connection: default
lock tables t1 write;
connection: flush
flush tables with read lock;;
connection: default
alter table t1 add column j int;
connection: insert
insert into t1 values (1,2);;
connection: default
unlock tables;
connection: flush
select * from t1;
i j
unlock tables;
select * from t1;
i j
1 2
drop table t1;
drop table if exists t1;
create table t1 (i int);
connection: default
lock tables t1 write;
connection: flush
flush tables with read lock;;
connection: default
flush tables;
unlock tables;
drop table t1;
drop table if exists t1,t2;
create table t1 (a int);
flush status;
lock tables t1 read;
insert into t1 values(1);
unlock tables;
drop table t1;
select @tlwa < @tlwb;
@tlwa < @tlwb
1
End of 5.1 tests
drop table if exists t1;
create table t1 (i int);
connection: default
lock tables t1 write;
connection: flush
flush tables with read lock;;
connection: default
flush tables;
drop table t1;
|