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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
# Scenario:1
# T1= ({W(B)}, HIGH_PRIORITY)
# T2= ( Try to take table level lock )
# Outcome: T2 waits for t1 to acquire table lock
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0);
# On connection 1
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
UPDATE t1 SET c1=1 WHERE c1=0;
# On connection 2
LOCK TABLE t1 WRITE/* From connection 2 */;
# On connection 1
COMMIT;
# On connection 2.
SELECT * FROM t1;
c1
1
UNLOCK TABLES;
include/assert.inc ['There is a 1 in t1']
DROP TABLE t1;
# Scenario:2
# T1= (LOCK TABLE)
# T2= ({ W(B)},HIGH_PRIORITY)
# Outcome : high prio transaction T2 waits for T1.
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0);
# On connection 2
LOCK TABLE t1 WRITE/* From connection 2 */;
UPDATE t1 SET c1=2 WHERE c1=0;
# On connection 1
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
UPDATE t1 SET c1=1 WHERE c1=0;
# On connection 2
COMMIT;
UNLOCK TABLES;
COMMIT;
SELECT * FROM t1;
c1
2
DROP TABLE t1;
# Scenario:3
# T1= ({R(B)}, HIGH_PRIORITY)
# T2= ({ R(B)},HIGH_PRIORITY)
# Outcome: Both T1 and T2 continue as they are read only.
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0);
# On connection 1
START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
c1
0
# On connection 2
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
c1
0
UPDATE t1 SET c1=1 WHERE c1=0;
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
COMMIT;
COMMIT;
include/assert.inc ['There is a 1 in t1']
DROP TABLE t1;
# Scenario:4
# Start of transaction causes table locks acquired with LOCK TABLES to be released
# LOCK TABLES Implicitly commit any active transaction
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0);
INSERT INTO t2 VALUES (0);
LOCK TABLE t2 WRITE;
# In_use is 1
SHOW OPEN TABLES LIKE 't2';
Database Table In_use Name_locked
test t2 1 0
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# In_use is 0 , its released due to start transaction statement
SHOW OPEN TABLES LIKE 't2';
Database Table In_use Name_locked
test t2 0 0
UPDATE t1 SET c1=1;
# Implicitly commits active transaction
LOCK TABLE t1 READ;
UNLOCK TABLES;
include/assert.inc ['There is a 1 in t1']
DROP TABLES t1,t2;
# Scenario:5
# Multiple concurrent high prio transactions with the same priority.
# Outcome : Transaction which is marked high prio first completes first and other high prio txns wait
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0);
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# connection 1:
INSERT INTO t1 VALUES (0);
ERROR 23000: Duplicate entry '0' for key 't1.PRIMARY'
UPDATE t1 SET c1=1 WHERE c1=0;
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# connection 2: wait - update
UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */;
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# connection 3: wait - delete
DELETE FROM t1 WHERE c1=1 /* From connection 3 */;
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# connection 4: wait - insert
# wait over
INSERT INTO t1 VALUES (1) /* From connection 4 */;
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
# connection 5: Insert goes but as it new row
INSERT INTO t1 VALUES (50);
# connection 1:
COMMIT;
# connection 2: Reap UPDATE t1 SET c1=2 WHERE c1=1
COMMIT;
# connection 3: Reap DELETE FROM t1 WHERE c1=1
COMMIT;
# connection 4: Reap INSERT INTO t1 VALUES (1)
COMMIT;
INSERT INTO t1 VALUES (3);
UPDATE t1 SET c1=4 WHERE c1=2;
INSERT INTO t1 VALUES (50);
COMMIT;
ERROR 23000: Duplicate entry '50' for key 't1.PRIMARY'
include/assert.inc ['There is a 3 in t1']
# Expected values : 1,3,4,50
SELECT * FROM t1 order by c1;
c1
1
3
4
50
DROP TABLES t1;
# Scenario:6
# Mark XA transaction as high prio.
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0),(1);
START TRANSACTION;
UPDATE t1 SET c1=11 WHERE c1=0;
XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
UPDATE t1 SET c1=10 WHERE c1=0;
XA END 'xa_high_prio';
XA PREPARE 'xa_high_prio';
XA COMMIT 'xa_high_prio';
COMMIT;
ERROR HY000: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
SELECT * FROM t1;
c1
1
10
DROP TABLE t1;
# Scenario:7
# Multiple XA transaction as high prio.
# Outcome : detect deadlock .
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0),(1);
START TRANSACTION;
UPDATE t1 SET c1=11 WHERE c1=0;
XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
UPDATE t1 SET c1=10 WHERE c1=0;
XA START 'second_xa_high_prio' /* HIGH PRIORITY */ ;
UPDATE t1 SET c1=13 WHERE c1=0;
XA END 'xa_high_prio';
XA PREPARE 'xa_high_prio';
XA COMMIT 'xa_high_prio';
COMMIT;
ERROR HY000: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
Reap UPDATE t1 SET c1=13 WHERE c1=0;
SELECT * FROM t1;
c1
1
10
DROP TABLE t1;
#
# Bug#21075892 SHUTDOWN HANG, WAITING FOR ASYNC ROLLBACK TO FINISH
#
CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE=InnoDB;
INSERT INTO t1 values(10, 10), (20, 20), (30, 30);
#connection 0
START TRANSACTION;
#Lock record (col1 = 20)
UPDATE t1 set col2 = 25 where col1 = 20;
#connection 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
include/start_transaction_high_prio.inc
START TRANSACTION /* HIGH PRIORITY */;
#Semi-consistent read: Attempt lock (col1 = 20) and skip in SQL
UPDATE t1 SET col2 = 20 where col1 > 10 and col1 <30 and col2 = 30;
COMMIT WORK;
#connection 0
ROLLBACK WORK;
DROP TABLE t1;
|
