Debian Codegraph @

mysql-test/suite/perfschema/t/innodb_data_locks.test

raw
1 drop table if exists t1_min;
2 drop table if exists t1_max;
3 drop table if exists t1_part;
4 drop table if exists t1_subpart;
5
6 create table t1_min(
7 c01 TINYINT,
8 c02 TINYINT UNSIGNED,
9 c03 SMALLINT,
10 c04 SMALLINT UNSIGNED,
11 c05 MEDIUMINT,
12 c06 MEDIUMINT UNSIGNED,
13 c07 INT,
14 c08 INT UNSIGNED,
15 c09 BIGINT,
16 c10 BIGINT UNSIGNED,
17 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
18 ) ENGINE=INNODB;
19
20 create table t1_max(
21 c01 TINYINT,
22 c02 TINYINT UNSIGNED,
23 c03 SMALLINT,
24 c04 SMALLINT UNSIGNED,
25 c05 MEDIUMINT,
26 c06 MEDIUMINT UNSIGNED,
27 c07 INT,
28 c08 INT UNSIGNED,
29 c09 BIGINT,
30 c10 BIGINT UNSIGNED,
31 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
32 ) ENGINE=INNODB;
33
34 create table t1_part(
35 a int,
36 b int,
37 c int,
38 PRIMARY KEY(a, b, c)
39 ) ENGINE=INNODB PARTITION BY HASH(a) PARTITIONS 4;
40
41 create table t1_subpart(
42 a int,
43 b int,
44 c int,
45 PRIMARY KEY (a, b, c)
46 ) ENGINE=INNODB
47 PARTITION BY RANGE( a )
48 SUBPARTITION BY HASH( b ) (
49 PARTITION p0 VALUES LESS THAN (1990) (
50 SUBPARTITION s0,
51 SUBPARTITION s1
52 ),
53 PARTITION p1 VALUES LESS THAN (2000) (
54 SUBPARTITION s2,
55 SUBPARTITION s3
56 ),
57 PARTITION p2 VALUES LESS THAN MAXVALUE (
58 SUBPARTITION s4,
59 SUBPARTITION s5
60 )
61 );
62
63 insert into t1_min values
64 (-128, 0,
65 -32768, 0,
66 -8388608, 0,
67 -2147483648, 0,
68 -9223372036854775808, 0);
69
70 insert into t1_max values
71 (127, 255,
72 32767, 65535,
73 8388607, 16777215,
74 2147483647, 4294967295,
75 9223372036854775807, 18446744073709551615);
76
77 insert into t1_part values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
78 insert into t1_subpart values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
79
80 -- connect (con_lock,localhost,root,,)
81 -- connect (con_min_trylock,localhost,root,,)
82 -- connect (con_max_trylock,localhost,root,,)
83 -- connect (con_min_tryinsert,localhost,root,,)
84 -- connect (con_max_tryinsert,localhost,root,,)
85 -- connect (con_trypart,localhost,root,,)
86 -- connect (con_trysubpart,localhost,root,,)
87 -- connect (con_monitor,localhost,root,,)
88
89 -- connection con_lock
90 SET autocommit=0;
91 select * from t1_min for update;
92 select * from t1_max for update;
93 select * from t1_part for update;
94 select * from t1_subpart for update;
95
96 -- connection con_min_trylock
97 -- send
98 select * from t1_min for update;
99
100 -- connection default
101 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
102 where OBJECT_NAME='t1_min' and LOCK_STATUS='WAITING';
103 --source include/wait_condition.inc
104
105 -- connection con_max_trylock
106 -- send
107 select * from t1_max for update;
108
109 -- connection default
110 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
111 where OBJECT_NAME='t1_max' and LOCK_STATUS='WAITING';
112 --source include/wait_condition.inc
113
114 -- connection con_min_tryinsert
115 -- send
116 insert into t1_min values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
117
118 -- connection default
119 let $wait_condition= SELECT COUNT(*)>=2 FROM performance_schema.data_locks
120 where OBJECT_NAME='t1_min' and LOCK_STATUS='WAITING';
121 --source include/wait_condition.inc
122
123 -- connection con_max_tryinsert
124 -- send
125 insert into t1_max values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
126
127 -- connection default
128 let $wait_condition= SELECT COUNT(*)>=2 FROM performance_schema.data_locks
129 where OBJECT_NAME='t1_max' and LOCK_STATUS='WAITING';
130 --source include/wait_condition.inc
131
132 -- connection con_trypart
133 -- send
134 select * from t1_part for update;
135
136 -- connection default
137 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
138 where OBJECT_NAME='t1_part' and LOCK_STATUS='WAITING';
139 --source include/wait_condition.inc
140
141 -- connection con_trysubpart
142 -- send
143 select * from t1_subpart for update;
144
145 -- connection default
146 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
147 where OBJECT_NAME='t1_subpart' and LOCK_STATUS='WAITING';
148 --source include/wait_condition.inc
149
150 -- connection con_monitor
151
152 # Debug
153 # select * from performance_schema.data_locks;
154 # select * from performance_schema.data_lock_waits;
155
156 select
157 ENGINE,
158 OBJECT_SCHEMA, OBJECT_NAME,
159 PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME,
160 LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
161 from performance_schema.data_locks
162 order by OBJECT_SCHEMA, OBJECT_NAME,
163 PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME,
164 LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA;
165
166
167 # Basic integrity
168
169 select ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID
170 from performance_schema.data_locks
171 where (ENGINE = "INNODB")
172 and (ENGINE_TRANSACTION_ID not in
173 (select trx_id from information_schema.innodb_trx));
174
175 -- vertical_results
176 select
177 dl.ENGINE,
178 dl.OBJECT_SCHEMA,
179 dl.OBJECT_NAME,
180 dl.PARTITION_NAME,
181 dl.SUBPARTITION_NAME,
182 dl.INDEX_NAME,
183 dl.LOCK_TYPE,
184 dl.LOCK_MODE,
185 dl.LOCK_STATUS,
186 dl.LOCK_DATA,
187
188 it.trx_state,
189 it.trx_weight,
190 it.trx_query,
191 it.trx_operation_state,
192 it.trx_tables_in_use,
193 it.trx_tables_locked,
194 it.trx_lock_structs,
195 it.trx_rows_locked,
196 it.trx_rows_modified,
197 it.trx_concurrency_tickets,
198 it.trx_isolation_level,
199 it.trx_unique_checks,
200 it.trx_foreign_key_checks,
201 it.trx_last_foreign_key_error,
202 it.trx_adaptive_hash_latched,
203 it.trx_adaptive_hash_timeout,
204 it.trx_is_read_only,
205 it.trx_autocommit_non_locking
206
207 from performance_schema.data_locks dl inner join information_schema.innodb_trx it
208 on dl.ENGINE_TRANSACTION_ID = it.TRX_ID
209 order by ENGINE,
210 OBJECT_SCHEMA, OBJECT_NAME,
211 PARTITION_NAME, SUBPARTITION_NAME, INDEX_NAME,
212 trx_query, lock_status, LOCK_DATA;
213
214 -- horizontal_results
215
216 -- connection default
217
218 -- disconnect con_lock
219 -- disconnect con_min_trylock
220 -- disconnect con_max_trylock
221 -- disconnect con_min_tryinsert
222 -- disconnect con_max_tryinsert
223 -- disconnect con_trypart
224 -- disconnect con_trysubpart
225 -- disconnect con_monitor
226
227 drop table t1_min;
228 drop table t1_max;
229 drop table t1_part;
230 drop table t1_subpart;
231
232 # BUG 94166: PERFORMANCE_SCHEMA.DATA_LOCKS.LOCK_DATA SHOULD SHOW PK COLUMNS FOR UNIQUE SEC
233
234 CREATE TABLE t (
235 id INT NOT NULL,
236 u INT NOT NULL,
237 s INT NOT NULL,
238 u1 INT NOT NULL,
239 u2 INT NOT NULL,
240 s1 INT NOT NULL,
241 s2 INT NOT NULL,
242 PRIMARY KEY k_id(id),
243 KEY k_s(s),
244 KEY k_s1_s2(s1,s2),
245 UNIQUE KEY k_u(u),
246 UNIQUE KEY k_u1_u2(u1,u2)
247 ) Engine = InnoDB;
248
249 INSERT INTO t VALUES (1,2,3,4,5,6,7), (10,20,30,40,50,60,70);
250 BEGIN;
251
252 SELECT * FROM t WHERE id=1 FOR UPDATE;
253
254 SELECT * FROM t WHERE u=2 FOR UPDATE;
255
256 SELECT * FROM t WHERE u1=3 FOR UPDATE;
257
258 SELECT * FROM t WHERE s=3 FOR UPDATE;
259
260 SELECT * FROM t WHERE u1=4 FOR UPDATE;
261
262 SELECT * FROM t WHERE u1=40 AND u2=50 FOR UPDATE;
263
264 SELECT * FROM t WHERE s1=6 FOR UPDATE;
265
266 SELECT * FROM t WHERE s1=60 AND s2=70 FOR UPDATE;
267
268 SELECT lock_type,index_name,lock_mode,lock_data
269 FROM performance_schema.data_locks
270 WHERE object_schema="test" AND object_name="t"
271 ORDER BY 1,2,3,4;
272
273 ROLLBACK;
274 DROP TABLE t;