| 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; |