Debian Codegraph @

mysql-test/suite/binlog/t/binlog_commit_wait.test

raw
1 --source include/have_innodb.inc
2 --source include/have_log_bin.inc
3
4 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
5 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
6
7 SET @old_count= @@GLOBAL.binlog_commit_wait_count;
8 SET GLOBAL binlog_commit_wait_count= 3;
9 SET @old_usec= @@GLOBAL.binlog_commit_wait_usec;
10 SET GLOBAL binlog_commit_wait_usec= 20000000;
11
12 connect(con1,localhost,root,,test);
13 connect(con2,localhost,root,,test);
14 connect(con3,localhost,root,,test);
15
16 # Get Initial status measurements
17 --connection default
18 --disable_cursor_protocol
19 SELECT variable_value INTO @group_commits FROM information_schema.global_status
20 WHERE variable_name = 'binlog_group_commits';
21 SELECT variable_value INTO @group_commit_trigger_count FROM information_schema.global_status
22 WHERE variable_name = 'binlog_group_commit_trigger_count';
23 SELECT variable_value INTO @group_commit_trigger_timeout FROM information_schema.global_status
24 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
25 SELECT variable_value INTO @group_commit_trigger_lock_wait FROM information_schema.global_status
26 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
27 --enable_cursor_protocol
28
29 # Note: binlog_group_commits is counted at the start of the group and group_commit_trigger_* is
30 # counted near when the groups its finalised.
31
32 # Check that if T2 goes to wait for a row lock of T1 while T1 is waiting for
33 # more transactions to arrive for group commit, the commit of T1 will complete
34 # immediately.
35 # We test this by setting a very high timeout (20 seconds), and testing that
36 # that much time does not elapse.
37
38 --connection default
39 SET @a= current_timestamp();
40
41 --connection con1
42 BEGIN;
43 INSERT INTO t1 VALUES (1,0);
44 send COMMIT;
45
46 --connection con2
47 send INSERT INTO t1 VALUES (1,1);
48
49 --connection con1
50 reap;
51
52 --connection default
53 SET @b= unix_timestamp(current_timestamp()) - unix_timestamp(@a);
54 SELECT IF(@b < 20, "Ok", CONCAT("Error: too much time elapsed: ", @b, " seconds >= 20"));
55
56 # All connections are to the same server. One transaction occurs on con1. It is
57 # commited before con2 is started. con2 transaction violates the unique key contraint. This
58 # type of group commit is binlog_group_commit_trigger_lock_wait so that further con2
59 # transactions will occur afterwards as they may be as result of the ER_DUP_ENTRY on the
60 # application side.
61 # before: binlog_group_commit=0, binlog_group_commit_trigger_count=0
62 # before: binlog_group_commit_trigger_timeout=0, binlog_group_commit_trigger_lock_wait=0
63 # after: binlog_group_commit+1 by reason of binlog_group_commit_trigger_lock_wait+1
64 SELECT variable_value - @group_commits FROM information_schema.global_status
65 WHERE variable_name = 'binlog_group_commits';
66 SELECT variable_value - @group_commit_trigger_count FROM information_schema.global_status
67 WHERE variable_name = 'binlog_group_commit_trigger_count';
68 SELECT variable_value - @group_commit_trigger_timeout FROM information_schema.global_status
69 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
70 SELECT variable_value - @group_commit_trigger_lock_wait FROM information_schema.global_status
71 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
72
73 --connection con2
74 --error ER_DUP_ENTRY
75 reap;
76
77
78 # Test that the commit triggers when sufficient commits have queued up.
79 --connection default
80 SET @a= current_timestamp();
81
82 --connection con1
83 send INSERT INTO t1 VALUES (2,0);
84
85 --connection con2
86 send INSERT INTO t1 VALUES (3,0);
87
88 --connection con3
89 INSERT INTO t1 VALUES (4,0);
90
91 --connection con1
92 reap;
93 --connection con2
94 reap;
95
96 --connection default
97 SET @b= unix_timestamp(current_timestamp()) - unix_timestamp(@a);
98 SELECT IF(@b < 20, "Ok", CONCAT("Error: too much time elapsed: ", @b, " seconds >= 20"));
99
100 # All connections are to the same server. 3 non-conflicting transaction occur
101 # on each connection. The binlog_commit_wait_count=3 at the start therefore 1
102 # group is committed by virtue of reaching 3 transactions. Hence
103 # binlog_group_commit_trigger_count is incremented.
104 # before: binlog_group_commit=1, binlog_group_commit_trigger_count=0
105 # before: binlog_group_commit_trigger_timeout=0, binlog_group_commit_trigger_lock_wait=1
106 # after: binlog_group_commit+1 by reason of binlog_group_commit_trigger_count+1
107 SELECT variable_value - @group_commits FROM information_schema.global_status
108 WHERE variable_name = 'binlog_group_commits';
109 SELECT variable_value - @group_commit_trigger_count FROM information_schema.global_status
110 WHERE variable_name = 'binlog_group_commit_trigger_count';
111 SELECT variable_value - @group_commit_trigger_timeout FROM information_schema.global_status
112 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
113 SELECT variable_value - @group_commit_trigger_lock_wait FROM information_schema.global_status
114 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
115
116 # Test that commit triggers immediately if there is already a transaction
117 # waiting on another transaction that reaches its commit.
118
119 --connection default
120 SET @a= current_timestamp();
121
122 --connection con1
123 send INSERT INTO t1 VALUES (6,0);
124
125 --connection con2
126 BEGIN;
127 UPDATE t1 SET b=b+1 WHERE a=1;
128
129 --connection con3
130 send UPDATE t1 SET b=b+10 WHERE a=1;
131
132 --connection con2
133 # A small sleep to let con3 have time to wait on con2.
134 # The sleep might be too small on loaded host, but that is not a big problem;
135 # it only means we will trigger a different code path (con3 waits after con2
136 # is ready to commit rather than before); and either path should work the same.
137 # So we will not get false positive in case of different timing; at worst false
138 # negative.
139 SELECT SLEEP(0.25);
140 UPDATE t1 SET b=b+1 WHERE a=3;
141 COMMIT;
142
143 --connection con1
144 reap;
145
146 --connection default
147 SET @b= unix_timestamp(current_timestamp()) - unix_timestamp(@a);
148 SELECT IF(@b < 20, "Ok", CONCAT("Error: too much time elapsed: ", @b, " seconds >= 20"));
149
150 # All connections are to the same server. con2 and con3 updates are aquiring
151 # the same row lock for a=1. Either con2 or con3 will be in a lock wait
152 # thefore the binlog_group_commit_trigger_lock_wait is incremented.
153 # before: binlog_group_commit=2, binlog_group_commit_trigger_count=1
154 # before: binlog_group_commit_trigger_timeout=0, binlog_group_commit_trigger_lock_wait=1
155 # after: binlog_group_commit+1 by reason of binlog_group_commit_trigger_lock_wait+1
156 SELECT variable_value - @group_commits FROM information_schema.global_status
157 WHERE variable_name = 'binlog_group_commits';
158 SELECT variable_value - @group_commit_trigger_count FROM information_schema.global_status
159 WHERE variable_name = 'binlog_group_commit_trigger_count';
160 SELECT variable_value - @group_commit_trigger_timeout FROM information_schema.global_status
161 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
162 SELECT variable_value - @group_commit_trigger_lock_wait FROM information_schema.global_status
163 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
164
165 --connection default
166 SET @a= current_timestamp();
167
168 # Now con3 will be waiting for a following group commit to trigger.
169 --connection con1
170 send INSERT INTO t1 VALUES (7,0);
171 --connection con2
172 INSERT INTO t1 VALUES (8,0);
173 --connection con3
174 reap;
175
176 --connection default
177 SET @b= unix_timestamp(current_timestamp()) - unix_timestamp(@a);
178 SELECT IF(@b < 20, "Ok", CONCAT("Error: too much time elapsed: ", @b, " seconds >= 20"));
179
180 # The con1 and con2 transactions above are combined with the 'send UPDATE t1 SET b=b+10 WHERE a=1;'
181 # on con3 from the previous block. So we have 3 so this is a count based group.
182 # before: binlog_group_commit=3, binlog_group_commit_trigger_count=1
183 # before: binlog_group_commit_trigger_timeout=0, binlog_group_commit_trigger_lock_wait=2
184 # after: binlog_group_commit+1 by reason of binlog_group_commit_trigger_count+1
185 SELECT variable_value - @group_commits FROM information_schema.global_status
186 WHERE variable_name = 'binlog_group_commits';
187 SELECT variable_value - @group_commit_trigger_count FROM information_schema.global_status
188 WHERE variable_name = 'binlog_group_commit_trigger_count';
189 SELECT variable_value - @group_commit_trigger_timeout FROM information_schema.global_status
190 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
191 SELECT variable_value - @group_commit_trigger_lock_wait FROM information_schema.global_status
192 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
193
194 # Test that when the binlog_commit_wait_usec is reached the tranction gets a group commit
195
196 --connection default
197 SET @a= current_timestamp();
198 SET GLOBAL binlog_commit_wait_usec= 5*1000*1000;
199
200 --connection con1
201 reap;
202 INSERT INTO t1 VALUES (9,0);
203
204 --connection default
205 SET @b= unix_timestamp(current_timestamp()) - unix_timestamp(@a);
206 SELECT IF(@b < 4, CONCAT("Error: too little time elapsed: ", @b, " seconds < 4"),
207 IF(@b < 20, "Ok", CONCAT("Error: too much time elapsed: ", @b, " seconds >= 20")));
208
209 # con1 pushes 1 transaction. The count was for 3 to occur before a group commit.
210 # The timeout is 5 seconds but we allow between 4 and 20 because of the fragile nature
211 # of time in test. This is a timeout causing the commit so binlog_group_commit_trigger_timeout
212 # is incremented.
213 # before: binlog_group_commit=4, binlog_group_commit_trigger_count=2
214 # before: binlog_group_commit_trigger_timeout=0, binlog_group_commit_trigger_lock_wait=2
215 # after: binlog_group_commit+1 by reason of binlog_group_commit_trigger_timeout+1
216 SELECT variable_value - @group_commits FROM information_schema.global_status
217 WHERE variable_name = 'binlog_group_commits';
218 SELECT variable_value - @group_commit_trigger_count FROM information_schema.global_status
219 WHERE variable_name = 'binlog_group_commit_trigger_count';
220 SELECT variable_value - @group_commit_trigger_timeout FROM information_schema.global_status
221 WHERE variable_name = 'binlog_group_commit_trigger_timeout';
222 SELECT variable_value - @group_commit_trigger_lock_wait FROM information_schema.global_status
223 WHERE variable_name = 'binlog_group_commit_trigger_lock_wait';
224
225 --connection default
226 SELECT * FROM t1 ORDER BY a;
227
228 --connection default
229 DROP TABLE t1;
230 SET GLOBAL binlog_commit_wait_count= @old_count;
231 SET GLOBAL binlog_commit_wait_usec= @old_usec;