Debian Codegraph @

mysql-test/suite/storage_engine/lock.test

raw
1 # Standard table locking:
2 # LOCK TABLE .. READ
3 # LOCK TABLE .. [LOW_PRIORITY] WRITE
4 # UNLOCK TABLES
5 #
6 # and global locking:
7 # FLUSH TABLES [..] WITH READ LOCK
8 #
9 #
10 --source have_engine.inc
11
12 # Save the initial number of concurrent sessions.
13 --source include/count_sessions.inc
14
15 connect (con1,localhost,root,,);
16 SET lock_wait_timeout=1;
17
18 connection default;
19
20 --disable_warnings
21 DROP TABLE IF EXISTS t1, t2, t3;
22 --enable_warnings
23
24 --let $create_definition = id $int_col, id2 $int_col
25 --source create_table.inc
26
27 INSERT INTO t1 (id,id2) VALUES (1,1),(1,2),(1,3);
28
29 # LOW_PRIORITY has no effect, but is still syntactically correct
30 LOCK TABLE t1 LOW_PRIORITY WRITE;
31 SELECT id2,COUNT(DISTINCT id) FROM t1 GROUP BY id2;
32
33 UPDATE t1 SET id=-1 WHERE id=1;
34 if ($mysql_errname)
35 {
36 --let $functionality = UPDATE
37 --source unexpected_result.inc
38 }
39
40 connection con1;
41 # With WRITE lock held by connection 'default',
42 # nobody else can access the table
43 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
44 SELECT id,id2 FROM t1;
45 --source check_errors.inc
46 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
47 LOCK TABLE t1 READ;
48 --source check_errors.inc
49
50 connection default;
51 LOCK TABLE t1 READ;
52 --let $error_codes = ER_TABLE_NOT_LOCKED_FOR_WRITE
53 UPDATE t1 SET id=1 WHERE id=1;
54 --source check_errors.inc
55 if ($mysql_errname != ER_TABLE_NOT_LOCKED_FOR_WRITE)
56 {
57 --let $functonality = UPDATE or locking
58 --source unexpected_result.inc
59 }
60
61 connection con1;
62 # With READ lock held by connection 'default',
63 # it should be possible to read from the table
64 # or acquire another READ lock,
65 # but not update it or acquire WRITE lock
66 SELECT COUNT(DISTINCT id) FROM t1;
67 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
68 UPDATE t1 SET id=2 WHERE id=2;
69 --source check_errors.inc
70 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
71 LOCK TABLE t1 WRITE;
72 --source check_errors.inc
73 LOCK TABLE t1 READ;
74 UNLOCK TABLES;
75
76
77 --connection default
78
79 --let $error_codes = ER_TABLE_NOT_LOCKED
80 --let $table_name = t2
81 --source create_table.inc
82
83 --let $table_name = t2
84 --let $temporary = 1
85 --source create_table.inc
86
87 DROP TABLE IF EXISTS t2;
88
89 UNLOCK TABLES;
90
91 --let $table_name = t2
92 --let $create_definition = id $int_col, id2 $int_col
93 --source create_table.inc
94 LOCK TABLE t1 WRITE, t2 WRITE;
95 INSERT INTO t2 (id,id2) SELECT id,id2 FROM t1;
96 UPDATE t1 SET id=1 WHERE id=-1;
97 if ($mysql_errname)
98 {
99 --let $functionality = UPDATE
100 --source unexpected_result.inc
101 }
102 DROP TABLE t1,t2;
103
104 #
105 # INSERT ... SELECT with lock tables
106 #
107
108 --let $create_definition = i1 $int_col, nr $int_col
109 --source create_table.inc
110
111 --let $table_name = t2
112 --let $create_definition = nr $int_col, nm $int_col
113 --source create_table.inc
114
115 INSERT INTO t2 (nr,nm) VALUES (1,3);
116 INSERT INTO t2 (nr,nm) VALUES (2,4);
117
118 lock tables t1 write, t2 read;
119 INSERT INTO t1 (i1,nr) SELECT 1, nr FROM t2 WHERE nm=3;
120 INSERT INTO t1 (i1,nr) SELECT 2, nr FROM t2 WHERE nm=4;
121 UNLOCK TABLES;
122
123 LOCK TABLES t1 WRITE;
124 --let $error_codes = ER_TABLE_NOT_LOCKED
125 INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1;
126 --source check_errors.inc
127 UNLOCK TABLES;
128 LOCK TABLES t1 WRITE, t1 AS t1_alias READ;
129 INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1 AS t1_alias;
130 --let $error_codes = ER_TABLE_NOT_LOCKED
131 DROP TABLE t1,t2;
132 --source check_errors.inc
133 UNLOCK TABLES;
134 DROP TABLE t1,t2;
135
136 #
137 # Check that a dropped table is removed from a lock
138
139 --source create_table.inc
140 --let $table_name = t2
141 --source create_table.inc
142 --let $table_name = t3
143 --source create_table.inc
144 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
145 # This removes one table after the other from the lock.
146 DROP TABLE t2, t3, t1;
147 #
148 # Check that a lock merge works
149 --source create_table.inc
150 --let $table_name = t2
151 --source create_table.inc
152 --let $table_name = t3
153 --source create_table.inc
154 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t1 AS t4 READ;
155
156 --let $alter_definition = ADD COLUMN c2 $int_col
157 --let $table_name = t2
158 --source alter_table.inc
159 if ($mysql_errname)
160 {
161 --let $my_last_stmt = $alter_statement
162 --let $functionality = ALTER TABLE
163 --source unexpected_result.inc
164 }
165
166 DROP TABLE t1, t2, t3;
167
168 # FLUSH TABLES is not permitted when there is an active LOCK TABLES .. READ,
169 # FLUSH TABLES .. WITH READ LOCK should be used instead
170 # (and for other connections the table is locked)
171
172 --source create_table.inc
173 --let $table_name = t2
174 --source create_table.inc
175
176 LOCK TABLE t1 READ, t2 READ;
177 --let $error_codes = ER_TABLE_NOT_LOCKED_FOR_WRITE
178 FLUSH TABLE t1;
179 --source check_errors.inc
180 --let $error_codes = ER_TABLE_NOT_LOCKED_FOR_WRITE
181 FLUSH TABLES;
182 --source check_errors.inc
183 --let $error_codes = ER_LOCK_OR_ACTIVE_TRANSACTION
184 FLUSH TABLES t1, t2 WITH READ LOCK;
185 --source check_errors.inc
186 UNLOCK TABLES;
187
188 FLUSH TABLES t1, t2 WITH READ LOCK;
189
190 --connection con1
191 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
192 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
193 --source check_errors.inc
194
195 --connection default
196 UNLOCK TABLES;
197
198 # Global lock
199
200 FLUSH TABLES WITH READ LOCK;
201
202 --connection con1
203 --let $error_codes = ER_LOCK_WAIT_TIMEOUT
204 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
205 --source check_errors.inc
206
207 --connection default
208 UNLOCK TABLES;
209 INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
210 FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT;
211 --let $error_codes = ER_CANT_UPDATE_WITH_READLOCK
212 DROP TABLE t1, t2;
213 --source check_errors.inc
214 UNLOCK TABLES;
215 DROP TABLE t1, t2;
216
217 --disconnect con1
218
219 #
220 # Ensure that FLUSH TABLES doesn't substitute a base locked table
221 # with a temporary one.
222 #
223
224 --source create_table.inc
225 --let $table_name = t2
226 --source create_table.inc
227 LOCK TABLE t1 WRITE, t2 WRITE;
228
229 --let $temporary = 1
230 --source create_table.inc
231 FLUSH TABLE t1;
232 DROP TEMPORARY TABLE t1;
233 SELECT a,b FROM t1;
234 UNLOCK TABLES;
235 DROP TABLE t1, t2;
236
237 # Check that all connections opened by test cases in this file are really
238 # gone so execution of other tests won't be affected by their presence.
239 --source include/wait_until_count_sessions.inc
240
241 --source cleanup_engine.inc
242