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
|
connect con1,localhost,root,,;
SET lock_wait_timeout=1;
connection default;
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
INSERT INTO t1 (id,id2) VALUES (1,1),(1,2),(1,3);
LOCK TABLE t1 LOW_PRIORITY WRITE;
SELECT id2,COUNT(DISTINCT id) FROM t1 GROUP BY id2;
id2 COUNT(DISTINCT id)
1 1
2 1
3 1
UPDATE t1 SET id=-1 WHERE id=1;
connection con1;
SELECT id,id2 FROM t1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
LOCK TABLE t1 READ;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection default;
LOCK TABLE t1 READ;
UPDATE t1 SET id=1 WHERE id=1;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
connection con1;
SELECT COUNT(DISTINCT id) FROM t1;
COUNT(DISTINCT id)
1
UPDATE t1 SET id=2 WHERE id=2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
LOCK TABLE t1 WRITE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
LOCK TABLE t1 READ;
UNLOCK TABLES;
connection default;
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
CREATE TEMPORARY TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
DROP TABLE IF EXISTS t2;
UNLOCK TABLES;
CREATE TABLE t2 (id <INT_COLUMN>, id2 <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
LOCK TABLE t1 WRITE, t2 WRITE;
INSERT INTO t2 (id,id2) SELECT id,id2 FROM t1;
UPDATE t1 SET id=1 WHERE id=-1;
DROP TABLE t1,t2;
CREATE TABLE t1 (i1 <INT_COLUMN>, nr <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t2 (nr <INT_COLUMN>, nm <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
INSERT INTO t2 (nr,nm) VALUES (1,3);
INSERT INTO t2 (nr,nm) VALUES (2,4);
lock tables t1 write, t2 read;
INSERT INTO t1 (i1,nr) SELECT 1, nr FROM t2 WHERE nm=3;
INSERT INTO t1 (i1,nr) SELECT 2, nr FROM t2 WHERE nm=4;
UNLOCK TABLES;
LOCK TABLES t1 WRITE;
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1;
ERROR HY000: Table 't1' was not locked with LOCK TABLES
UNLOCK TABLES;
LOCK TABLES t1 WRITE, t1 AS t1_alias READ;
INSERT INTO t1 (i1,nr) SELECT i1, nr FROM t1 AS t1_alias;
DROP TABLE t1,t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t1,t2;
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
DROP TABLE t2, t3, t1;
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t3 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t1 AS t4 READ;
ALTER TABLE t2 ADD COLUMN c2 <INT_COLUMN>;
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
LOCK TABLE t1 READ, t2 READ;
FLUSH TABLE t1;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
FLUSH TABLES;
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
FLUSH TABLES t1, t2 WITH READ LOCK;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
UNLOCK TABLES;
FLUSH TABLES t1, t2 WITH READ LOCK;
connection con1;
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection default;
UNLOCK TABLES;
FLUSH TABLES WITH READ LOCK;
connection con1;
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection default;
UNLOCK TABLES;
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b');
FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT;
DROP TABLE t1, t2;
ERROR HY000: Can't execute the query because you have a conflicting read lock
UNLOCK TABLES;
DROP TABLE t1, t2;
disconnect con1;
CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
LOCK TABLE t1 WRITE, t2 WRITE;
CREATE TEMPORARY TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
FLUSH TABLE t1;
DROP TEMPORARY TABLE t1;
SELECT a,b FROM t1;
a b
UNLOCK TABLES;
DROP TABLE t1, t2;
|
