Debian Codegraph @

mysql-test/suite/innodb/r/high_prio_trx_7.result

raw
1 # Scenario:1
2 # T1= ({W(B)}, HIGH_PRIORITY)
3 # T2= ( Try to take table level lock )
4 # Outcome: T2 waits for t1 to acquire table lock
5 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
6 INSERT INTO t1 VALUES (0);
7
8 # On connection 1
9 include/start_transaction_high_prio.inc
10 START TRANSACTION /* HIGH PRIORITY */;
11 UPDATE t1 SET c1=1 WHERE c1=0;
12
13 # On connection 2
14 LOCK TABLE t1 WRITE/* From connection 2 */;
15
16 # On connection 1
17 COMMIT;
18
19 # On connection 2.
20 SELECT * FROM t1;
21 c1
22 1
23 UNLOCK TABLES;
24 include/assert.inc ['There is a 1 in t1']
25 DROP TABLE t1;
26 # Scenario:2
27 # T1= (LOCK TABLE)
28 # T2= ({ W(B)},HIGH_PRIORITY)
29 # Outcome : high prio transaction T2 waits for T1.
30 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
31 INSERT INTO t1 VALUES (0);
32
33 # On connection 2
34 LOCK TABLE t1 WRITE/* From connection 2 */;
35 UPDATE t1 SET c1=2 WHERE c1=0;
36
37 # On connection 1
38 include/start_transaction_high_prio.inc
39 START TRANSACTION /* HIGH PRIORITY */;
40 UPDATE t1 SET c1=1 WHERE c1=0;
41 # On connection 2
42 COMMIT;
43 UNLOCK TABLES;
44 COMMIT;
45 SELECT * FROM t1;
46 c1
47 2
48 DROP TABLE t1;
49 # Scenario:3
50 # T1= ({R(B)}, HIGH_PRIORITY)
51 # T2= ({ R(B)},HIGH_PRIORITY)
52 # Outcome: Both T1 and T2 continue as they are read only.
53 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
54 INSERT INTO t1 VALUES (0);
55
56 # On connection 1
57 START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
58 SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
59 c1
60 0
61
62 # On connection 2
63 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
64 START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
65 SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
66 c1
67 0
68 UPDATE t1 SET c1=1 WHERE c1=0;
69 ERROR 25006: Cannot execute statement in a READ ONLY transaction.
70 COMMIT;
71 COMMIT;
72 include/assert.inc ['There is a 1 in t1']
73 DROP TABLE t1;
74 # Scenario:4
75 # Start of transaction causes table locks acquired with LOCK TABLES to be released
76 # LOCK TABLES Implicitly commit any active transaction
77 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
78 CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
79 INSERT INTO t1 VALUES (0);
80 INSERT INTO t2 VALUES (0);
81 LOCK TABLE t2 WRITE;
82 # In_use is 1
83 SHOW OPEN TABLES LIKE 't2';
84 Database Table In_use Name_locked
85 test t2 1 0
86 include/start_transaction_high_prio.inc
87 START TRANSACTION /* HIGH PRIORITY */;
88 # In_use is 0 , its released due to start transaction statement
89 SHOW OPEN TABLES LIKE 't2';
90 Database Table In_use Name_locked
91 test t2 0 0
92 UPDATE t1 SET c1=1;
93 # Implicitly commits active transaction
94 LOCK TABLE t1 READ;
95 UNLOCK TABLES;
96 include/assert.inc ['There is a 1 in t1']
97 DROP TABLES t1,t2;
98 # Scenario:5
99 # Multiple concurrent high prio transactions with the same priority.
100 # Outcome : Transaction which is marked high prio first completes first and other high prio txns wait
101 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
102 INSERT INTO t1 VALUES (0);
103 include/start_transaction_high_prio.inc
104 START TRANSACTION /* HIGH PRIORITY */;
105 # connection 1:
106 INSERT INTO t1 VALUES (0);
107 ERROR 23000: Duplicate entry '0' for key 't1.PRIMARY'
108 UPDATE t1 SET c1=1 WHERE c1=0;
109 include/start_transaction_high_prio.inc
110 START TRANSACTION /* HIGH PRIORITY */;
111 # connection 2: wait - update
112 UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */;
113 include/start_transaction_high_prio.inc
114 START TRANSACTION /* HIGH PRIORITY */;
115 # connection 3: wait - delete
116 DELETE FROM t1 WHERE c1=1 /* From connection 3 */;
117 include/start_transaction_high_prio.inc
118 START TRANSACTION /* HIGH PRIORITY */;
119 # connection 4: wait - insert
120 # wait over
121 INSERT INTO t1 VALUES (1) /* From connection 4 */;
122 include/start_transaction_high_prio.inc
123 START TRANSACTION /* HIGH PRIORITY */;
124 # connection 5: Insert goes but as it new row
125 INSERT INTO t1 VALUES (50);
126 # connection 1:
127 COMMIT;
128 # connection 2: Reap UPDATE t1 SET c1=2 WHERE c1=1
129 COMMIT;
130 # connection 3: Reap DELETE FROM t1 WHERE c1=1
131 COMMIT;
132 # connection 4: Reap INSERT INTO t1 VALUES (1)
133 COMMIT;
134 INSERT INTO t1 VALUES (3);
135 UPDATE t1 SET c1=4 WHERE c1=2;
136 INSERT INTO t1 VALUES (50);
137 COMMIT;
138 ERROR 23000: Duplicate entry '50' for key 't1.PRIMARY'
139 include/assert.inc ['There is a 3 in t1']
140 # Expected values : 1,3,4,50
141 SELECT * FROM t1 order by c1;
142 c1
143 1
144 3
145 4
146 50
147 DROP TABLES t1;
148 # Scenario:6
149 # Mark XA transaction as high prio.
150 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
151 INSERT INTO t1 VALUES (0),(1);
152 START TRANSACTION;
153 UPDATE t1 SET c1=11 WHERE c1=0;
154 XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
155 UPDATE t1 SET c1=10 WHERE c1=0;
156 XA END 'xa_high_prio';
157 XA PREPARE 'xa_high_prio';
158 XA COMMIT 'xa_high_prio';
159 COMMIT;
160 ERROR HY000: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
161 SELECT * FROM t1;
162 c1
163 1
164 10
165 DROP TABLE t1;
166 # Scenario:7
167 # Multiple XA transaction as high prio.
168 # Outcome : detect deadlock .
169 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
170 INSERT INTO t1 VALUES (0),(1);
171 START TRANSACTION;
172 UPDATE t1 SET c1=11 WHERE c1=0;
173 XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
174 UPDATE t1 SET c1=10 WHERE c1=0;
175 XA START 'second_xa_high_prio' /* HIGH PRIORITY */ ;
176 UPDATE t1 SET c1=13 WHERE c1=0;
177 XA END 'xa_high_prio';
178 XA PREPARE 'xa_high_prio';
179 XA COMMIT 'xa_high_prio';
180 COMMIT;
181 ERROR HY000: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
182 Reap UPDATE t1 SET c1=13 WHERE c1=0;
183 SELECT * FROM t1;
184 c1
185 1
186 10
187 DROP TABLE t1;
188 #
189 # Bug#21075892 SHUTDOWN HANG, WAITING FOR ASYNC ROLLBACK TO FINISH
190 #
191 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE=InnoDB;
192 INSERT INTO t1 values(10, 10), (20, 20), (30, 30);
193 #connection 0
194 START TRANSACTION;
195 #Lock record (col1 = 20)
196 UPDATE t1 set col2 = 25 where col1 = 20;
197 #connection 1
198 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
199 include/start_transaction_high_prio.inc
200 START TRANSACTION /* HIGH PRIORITY */;
201 #Semi-consistent read: Attempt lock (col1 = 20) and skip in SQL
202 UPDATE t1 SET col2 = 20 where col1 > 10 and col1 <30 and col2 = 30;
203 COMMIT WORK;
204 #connection 0
205 ROLLBACK WORK;
206 DROP TABLE t1;