| 1 |
connect con1,localhost,root,,; |
| 2 |
SET lock_wait_timeout=1; |
| 3 |
connection default; |
| 4 |
DROP TABLE IF EXISTS t1, t2, t3; |
| 5 |
CREATE TABLE t1 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 6 |
INSERT INTO t1 (id,id2) VALUES (1,1),(1,2),(1,3); |
| 7 |
LOCK TABLE t1 LOW_PRIORITY WRITE; |
| 8 |
SELECT id2,COUNT(DISTINCT id) FROM t1 GROUP BY id2; |
| 9 |
id2 COUNT(DISTINCT id) |
| 10 |
1 1 |
| 11 |
2 1 |
| 12 |
3 1 |
| 13 |
UPDATE t1 SET id=-1 WHERE id=1; |
| 14 |
connection con1; |
| 15 |
SELECT id,id2 FROM t1; |
| 16 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 17 |
LOCK TABLE t1 READ; |
| 18 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 19 |
connection default; |
| 20 |
LOCK TABLE t1 READ; |
| 21 |
UPDATE t1 SET id=1 WHERE id=1; |
| 22 |
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated |
| 23 |
connection con1; |
| 24 |
SELECT COUNT(DISTINCT id) FROM t1; |
| 25 |
COUNT(DISTINCT id) |
| 26 |
1 |
| 27 |
UPDATE t1 SET id=2 WHERE id=2; |
| 28 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 29 |
LOCK TABLE t1 WRITE; |
| 30 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 31 |
LOCK TABLE t1 READ; |
| 32 |
UNLOCK TABLES; |
| 33 |
connection default; |
| 34 |
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 35 |
ERROR HY000: Table 't2' was not locked with LOCK TABLES |
| 36 |
CREATE TEMPORARY TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 37 |
DROP TABLE IF EXISTS t2; |
| 38 |
UNLOCK TABLES; |
| 39 |
CREATE TABLE t2 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 40 |
LOCK TABLE t1 WRITE, t2 WRITE; |
| 41 |
INSERT INTO t2 (id,id2) SELECT id,id2 FROM t1; |
| 42 |
UPDATE t1 SET id=1 WHERE id=-1; |
| 43 |
DROP TABLE t1,t2; |
| 44 |
CREATE TABLE t1 (i1 <INT_COLUMN>, nr <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 45 |
CREATE TABLE t2 (nr <INT_COLUMN>, nm <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 46 |
INSERT INTO t2 (nr,nm) VALUES (1,3); |
| 47 |
INSERT INTO t2 (nr,nm) VALUES (2,4); |
| 48 |
lock tables t1 write, t2 read; |
| 49 |
INSERT INTO t1 (i1,nr) SELECT 1, nr FROM t2 WHERE nm=3; |
| 50 |
INSERT INTO t1 (i1,nr) SELECT 2, nr FROM t2 WHERE nm=4; |
| 51 |
UNLOCK TABLES; |
| 52 |
LOCK TABLES t1 WRITE; |
| 53 |
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1; |
| 54 |
ERROR HY000: Table 't1' was not locked with LOCK TABLES |
| 55 |
UNLOCK TABLES; |
| 56 |
LOCK TABLES t1 WRITE, t1 AS t1_alias READ; |
| 57 |
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1 AS t1_alias; |
| 58 |
DROP TABLE t1,t2; |
| 59 |
ERROR HY000: Table 't2' was not locked with LOCK TABLES |
| 60 |
UNLOCK TABLES; |
| 61 |
DROP TABLE t1,t2; |
| 62 |
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 63 |
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 64 |
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 65 |
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; |
| 66 |
DROP TABLE t2, t3, t1; |
| 67 |
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 68 |
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 69 |
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 70 |
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t1 AS t4 READ; |
| 71 |
ALTER TABLE t2 ADD COLUMN c2 <INT_COLUMN>; |
| 72 |
DROP TABLE t1, t2, t3; |
| 73 |
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 74 |
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 75 |
LOCK TABLE t1 READ, t2 READ; |
| 76 |
FLUSH TABLE t1; |
| 77 |
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated |
| 78 |
FLUSH TABLES; |
| 79 |
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated |
| 80 |
FLUSH TABLES t1, t2 WITH READ LOCK; |
| 81 |
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction |
| 82 |
UNLOCK TABLES; |
| 83 |
FLUSH TABLES t1, t2 WITH READ LOCK; |
| 84 |
connection con1; |
| 85 |
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); |
| 86 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 87 |
connection default; |
| 88 |
UNLOCK TABLES; |
| 89 |
FLUSH TABLES WITH READ LOCK; |
| 90 |
connection con1; |
| 91 |
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); |
| 92 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 93 |
connection default; |
| 94 |
UNLOCK TABLES; |
| 95 |
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); |
| 96 |
FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT; |
| 97 |
DROP TABLE t1, t2; |
| 98 |
ERROR HY000: Can't execute the query because you have a conflicting read lock |
| 99 |
UNLOCK TABLES; |
| 100 |
DROP TABLE t1, t2; |
| 101 |
disconnect con1; |
| 102 |
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 103 |
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 104 |
LOCK TABLE t1 WRITE, t2 WRITE; |
| 105 |
CREATE TEMPORARY TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; |
| 106 |
FLUSH TABLE t1; |
| 107 |
DROP TEMPORARY TABLE t1; |
| 108 |
SELECT a,b FROM t1; |
| 109 |
a b |
| 110 |
UNLOCK TABLES; |
| 111 |
DROP TABLE t1, t2; |