Debian Codegraph @

mysql-test/suite/innodb/t/innodb_i_s_innodb_trx.test

raw
1
2 #
3 # Test that transaction data is correctly "visualized" in
4 # INFORMATION_SCHEMA.INNODB_TRX
5 #
6
7 # Make sure the locks keep waiting until they are released,
8 # even on a busy system.
9 SET GLOBAL innodb_lock_wait_timeout=600;
10
11 DESCRIBE INFORMATION_SCHEMA.INNODB_TRX;
12
13 -- disable_warnings
14 DROP TABLE IF EXISTS t1;
15 -- enable_warnings
16
17 CREATE TABLE t1 (
18 c01 INT,
19 c02 INT,
20 PRIMARY KEY (c01)
21 ) ENGINE=INNODB STATS_AUTO_RECALC=0;
22
23 INSERT INTO t1 VALUES
24 (1,2),(2,4),(3,6),(4,8);
25
26 CREATE TABLE t2 (
27 c01 INT,
28 c02 INT,
29 PRIMARY KEY (c01),
30 CONSTRAINT fk1 FOREIGN KEY (c02) REFERENCES t1 (c01)
31 ) ENGINE=INNODB STATS_AUTO_RECALC=0;
32
33 INSERT INTO t2 VALUES
34 (1,1),(2,2),(3,3);
35
36 -- source include/count_sessions.inc
37
38 -- connect (con_trx,localhost,root,,)
39 -- connect (con_verify_innodb_trx,localhost,root,,)
40
41 -- connection con_trx
42 SET autocommit=0;
43 INSERT INTO t1 VALUES (5,10);
44 SELECT * FROM t1 FOR UPDATE;
45
46 let $wait_timeout= 300;
47 let $wait_condition=
48 SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX;
49 -- source include/wait_condition.inc
50
51 -- connection con_verify_innodb_trx
52 SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked,
53 trx_rows_locked, trx_rows_modified, trx_concurrency_tickets,
54 trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
55 FROM INFORMATION_SCHEMA.INNODB_TRX;
56
57 -- connection con_trx
58 ROLLBACK;
59 SET FOREIGN_KEY_CHECKS = 0;
60 SET UNIQUE_CHECKS = 0;
61 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
62 BEGIN;
63 INSERT INTO t1 VALUES (6,12);
64
65 let $wait_timeout= 300;
66 let $wait_condition=
67 SELECT trx_unique_checks = 0 FROM INFORMATION_SCHEMA.INNODB_TRX;
68 -- source include/wait_condition.inc
69
70 -- connection con_verify_innodb_trx
71 SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
72 FROM INFORMATION_SCHEMA.INNODB_TRX;
73
74 -- connection con_trx
75 ROLLBACK;
76 SET FOREIGN_KEY_CHECKS = 1;
77 SET UNIQUE_CHECKS = 1;
78 BEGIN;
79 -- error 1452
80 INSERT INTO t2 VALUES (4,10);
81
82 let $wait_timeout= 300;
83 let $wait_condition=
84 SELECT trx_unique_checks = 1 FROM INFORMATION_SCHEMA.INNODB_TRX;
85 -- source include/wait_condition.inc
86
87 -- connection con_verify_innodb_trx
88 SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error
89 FROM INFORMATION_SCHEMA.INNODB_TRX;
90
91 -- connection default
92
93 -- disconnect con_trx
94 -- disconnect con_verify_innodb_trx
95
96 # Check that trx_schedule_weight is computed and reported:
97 # con_3 waits for con_2, which waits for con_1, so we want their weights to be:
98 # con_1 -> NULL
99 # con_2 -> 2
100 # con_3 -> 1
101 # A helper table to map con_1,...,con_3 to their connection ids
102 CREATE TABLE t0 (
103 id INT PRIMARY KEY,
104 trx_mysql_thread_id BIGINT
105 ) ENGINE=InnoDB;
106
107 -- connect (con_1,localhost,root,,)
108 INSERT INTO t0 VALUES (1, CONNECTION_ID());
109 BEGIN;
110 SELECT * FROM t1 FOR SHARE;
111
112 -- connect (con_2,localhost,root,,)
113 INSERT INTO t0 VALUES (2, CONNECTION_ID());
114 BEGIN;
115 -- send SELECT * FROM t1 FOR UPDATE
116
117 -- connection default
118 let $wait_condition=
119 SELECT trx_schedule_weight > 0
120 FROM INFORMATION_SCHEMA.INNODB_TRX
121 JOIN t0 USING(trx_mysql_thread_id)
122 WHERE t0.id = 2;
123 -- source include/wait_condition.inc
124
125 -- connect (con_3,localhost,root,,)
126 INSERT INTO t0 VALUES (3, CONNECTION_ID());
127 BEGIN;
128 -- send SELECT * FROM t1 FOR SHARE
129
130 -- connection default
131 let $wait_condition=
132 SELECT trx_schedule_weight > 0
133 FROM INFORMATION_SCHEMA.INNODB_TRX
134 JOIN t0 USING(trx_mysql_thread_id)
135 WHERE t0.id = 3;
136 -- source include/wait_condition.inc
137
138 SELECT t0.id, trx_schedule_weight
139 FROM INFORMATION_SCHEMA.INNODB_TRX
140 JOIN t0 USING(trx_mysql_thread_id)
141 ORDER BY t0.id;
142
143 -- connection con_1
144 ROLLBACK;
145
146 -- connection con_2
147 -- reap
148 ROLLBACK;
149
150 -- connection con_3
151 -- reap
152 ROLLBACK;
153
154 -- connection default
155 -- disconnect con_1
156 -- disconnect con_2
157 -- disconnect con_3
158
159
160 DROP TABLE t2;
161 DROP TABLE t1;
162 DROP TABLE t0;
163
164 -- source include/wait_until_count_sessions.inc
165
166 SET GLOBAL innodb_lock_wait_timeout=default;