Debian Codegraph @

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

raw
1 # does not conflict
2 INSERT INTO t1 VALUES (5,5);
3 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
4 FROM performance_schema.data_locks
5 WHERE object_schema='test' AND object_name = 't1';
6 index_name lock_type lock_mode lock_status lock_data
7 NULL TABLE IX GRANTED NULL
8 COMMIT;
9 SELECT * FROM t1;
10 a b
11 0 0
12 1 1
13 3 3
14 4 4
15 5 5
16 # conflicts on `a`, but not on `b`
17 INSERT INTO t1 VALUES (0,5);
18 ERROR 23000: Duplicate entry '0' for key 't1.a'
19 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
20 FROM performance_schema.data_locks
21 WHERE object_schema='test' AND object_name = 't1';
22 index_name lock_type lock_mode lock_status lock_data
23 NULL TABLE IX GRANTED NULL
24 a RECORD S GRANTED 0, 0
25 COMMIT;
26 SELECT * FROM t1;
27 a b
28 0 0
29 1 1
30 3 3
31 4 4
32 # conflicts on `b`, but not on `a`
33 INSERT INTO t1 VALUES (5,0);
34 ERROR 23000: Duplicate entry '0' for key 't1.PRIMARY'
35 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
36 FROM performance_schema.data_locks
37 WHERE object_schema='test' AND object_name = 't1';
38 index_name lock_type lock_mode lock_status lock_data
39 NULL TABLE IX GRANTED NULL
40 PRIMARY RECORD S,REC_NOT_GAP GRANTED 0
41 COMMIT;
42 SELECT * FROM t1;
43 a b
44 0 0
45 1 1
46 3 3
47 4 4
48 # conflicts on both `a` and `b`
49 INSERT INTO t1 VALUES (0,0);
50 ERROR 23000: Duplicate entry '0' for key 't1.PRIMARY'
51 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
52 FROM performance_schema.data_locks
53 WHERE object_schema='test' AND object_name = 't1';
54 index_name lock_type lock_mode lock_status lock_data
55 NULL TABLE IX GRANTED NULL
56 PRIMARY RECORD S,REC_NOT_GAP GRANTED 0
57 COMMIT;
58 SELECT * FROM t1;
59 a b
60 0 0
61 1 1
62 3 3
63 4 4
64 # conflicts on `a` with deleted, but not on `b`
65 INSERT INTO t1 VALUES (2,5);
66 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
67 FROM performance_schema.data_locks
68 WHERE object_schema='test' AND object_name = 't1';
69 index_name lock_type lock_mode lock_status lock_data
70 NULL TABLE IX GRANTED NULL
71 a RECORD S GRANTED 2, 2
72 a RECORD S,GAP GRANTED 2, 5
73 a RECORD S,GAP GRANTED 3, 3
74 COMMIT;
75 SELECT * FROM t1;
76 a b
77 0 0
78 1 1
79 2 5
80 3 3
81 4 4
82 # conflicts on `b` with deleted, but not on `a`
83 INSERT INTO t1 VALUES (5,2);
84 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
85 FROM performance_schema.data_locks
86 WHERE object_schema='test' AND object_name = 't1';
87 index_name lock_type lock_mode lock_status lock_data
88 NULL TABLE IX GRANTED NULL
89 PRIMARY RECORD S,REC_NOT_GAP GRANTED 2
90 COMMIT;
91 SELECT * FROM t1;
92 a b
93 0 0
94 1 1
95 3 3
96 4 4
97 5 2
98 # conflicts on both `a` and `b` with deleted row
99 INSERT INTO t1 VALUES (2,2);
100 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
101 FROM performance_schema.data_locks
102 WHERE object_schema='test' AND object_name = 't1';
103 index_name lock_type lock_mode lock_status lock_data
104 NULL TABLE IX GRANTED NULL
105 PRIMARY RECORD S,REC_NOT_GAP GRANTED 2
106 a RECORD S GRANTED 2, 2
107 a RECORD S,GAP GRANTED 3, 3
108 COMMIT;
109 SELECT * FROM t1;
110 a b
111 0 0
112 1 1
113 2 2
114 3 3
115 4 4
116 # conflicts on `a` with deleted, and with `b` on not-deleted
117 INSERT INTO t1 VALUES (2,0);
118 ERROR 23000: Duplicate entry '0' for key 't1.PRIMARY'
119 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
120 FROM performance_schema.data_locks
121 WHERE object_schema='test' AND object_name = 't1';
122 index_name lock_type lock_mode lock_status lock_data
123 NULL TABLE IX GRANTED NULL
124 PRIMARY RECORD S,REC_NOT_GAP GRANTED 0
125 COMMIT;
126 SELECT * FROM t1;
127 a b
128 0 0
129 1 1
130 3 3
131 4 4
132 # conflicts on `b` with deleted, and with `a` on not-deleted
133 INSERT INTO t1 VALUES (0,2);
134 ERROR 23000: Duplicate entry '0' for key 't1.a'
135 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
136 FROM performance_schema.data_locks
137 WHERE object_schema='test' AND object_name = 't1';
138 index_name lock_type lock_mode lock_status lock_data
139 NULL TABLE IX GRANTED NULL
140 PRIMARY RECORD S,REC_NOT_GAP GRANTED 2
141 a RECORD S GRANTED 0, 0
142 COMMIT;
143 SELECT * FROM t1;
144 a b
145 0 0
146 1 1
147 3 3
148 4 4
149 # does not conflict
150 REPLACE INTO t1 VALUES (5,5);
151 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
152 FROM performance_schema.data_locks
153 WHERE object_schema='test' AND object_name = 't1';
154 index_name lock_type lock_mode lock_status lock_data
155 NULL TABLE IX GRANTED NULL
156 COMMIT;
157 SELECT * FROM t1;
158 a b
159 0 0
160 1 1
161 3 3
162 4 4
163 5 5
164 # conflicts on `a`, but not on `b`
165 REPLACE INTO t1 VALUES (0,5);
166 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
167 FROM performance_schema.data_locks
168 WHERE object_schema='test' AND object_name = 't1';
169 index_name lock_type lock_mode lock_status lock_data
170 NULL TABLE IX GRANTED NULL
171 PRIMARY RECORD X,REC_NOT_GAP GRANTED 0
172 a RECORD X GRANTED 0, 0
173 a RECORD X GRANTED 1, 1
174 a RECORD X,GAP GRANTED 0, 5
175 COMMIT;
176 SELECT * FROM t1;
177 a b
178 0 5
179 1 1
180 3 3
181 4 4
182 # conflicts on `b`, but not on `a`
183 REPLACE INTO t1 VALUES (5,0);
184 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
185 FROM performance_schema.data_locks
186 WHERE object_schema='test' AND object_name = 't1';
187 index_name lock_type lock_mode lock_status lock_data
188 NULL TABLE IX GRANTED NULL
189 PRIMARY RECORD X,REC_NOT_GAP GRANTED 0
190 COMMIT;
191 SELECT * FROM t1;
192 a b
193 1 1
194 3 3
195 4 4
196 5 0
197 # conflicts on both `a` and `b`
198 REPLACE INTO t1 VALUES (0,0);
199 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
200 FROM performance_schema.data_locks
201 WHERE object_schema='test' AND object_name = 't1';
202 index_name lock_type lock_mode lock_status lock_data
203 NULL TABLE IX GRANTED NULL
204 PRIMARY RECORD X,REC_NOT_GAP GRANTED 0
205 a RECORD X GRANTED 1, 1
206 a RECORD X,GAP GRANTED 0, 0
207 a RECORD X,REC_NOT_GAP GRANTED 0, 0
208 COMMIT;
209 SELECT * FROM t1;
210 a b
211 0 0
212 1 1
213 3 3
214 4 4
215 # conflicts on `a` with deleted, but not on `b`
216 REPLACE INTO t1 VALUES (2,5);
217 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
218 FROM performance_schema.data_locks
219 WHERE object_schema='test' AND object_name = 't1';
220 index_name lock_type lock_mode lock_status lock_data
221 NULL TABLE IX GRANTED NULL
222 a RECORD X GRANTED 2, 2
223 a RECORD X GRANTED 3, 3
224 a RECORD X,GAP GRANTED 2, 5
225 COMMIT;
226 SELECT * FROM t1;
227 a b
228 0 0
229 1 1
230 2 5
231 3 3
232 4 4
233 # conflicts on `b` with deleted, but not on `a`
234 REPLACE INTO t1 VALUES (5,2);
235 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
236 FROM performance_schema.data_locks
237 WHERE object_schema='test' AND object_name = 't1';
238 index_name lock_type lock_mode lock_status lock_data
239 NULL TABLE IX GRANTED NULL
240 PRIMARY RECORD X,REC_NOT_GAP GRANTED 2
241 COMMIT;
242 SELECT * FROM t1;
243 a b
244 0 0
245 1 1
246 3 3
247 4 4
248 5 2
249 # conflicts on both `a` and `b` with deleted row
250 REPLACE INTO t1 VALUES (2,2);
251 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
252 FROM performance_schema.data_locks
253 WHERE object_schema='test' AND object_name = 't1';
254 index_name lock_type lock_mode lock_status lock_data
255 NULL TABLE IX GRANTED NULL
256 PRIMARY RECORD X,REC_NOT_GAP GRANTED 2
257 a RECORD X GRANTED 2, 2
258 a RECORD X GRANTED 3, 3
259 COMMIT;
260 SELECT * FROM t1;
261 a b
262 0 0
263 1 1
264 2 2
265 3 3
266 4 4
267 # conflicts on `a` with deleted, and with `b` on not-deleted
268 REPLACE INTO t1 VALUES (2,0);
269 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
270 FROM performance_schema.data_locks
271 WHERE object_schema='test' AND object_name = 't1';
272 index_name lock_type lock_mode lock_status lock_data
273 NULL TABLE IX GRANTED NULL
274 PRIMARY RECORD X,REC_NOT_GAP GRANTED 0
275 a RECORD X GRANTED 2, 2
276 a RECORD X GRANTED 3, 3
277 a RECORD X,GAP GRANTED 2, 0
278 COMMIT;
279 SELECT * FROM t1;
280 a b
281 1 1
282 2 0
283 3 3
284 4 4
285 # conflicts on `b` with deleted, and with `a` on not-deleted
286 REPLACE INTO t1 VALUES (0,2);
287 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
288 FROM performance_schema.data_locks
289 WHERE object_schema='test' AND object_name = 't1';
290 index_name lock_type lock_mode lock_status lock_data
291 NULL TABLE IX GRANTED NULL
292 PRIMARY RECORD X,REC_NOT_GAP GRANTED 0
293 PRIMARY RECORD X,REC_NOT_GAP GRANTED 2
294 a RECORD X GRANTED 0, 0
295 a RECORD X GRANTED 1, 1
296 a RECORD X,GAP GRANTED 0, 2
297 COMMIT;
298 SELECT * FROM t1;
299 a b
300 0 2
301 1 1
302 3 3
303 4 4