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=InnoDB;
12
CREATE TABLE t2 (c2 INT ) ENGINE=InnoDB;
13
CREATE TABLE t3 (c3 INT ) ENGINE=InnoDB;
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;
25
# Valid syntax for transactional locks with NOWAIT option.
26
## In the preliminary reference implementation we expect these errors:
27
## NOWAIT+SHARE="timed out", NOWAIT+EXCLUSIVE="not supported".
28
## Statements abort on first error.
29
LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;
30
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
31
LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT, t2 IN SHARE MODE NOWAIT;
32
ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version
33
LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE NOWAIT;
34
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
36
# Valid syntax for aliases with and without 'AS'.
37
LOCK TABLE t1 AS a1 READ, t2 a2 WRITE;
39
LOCK TABLE t1 AS a1 IN SHARE MODE, t2 a2 IN EXCLUSIVE MODE;
42
# Transactional locks taken on a view.
43
CREATE VIEW v1 AS SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
44
LOCK TABLE v1 IN SHARE MODE;
45
LOCK TABLE v1 IN EXCLUSIVE MODE;
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=InnoDB;
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=InnoDB;
217
CREATE TABLE mysqltest.t6 (c6 INT) ENGINE=InnoDB;
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.
241
# Show that the inserted value has gone.
244
# Drop the connection with the unprivileged user.
245
# connection default.
247
# Sufficient privileges do unlock tables and end transactions.
248
# Grant sufficient privileges to the user.
249
# Make a new connection with this user.
250
GRANT SELECT, INSERT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
252
# Lock tables in the test database.
253
LOCK TABLE t1 READ, t2 WRITE;
254
# Lock tables in the mysqltest database.
255
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
256
# Show that the locks in 'test' have been replaced ...
257
INSERT INTO t1 SELECT * FROM t2;
258
ERROR HY000: Table 't1' was not locked with LOCK TABLES
259
INSERT INTO t2 SELECT * FROM t1;
260
ERROR HY000: Table 't2' was not locked with LOCK TABLES
261
# ... by the locks in 'mysqltest'.
262
INSERT INTO mysqltest.t5 SELECT * FROM mysqltest.t6;
263
ERROR HY000: Table 't5' was locked with a READ lock and can't be updated
264
INSERT INTO mysqltest.t6 SELECT * FROM mysqltest.t5;
270
INSERT INTO t1 VALUES(111);
272
LOCK TABLE mysqltest.t5 READ;
273
# Rollback transaction.
276
# Show that the inserted value had been committed.
281
# connection default.
283
DROP USER mysqltest_1@localhost;
284
DROP DATABASE mysqltest;
285
DROP TABLE t1, t2, t3, t4;
287
# WL3594 - transactional LOCK TABLE Testing - Functional tests
288
# ============================================================
289
# Prepare tables and connections.
290
# Set AUTOCOMMIT= 0 in each connection.
292
CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
293
CREATE TABLE t2 (c2 INT) ENGINE=InnoDB;
298
# connection default.
300
# Transactional lock behaviour:
301
# LOCK TABLE does _not_ commit a transaction.
303
INSERT INTO t1 VALUES (111);
304
# Lock transactional.
305
LOCK TABLE t1 IN EXCLUSIVE MODE;
308
# Show that the inserted value has gone.
312
# After LOCK TABLE one can access tables not mentioned in LOCK TABLE.
313
# Lock t1 transactional.
314
LOCK TABLE t1 IN EXCLUSIVE MODE;
315
# Insert a value into t2.
316
INSERT INTO t2 VALUES (222);
317
# Show that the inserted value is indeed in the table.
322
# One can issue LOCK TABLE many times, adding more tables.
323
# Lock t2 transactional.
324
LOCK TABLE t2 IN EXCLUSIVE MODE;
326
# LOCK TABLE does not rollback a transaction.
327
# Show that the inserted value is still in the table.
331
# Rollback transaction.
333
# Show that the inserted value has gone.
337
# Tables are unlocked at the end of transaction (commit).
338
# Take an exclusive lock.
339
LOCK TABLE t1 IN EXCLUSIVE MODE;
343
# Take an exclusive lock.
344
# This would fail after timeout if t1 is still locked.
345
LOCK TABLE t1 IN EXCLUSIVE MODE;
348
# connection default.
350
# Tables are unlocked at the end of transaction (rollback).
351
# Take an exclusive lock.
352
LOCK TABLE t1 IN EXCLUSIVE MODE;
356
# Take an exclusive lock.
357
# This would fail after timeout if t1 is still locked.
358
LOCK TABLE t1 IN EXCLUSIVE MODE;
361
# connection default.
363
# UNLOCK TABLES does not touch a transaction when
364
# no non-transactional table locks exist.
365
# Take a transactional lock.
366
LOCK TABLE t1 IN EXCLUSIVE MODE;
368
INSERT INTO t1 VALUES(111);
369
# Unlock (non-transactional) table locks.
371
# Show that the inserted value is still in the table.
377
# Show that the inserted value has gone.
381
# UNLOCK TABLES commits a transaction when
382
# non-transactional table locks exist.
383
# Take a non-transactional lock.
386
INSERT INTO t1 VALUES(111);
387
# Unlock (non-transactional) table locks.
389
# Show that the inserted value is still in the table.
395
# Show that the inserted value is still in the table.
401
# START TRANSACTION removes a previous lock.
402
# Take an exclusive lock.
403
LOCK TABLE t1 IN EXCLUSIVE MODE;
407
# Take an exclusive lock.
408
# This would fail after timeout if t1 is still locked.
409
LOCK TABLE t1 IN EXCLUSIVE MODE;
411
# connection default.
414
# With Auto commit on, transactional locks will be ignored
417
# Take an exclusive lock.
418
LOCK TABLE t1 IN EXCLUSIVE MODE;
422
# Take an exclusive lock.
423
# This would fail after timeout if t1 is still locked.
424
LOCK TABLE t1 IN SHARE MODE;
427
# connection default.
432
# With Auto commit on, transactional locks can time out.
433
# Default connection runs in transactional mode.
436
# Take an exclusive lock, which persists.
437
LOCK TABLE t1 IN EXCLUSIVE MODE;
439
# conn1 runs in autocommit mode.
442
# Try an exclusive lock,
443
# which times out though running in autocommit mode.
444
LOCK TABLE t1 IN SHARE MODE;
445
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
448
# connection default.
453
# Normal WRITE locks go before readers (autocommit).
457
INSERT INTO t1 VALUES(111);
458
# Take a non-transactional lock.
463
# Take a non-transactional WRITE lock,
464
# which waits in background until first read lock is released.
466
# connection default.
467
# Wait for the helper thread to sit on its lock.
471
# Take a non-transactional READ lock,
472
# which waits in background until the WRITE lock is released.
474
# connection default.
475
# Wait for the helper threads to sit on their locks.
476
# Unlock this connections non-transactional lock.
479
# Now the WRITE lock is taken.
481
INSERT INTO t1 VALUES(1111);
485
# Now the READ lock is taken.
486
# Select from the table.
493
# connection default.
496
# LOW_PRIORITY WRITE locks wait for readers (autocommit).
498
INSERT INTO t1 VALUES(111);
499
# Take a non-transactional lock.
502
# Take a non-transactional LOW_PRIORITY WRITE lock,
503
# which waits in background until all read locks are released.
504
LOCK TABLE t1 LOW_PRIORITY WRITE;
505
# connection default.
506
# Wait for the helper thread to sit on its lock.
508
# Take a non-transactional READ lock,
509
# which goes before the LOW_PRIORITY WRITE lock.
511
# The READ lock could be taken immediately.
512
# Select from the table.
519
# connection default.
520
# Unlock this connections non-transactional lock.
523
# Now the LOW_PRIORITY WRITE lock is taken.
525
INSERT INTO t1 VALUES(1111);
529
# connection default.
534
# Normal WRITE locks go before readers (transaction).
536
INSERT INTO t1 VALUES(111);
538
# Take a non-transactional lock.
541
# Take a non-transactional WRITE lock,
542
# which waits in background until first read lock is released.
544
# connection default.
545
# Wait for the helper thread to sit on its lock.
547
# Take a non-transactional READ lock,
548
# which waits in background until the WRITE lock is released.
550
# connection default.
551
# Wait for the helper threads to sit on their locks.
552
# Unlock this connections non-transactional lock.
555
# Now the WRITE lock is taken.
557
INSERT INTO t1 VALUES(1111);
561
# Now the READ lock is taken.
562
# Select from the table.
569
# connection default.
573
# LOW_PRIORITY WRITE behaves like WRITE in transaction mode.
575
INSERT INTO t1 VALUES(111);
577
# Take a non-transactional lock.
580
# Take a non-transactional LOW_PRIORITY WRITE lock,
581
# which waits in background until first read lock is released.
582
LOCK TABLE t1 LOW_PRIORITY WRITE;
583
# connection default.
584
# Wait for the helper thread to sit on its lock.
586
# Take a non-transactional READ lock,
587
# which waits in background for the LOW_PRIORITY WRITE lock.
589
# connection default.
590
# Wait for the helper threads to sit on their locks.
591
# Unlock this connections non-transactional lock.
594
# Now the LOW_PRIORITY WRITE lock is taken.
596
INSERT INTO t1 VALUES(1111);
600
# Now the READ lock is taken.
601
# Select from the table.
608
# connection default.
613
# Take an exclusive lock.
614
LOCK TABLE t1 IN EXCLUSIVE MODE;
616
# Try an exclusive lock,
617
# which conflicts and cannot immediately be taken.
618
LOCK TABLE t1 IN SHARE MODE NOWAIT;
619
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
620
# connection default.
624
# Transactional table locks do not interfere with the global read lock.
625
# Take an exclusive lock on t1.
626
LOCK TABLE t1 IN EXCLUSIVE MODE;
628
# Try an exclusive lock, which conflicts.
629
LOCK TABLE t1 IN EXCLUSIVE MODE;
630
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
631
# Can take the global read lock when an exclusive lock exist.
632
FLUSH TABLES WITH READ LOCK;
633
# Show that the global read lock exists.
635
ERROR HY000: Can't execute the query because you have a conflicting read lock
636
# connection default.
637
# Can take an exclusive lock when the global read lock exists.
638
# Take an exclusive lock on t2.
639
LOCK TABLE t2 IN EXCLUSIVE MODE;
641
# Show that an exclusive lock on t1 exists.
642
LOCK TABLE t1 IN EXCLUSIVE MODE;
643
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
644
# Show that an exclusive lock on t2 exists.
645
LOCK TABLE t2 IN EXCLUSIVE MODE;
646
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
647
# Show that the global read lock exists.
649
ERROR HY000: Can't execute the query because you have a conflicting read lock
650
# Release global read lock.
654
# connection default.
659
# Access conflict on INSERT.
660
# Take an share lock on t1.
661
LOCK TABLE t1 IN SHARE MODE;
663
SELECT GET_LOCK("mysqltest1", 10);
664
GET_LOCK("mysqltest1", 10)
666
# Try to insert a value,
667
# which must wait in background for the lock to go away.
668
INSERT INTO t1 VALUES (111);
669
## connection default.
670
## Wait in background until the insert times out and releases lock.
671
SELECT GET_LOCK("mysqltest1", 10);
673
# Wait for INSERT to timeout.
674
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
675
SELECT RELEASE_LOCK("mysqltest1");
676
RELEASE_LOCK("mysqltest1")
679
# connection default.
680
GET_LOCK("mysqltest1", 10)
684
# Show that the insert in conn1 failed.
688
# Access conflict on UPDATE with exclusive lock.
690
INSERT INTO t1 VALUES (111);
693
# Take an exclusive lock.
694
LOCK TABLE t1 IN EXCLUSIVE MODE;
695
# connection default.
696
# Try a second exclusive lock, which fails due to the other lock.
697
LOCK TABLE t1 IN EXCLUSIVE MODE;
698
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
699
# Try an update, which fails due to the exclusive lock.
700
UPDATE t1 SET c1= 111222;
701
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
703
# The exclusive table locker can still update.
704
UPDATE t1 SET c1= 111333;
705
# connection default.
706
# Select is allowed despite the table lock, but sees old data.
713
# connection default.
714
# It seems like the failed update began a transaction, so still old data.
720
# Now select sees current data.
727
# Access conflict on UPDATE with share lock.
729
INSERT INTO t1 VALUES (111);
733
LOCK TABLE t1 IN SHARE MODE;
734
# Update with a single share lock is possible.
735
UPDATE t1 SET c1= 111222;
736
# Commit to get rid of the row lock.
739
LOCK TABLE t1 IN SHARE MODE;
740
# connection default.
741
# An exclusive lock is not possible on a share lock.
742
LOCK TABLE t1 IN EXCLUSIVE MODE;
743
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
744
# More share locks are possible.
745
LOCK TABLE t1 IN SHARE MODE;
746
# Noone can update when multiple share locks exist.
747
UPDATE t1 SET c1= 111333;
748
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
750
# Noone can update when multiple share locks exist.
751
UPDATE t1 SET c1= 111444;
752
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
754
# connection default.
761
# LOCK TABLE is prohibited in stored procedure.
762
CREATE PROCEDURE lock_t1_excl()
763
LOCK TABLE t1 IN EXCLUSIVE MODE;
764
ERROR 0A000: LOCK is not allowed in stored procedures
766
# LOCK TABLE is prohibited in trigger.
767
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
768
LOCK TABLE t2 IN EXCLUSIVE MODE;
769
ERROR 0A000: LOCK is not allowed in stored procedures
771
# LOCK TABLE on a pre-locked table through a trigger.
772
# Create a trigger on t1 that updates t2.
773
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
775
UPDATE t2 SET c2= c2 + 111;
777
# Take an exclusive lock on t1.
778
# This pre-locks t2 through the trigger.
779
LOCK TABLE t1 IN EXCLUSIVE MODE;
781
# Try to take an exclusive lock on t2,
782
# which is pre-locked through the trigger on t1.
783
LOCK TABLE t2 IN EXCLUSIVE MODE;
784
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
786
# connection default.
787
# Commit to release the lock.
791
# LOCK TABLE on a pre-locked table through a view.
792
# Create a function that selects from t2.
793
CREATE FUNCTION count_t2() RETURNS INT
795
RETURN (SELECT COUNT(*) FROM t2);
797
# Create a view with t1 and the function.
798
CREATE VIEW v1 AS SELECT COUNT(*), count_t2() FROM t1 GROUP BY 2;
799
# Take an exclusive lock on v1.
800
# This pre-locks t2 through the view.
801
LOCK TABLE v1 IN EXCLUSIVE MODE;
803
# Try to take an exclusive lock on t2,
804
# which is pre-locked through the function in v1.
805
LOCK TABLE t2 IN EXCLUSIVE MODE;
806
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
808
# connection default.
809
# Commit to release the lock.
812
DROP FUNCTION count_t2;
814
# Transactional LOCK TABLE by-passes pre-lock.
816
# Insert a value to t1 and t2 each.
817
INSERT INTO t1 VALUES (111);
818
INSERT INTO t2 VALUES (222);
820
# Create a trigger on t1 that updates t2.
821
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
823
INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 10);
824
UPDATE t2 SET c2= c2 + 111;
825
INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
827
# Take an SQL lock which blocks the trigger.
828
SELECT GET_LOCK("mysqltest1", 10);
829
GET_LOCK("mysqltest1", 10)
832
# Insert into t1 to fire trigger. This waits on GET_LOCK.
833
INSERT INTO t1 VALUES(111222);
834
# connection default.
835
# Wait for the helper thread to sit on its lock.
836
# Take an exclusive lock.
837
LOCK TABLE t2 IN EXCLUSIVE MODE;
838
# Use the lock for insert.
839
INSERT INTO t2 VALUES (111333);
840
# Commit to release the lock again.
842
# Release the SQL lock to let the trigger finish.
843
SELECT RELEASE_LOCK("mysqltest1");
844
RELEASE_LOCK("mysqltest1")
850
# connection default.
869
# Non-transactional LOCK TABLE cannot by-passes pre-lock.
871
# Insert a value to t1 and t2 each.
872
INSERT INTO t1 VALUES (111);
873
INSERT INTO t2 VALUES (222);
875
# Create a trigger on t1 that updates t2.
876
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
878
# This will time out. So use a small value.
879
INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 1);
880
UPDATE t2 SET c2= c2 + 111;
881
INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
883
# Take an SQL lock which blocks the trigger.
884
SELECT GET_LOCK("mysqltest1", 10);
885
GET_LOCK("mysqltest1", 10)
888
# Insert into t1 to fire trigger. This waits on GET_LOCK.
889
INSERT INTO t1 VALUES(111222);
890
# connection default.
891
# Wait for the helper thread to sit on its lock.
892
# Take a write lock. This waits until the trigger times out.
894
# Use the lock for insert.
895
INSERT INTO t2 VALUES (111333);
896
# Release the lock again.
898
# Release the SQL lock.
899
SELECT RELEASE_LOCK("mysqltest1");
900
RELEASE_LOCK("mysqltest1")
904
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
907
# connection default.
added
removed
mysql-test/r/locktrans_innodb.result
