1
DROP DATABASE IF EXISTS mysqltest;
2
DROP TABLE IF EXISTS t1, t2, t3, t4, v1;
3
DROP VIEW IF EXISTS t1, t2, t3, t4, v1;
4
DROP PROCEDURE IF EXISTS lock_t1_excl;
5
DROP PROCEDURE IF EXISTS count_t2;
6
DROP PROCEDURE IF EXISTS update_t2;
7
DROP TRIGGER IF EXISTS t1_ai;
9
# WL3561 - transactional LOCK TABLE - Syntax tests
10
# ================================================
11
CREATE TABLE t1 (c1 INT ) ENGINE=MyISAM;
12
CREATE TABLE t2 (c2 INT ) ENGINE=MyISAM;
13
CREATE TABLE t3 (c3 INT ) ENGINE=MyISAM;
15
# Valid syntax for non-transactional locks.
16
LOCK TABLE t1 READ, t2 WRITE;
18
LOCK TABLE t1 READ LOCAL, t2 LOW_PRIORITY WRITE;
21
# Valid syntax for transactional locks.
22
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
24
Warning 1613 Converted to non-transactional lock on 't1'
25
Warning 1613 Converted to non-transactional lock on 't2'
28
# Valid syntax for aliases with and without 'AS'.
29
LOCK TABLE t1 AS a1 READ, t2 a2 WRITE;
31
LOCK TABLE t1 AS a1 IN SHARE MODE, t2 a2 IN EXCLUSIVE MODE;
33
Warning 1613 Converted to non-transactional lock on 'a1'
34
Warning 1613 Converted to non-transactional lock on 'a2'
37
# Transactional locks taken on a view.
38
CREATE VIEW v1 AS SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
39
LOCK TABLE v1 IN SHARE MODE;
41
Warning 1613 Converted to non-transactional lock on 't1'
42
Warning 1613 Converted to non-transactional lock on 't2'
43
LOCK TABLE v1 IN EXCLUSIVE MODE;
45
Warning 1613 Converted to non-transactional lock on 'v1'
48
# Locking INFORMATION_SCHEMA fails on missing privileges.
49
LOCK TABLE information_schema.tables IN SHARE MODE;
50
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
51
LOCK TABLE information_schema.tables IN EXCLUSIVE MODE;
52
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
53
LOCK TABLE information_schema.tables READ;
54
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
55
LOCK TABLE information_schema.tables WRITE;
56
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
58
# The new keywords EXCLUSIVE and NOWAIT are not reserved words.
59
CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=MyISAM;
63
# Syntax errors for misspelled modes or left out symbols.
64
##-------------------------------------------------------
65
LOCK TABLE t1 IN SHARED MODE;
66
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE' at line 1
67
LOCK TABLE t1 SHARE MODE;
68
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
69
LOCK TABLE t1 IN SHARE;
70
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
71
LOCK TABLE t1 IN MODE;
72
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
73
LOCK TABLE t1 READ NOWAIT, t2 WRITE NOWAIT;
74
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 WRITE NOWAIT' at line 1
75
LOCK TABLE t1 READ NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;
76
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT' at line 1
77
LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 WRITE NOWAIT;
78
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
79
LOCK TABLE t1 IN SHARED MODE NOWAIT;
80
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE NOWAIT' at line 1
81
LOCK TABLE t1 SHARE MODE NOWAIT;
82
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
83
LOCK TABLE t1 IN SHARE NOWAIT;
84
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
85
LOCK TABLE t1 IN MODE NOWAIT;
86
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
87
##----------------------
88
## End of syntax errors.
91
# WL3561 - transactional LOCK TABLE - Lock method conversion
92
# ==========================================================
94
# Implicit lock method conversion due to mix in statement.
95
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
97
Warning 1613 Converted to non-transactional lock on 't2'
99
# Lock t1 share (converted to read), t2 write.
100
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
102
Warning 1613 Converted to non-transactional lock on 't1'
103
# Show t1 is read locked, t2 write locked.
104
INSERT INTO t1 SELECT * FROM t2;
105
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
106
INSERT INTO t2 SELECT * FROM t1;
108
# Implicit lock method conversion due to existing non-transact. locks.
109
# Implicitly unlock existing non-transactional locks and take new ones.
110
# Lock t1 exclusive (converted to write), t2 share (converted to read).
111
LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE;
113
Warning 1613 Converted to non-transactional lock on 't1'
114
Warning 1613 Converted to non-transactional lock on 't2'
115
# Show t1 is write locked, t2 read locked.
116
INSERT INTO t1 SELECT * FROM t2;
117
INSERT INTO t2 SELECT * FROM t1;
118
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
121
# Reject lock method conversion in strict mode.
123
SET @wl3561_save_sql_mode= @@SQL_MODE;
124
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
125
# Try mixed mode locks.
126
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
127
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't2'
128
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
129
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
130
# Lock non-transactional.
131
LOCK TABLE t1 READ, t2 WRITE;
132
# Try transactional locks on top of the existing non-transactional locks.
133
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
134
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
135
## Error is reported on first table only. Show both errors:
138
Error 1614 Cannot convert to non-transactional lock in strict mode on 't1'
139
Error 1614 Cannot convert to non-transactional lock in strict mode on 't2'
141
SET @@SQL_MODE= @wl3561_save_sql_mode;
143
# Reject lock method conversion in an active transaction.
146
# Try mixed mode locks.
147
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
148
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't2'
149
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
150
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't1'
153
# Implicit lock method conversion for non-transactional storage engine.
154
# Create a non-transactional table.
155
CREATE TABLE t4 (c4 INT) ENGINE= MyISAM;
156
# Request a transactional lock, which is converted to non-transactional.
157
LOCK TABLE t4 IN SHARE MODE;
159
Warning 1613 Converted to non-transactional lock on 't4'
160
# Try a conflict with the existing non-transactional lock.
161
INSERT INTO t4 VALUES(444);
162
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
165
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
166
# Try a transactional lock, which would need a conversion.
167
LOCK TABLE t4 IN SHARE MODE;
168
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't4'
169
SET @@SQL_MODE= @wl3561_save_sql_mode;
171
# View with transactional and non-transactional storage engine.
172
CREATE VIEW v1 AS SELECT * FROM t3, t4 WHERE t3.c3 = t4.c4;
173
# Request a share lock on the view, which is converted to read locks.
174
LOCK TABLE v1 IN SHARE MODE;
176
Warning 1613 Converted to non-transactional lock on 't3'
177
Warning 1613 Converted to non-transactional lock on 't4'
178
# Show that read locks on the base tables prohibit writing ...
179
INSERT INTO t3 SELECT * FROM t4;
180
ERROR HY000: Table 't3' was locked with a READ lock and can't be updated
181
INSERT INTO t4 SELECT * FROM t3;
182
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
183
# ... but allow reading.
184
SELECT COUNT(*) FROM t3, t4 WHERE t3.c3 = t4.c4;
187
SELECT COUNT(*) FROM v1;
190
## Report conversion on view due to existing non-transactional locks.
191
LOCK TABLE v1 IN EXCLUSIVE MODE;
193
Warning 1613 Converted to non-transactional lock on 'v1'
194
INSERT INTO t3 VALUES(333);
195
INSERT INTO t4 VALUES(444);
196
INSERT INTO t1 VALUES(111);
197
ERROR HY000: Table 't1' was not locked with LOCK TABLES
199
## Now report conversion on base table again.
200
LOCK TABLE v1 IN EXCLUSIVE MODE;
202
Warning 1613 Converted to non-transactional lock on 't3'
203
Warning 1613 Converted to non-transactional lock on 't4'
204
INSERT INTO t3 VALUES(333);
205
INSERT INTO t4 VALUES(444);
206
INSERT INTO t1 VALUES(111);
207
ERROR HY000: Table 't1' was not locked with LOCK TABLES
212
# Insufficient privileges do not unlock tables nor end transactions.
213
# Prepare database, tables and an user with insufficient privileges.
214
# Make a new connection with this user.
215
CREATE DATABASE mysqltest;
216
CREATE TABLE mysqltest.t5 (c5 INT) ENGINE=MyISAM;
217
CREATE TABLE mysqltest.t6 (c6 INT) ENGINE=MyISAM;
218
CREATE USER mysqltest_1@localhost;
219
GRANT SELECT, INSERT ON mysqltest.* TO mysqltest_1@localhost;
221
# Show sufficient privileges to lock tables in the test database.
222
LOCK TABLE t1 READ, t2 WRITE;
223
# Show insufficient privileges in the mysqltest database.
224
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
225
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
226
# Show that the locks in 'test' still exist.
227
INSERT INTO t1 SELECT * FROM t2;
228
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
229
INSERT INTO t2 SELECT * FROM t1;
235
INSERT INTO t1 VALUES(111);
236
# Try a lock that fails on privileges.
237
LOCK TABLE mysqltest.t5 READ;
238
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
239
# Rollback transaction.
242
Warning 1196 Some non-transactional changed tables couldn't be rolled back
243
# Show that the inserted value has gone.
247
# Drop the connection with the unprivileged user.
248
# connection default.
250
# Sufficient privileges do unlock tables and end transactions.
251
# Grant sufficient privileges to the user.
252
# Make a new connection with this user.
253
GRANT SELECT, INSERT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
255
# Lock tables in the test database.
256
LOCK TABLE t1 READ, t2 WRITE;
257
# Lock tables in the mysqltest database.
258
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
259
# Show that the locks in 'test' have been replaced ...
260
INSERT INTO t1 SELECT * FROM t2;
261
ERROR HY000: Table 't1' was not locked with LOCK TABLES
262
INSERT INTO t2 SELECT * FROM t1;
263
ERROR HY000: Table 't2' was not locked with LOCK TABLES
264
# ... by the locks in 'mysqltest'.
265
INSERT INTO mysqltest.t5 SELECT * FROM mysqltest.t6;
266
ERROR HY000: Table 't5' was locked with a READ lock and can't be updated
267
INSERT INTO mysqltest.t6 SELECT * FROM mysqltest.t5;
273
INSERT INTO t1 VALUES(111);
275
LOCK TABLE mysqltest.t5 READ;
276
# Rollback transaction.
279
# Show that the inserted value had been committed.
285
# connection default.
287
DROP USER mysqltest_1@localhost;
288
DROP DATABASE mysqltest;
289
DROP TABLE t1, t2, t3, t4;
291
# WL3594 - transactional LOCK TABLE Testing - Functional tests
292
# ============================================================
293
# Prepare tables and connections.
294
# Set AUTOCOMMIT= 0 in each connection.
296
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
297
CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
302
# connection default.
304
# Normal WRITE locks go before readers (autocommit).
308
INSERT INTO t1 VALUES(111);
309
# Take a non-transactional lock.
314
# Take a non-transactional WRITE lock,
315
# which waits in background until first read lock is released.
317
# connection default.
318
# Wait for the helper thread to sit on its lock.
322
# Take a non-transactional READ lock,
323
# which waits in background until the WRITE lock is released.
325
# connection default.
326
# Wait for the helper threads to sit on their locks.
327
# Unlock this connections non-transactional lock.
330
# Now the WRITE lock is taken.
332
INSERT INTO t1 VALUES(1111);
336
# Now the READ lock is taken.
337
# Select from the table.
344
# connection default.
347
# LOW_PRIORITY WRITE locks wait for readers (autocommit).
349
INSERT INTO t1 VALUES(111);
350
# Take a non-transactional lock.
353
# Take a non-transactional LOW_PRIORITY WRITE lock,
354
# which waits in background until all read locks are released.
355
LOCK TABLE t1 LOW_PRIORITY WRITE;
356
# connection default.
357
# Wait for the helper thread to sit on its lock.
359
# Take a non-transactional READ lock,
360
# which goes before the LOW_PRIORITY WRITE lock.
362
# The READ lock could be taken immediately.
363
# Select from the table.
370
# connection default.
371
# Unlock this connections non-transactional lock.
374
# Now the LOW_PRIORITY WRITE lock is taken.
376
INSERT INTO t1 VALUES(1111);
380
# connection default.
385
# LOCK TABLE is prohibited in stored procedure.
386
CREATE PROCEDURE lock_t1_excl()
387
LOCK TABLE t1 IN EXCLUSIVE MODE;
388
ERROR 0A000: LOCK is not allowed in stored procedures
390
# LOCK TABLE is prohibited in trigger.
391
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
392
LOCK TABLE t2 IN EXCLUSIVE MODE;
393
ERROR 0A000: LOCK is not allowed in stored procedures
added
removed
mysql-test/r/locktrans_myisam.result
