Debian Codegraph @

mysql-test/suite/handler/savepoint.inc

raw
1 --echo #
2 --echo # Testing savepoints with handlers that supports it
3 --echo #
4 connect(con1, localhost, root,,);
5 connect(con2, localhost, root,,);
6 connect(con3, localhost, root,,);
7 connection default;
8 --echo #
9 --echo # ROLLBACK TO SAVEPOINT releases transactional locks,
10 --echo # but has no effect on open HANDLERs
11 --echo #
12 create table t1 (a int, key a (a));
13 insert into t1 (a) values (1), (2), (3), (4), (5);
14 create table t2 like t1;
15 create table t3 like t1;
16 begin;
17 --echo # Have something before the savepoint
18 select * from t3;
19 savepoint sv;
20 handler t1 open;
21 handler t1 read a first;
22 handler t1 read a next;
23 select * from t2;
24 connection con1;
25 --echo # Sending:
26 --send drop table t1
27 connection con2;
28 --echo # Sending:
29 --send drop table t2
30 connection default;
31 --echo # Let DROP TABLE statements sync in. We must use
32 --echo # a separate connection for that, because otherwise SELECT
33 --echo # will auto-close the HANDLERs, becaues there are pending
34 --echo # exclusive locks against them.
35 connection con3;
36 --echo # Waiting for 'drop table t1' to get blocked...
37 let $wait_condition=select count(*)=1 from information_schema.processlist
38 where state='Waiting for table metadata lock' and
39 info='drop table t1';
40 --source include/wait_condition.inc
41 --echo # Waiting for 'drop table t2' to get blocked...
42 let $wait_condition=select count(*)=1 from information_schema.processlist
43 where state='Waiting for table metadata lock' and
44 info='drop table t2';
45 --source include/wait_condition.inc
46 --echo # Demonstrate that t2 lock was released and t2 was dropped
47 --echo # after ROLLBACK TO SAVEPOINT
48 connection default;
49 rollback to savepoint sv;
50 connection con2;
51 --echo # Reaping 'drop table t2'...
52 --reap
53 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
54 --echo # lock.
55 connection default;
56 handler t1 read a next;
57 handler t1 read a next;
58 --echo # Demonstrate that the drop will go through as soon as we close the
59 --echo # HANDLER
60 handler t1 close;
61 connection con1;
62 --echo # Reaping 'drop table t1'...
63 --reap
64 connection default;
65 commit;
66 drop table t3;
67 --echo #
68 --echo # A few special cases when using SAVEPOINT/ROLLBACK TO
69 --echo # SAVEPOINT and HANDLER.
70 --echo #
71 --echo # Show that rollback to the savepoint taken in the beginning
72 --echo # of the transaction doesn't release mdl lock on
73 --echo # the HANDLER that was opened later.
74 --echo #
75 create table t1 (a int, key using btree (a));
76 insert into t1 (a) values (1), (2), (3), (4), (5);
77 create table t2 like t1;
78 begin;
79 savepoint sv;
80 handler t1 open;
81 handler t1 read a first;
82 handler t1 read a next;
83 select * from t2;
84 connection con1;
85 --echo # Sending:
86 --send drop table t1
87 connection con2;
88 --echo # Sending:
89 --send drop table t2
90 connection default;
91 --echo # Let DROP TABLE statements sync in. We must use
92 --echo # a separate connection for that, because otherwise SELECT
93 --echo # will auto-close the HANDLERs, becaues there are pending
94 --echo # exclusive locks against them.
95 connection con3;
96 --echo # Waiting for 'drop table t1' to get blocked...
97 let $wait_condition=select count(*)=1 from information_schema.processlist
98 where state='Waiting for table metadata lock' and
99 info='drop table t1';
100 --source include/wait_condition.inc
101 --echo # Waiting for 'drop table t2' to get blocked...
102 let $wait_condition=select count(*)=1 from information_schema.processlist
103 where state='Waiting for table metadata lock' and
104 info='drop table t2';
105 --source include/wait_condition.inc
106 --echo # Demonstrate that t2 lock was released and t2 was dropped
107 --echo # after ROLLBACK TO SAVEPOINT
108 connection default;
109 rollback to savepoint sv;
110 connection con2;
111 --echo # Reaping 'drop table t2'...
112 --reap
113 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
114 --echo # lock.
115 connection default;
116 handler t1 read a next;
117 handler t1 read a next;
118 --echo # Demonstrate that the drop will go through as soon as we close the
119 --echo # HANDLER
120 handler t1 close;
121 connection con1;
122 --echo # Reaping 'drop table t1'...
123 --reap
124 connection default;
125 commit;
126 --echo #
127 --echo # Show that rollback to the savepoint taken in the beginning
128 --echo # of the transaction works properly (no valgrind warnins, etc),
129 --echo # even though it's done after the HANDLER mdl lock that was there
130 --echo # at the beginning is released and added again.
131 --echo #
132 create table t1 (a int, key using btree (a));
133 insert into t1 (a) values (1), (2), (3), (4), (5);
134 create table t2 like t1;
135 create table t3 like t1;
136 insert into t3 (a) select a from t1;
137 begin;
138 handler t1 open;
139 savepoint sv;
140 handler t1 read a first;
141 select * from t2;
142 handler t1 close;
143 handler t3 open;
144 handler t3 read a first;
145 rollback to savepoint sv;
146 connection con1;
147 drop table t1, t2;
148 --echo # Sending:
149 --send drop table t3
150 --echo # Let DROP TABLE statement sync in.
151 connection con2;
152 --echo # Waiting for 'drop table t3' to get blocked...
153 let $wait_condition=select count(*)=1 from information_schema.processlist
154 where state='Waiting for table metadata lock' and
155 info='drop table t3';
156 --source include/wait_condition.inc
157 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
158 --echo # lock.
159 connection default;
160 handler t3 read a next;
161 --echo # Demonstrate that the drop will go through as soon as we close the
162 --echo # HANDLER
163 handler t3 close;
164 connection con1;
165 --echo # Reaping 'drop table t3'...
166 --reap
167 connection default;
168 commit;
169
170 --echo #
171 --echo # Cleanup for savepoint.inc
172 --echo #
173 connection con1;
174 disconnect con1;
175 --source include/wait_until_disconnected.inc
176 connection con2;
177 disconnect con2;
178 --source include/wait_until_disconnected.inc
179 connection con3;
180 disconnect con3;
181 --source include/wait_until_disconnected.inc
182 connection default;