Debian Codegraph @

mysql-test/suite/innodb/t/innodb-semi-consistent.test

raw
1
2 --disable_warnings
3 drop table if exists t1;
4 --enable_warnings
5
6 # basic tests of semi-consistent reads
7
8 connect (a,localhost,root,,);
9 connect (b,localhost,root,,);
10 connection a;
11 set binlog_format=mixed;
12 set session transaction isolation level repeatable read;
13 set autocommit=0;
14
15 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
16 insert into t1 values (1),(2),(3),(4),(5),(6),(7);
17 # this should lock the entire table
18 select * from t1 where a=3 lock in share mode;
19 connection b;
20 set binlog_format=mixed;
21 set session transaction isolation level repeatable read;
22 set autocommit=0;
23
24 # Test single-table UPDATE
25 -- error ER_LOCK_WAIT_TIMEOUT
26 update t1 set a=10 where a=5;
27 commit;
28 connection a;
29 commit;
30 connection b;
31 # perform a semi-consistent read (and unlock non-matching rows)
32 set session transaction isolation level read committed;
33 update t1 set a=10 where a=5;
34 connection a;
35 -- error ER_LOCK_WAIT_TIMEOUT
36 select * from t1 where a=2 for update;
37 # this should lock the records (1),(2)
38 select * from t1 where a=2 limit 1 for update;
39 connection b;
40 # semi-consistent read will skip non-matching locked rows a=1, a=2
41 update t1 set a=11 where a=6;
42 -- error ER_LOCK_WAIT_TIMEOUT
43 update t1 set a=12 where a=2;
44 -- error ER_LOCK_WAIT_TIMEOUT
45 update t1 set a=13 where a=1;
46 connection a;
47 commit;
48 connection b;
49 update t1 set a=14 where a=1;
50 commit;
51 connection a;
52 select * from t1;
53
54 # Restore initial content, and test multi-table UPDATE
55
56 delete from t1;
57 insert into t1 values (1),(2),(3),(4),(5),(6),(7);
58 create table t2 as select 1 as col;
59 commit;
60
61 # this should lock the entire table
62 select * from t1 where a=3 lock in share mode;
63
64 connection b;
65 -- error ER_LOCK_WAIT_TIMEOUT
66 # updated table must be first in plan for it to do semi-consistent read:
67 update t1 straight_join t2 set a=10 where a=5;
68 commit;
69 connection a;
70 commit;
71 connection b;
72 flush status;
73 # perform a semi-consistent read (and unlock non-matching rows)
74 set session transaction isolation level read committed;
75 update t1 straight_join t2 set a=10 where a=5;
76 connection a;
77 -- error ER_LOCK_WAIT_TIMEOUT
78 select * from t1 where a=2 for update;
79 # this should lock the records (1),(2)
80 select * from t1 where a=2 limit 1 for update;
81 connection b;
82 # semi-consistent read will skip non-matching locked rows a=1, a=2
83 update t1 straight_join t2 set a=11 where a=6;
84 -- error ER_LOCK_WAIT_TIMEOUT
85 update t1 straight_join t2 set a=12 where a=2;
86 -- error ER_LOCK_WAIT_TIMEOUT
87 update t1 straight_join t2 set a=13 where a=1;
88 connection a;
89 commit;
90 connection b;
91 update t1 straight_join t2 set a=14 where a=1;
92 commit;
93 # 0 tmp tables: multi-table
94 # UPDATE didn't use any temporary table, which proves that it
95 # updated t1 on the fly.
96 show status like "created_tmp_tables";
97 connection a;
98 select * from t1;
99
100 # Restore initial content, and test multi-table UPDATE again,
101 # this time with the internal use of a tmp table to
102 # delay row updates (this exercises different calls to
103 # try_semi_consistent_read()).
104
105 delete from t1;
106 insert into t1 values (1),(2),(3),(4),(5),(6),(7);
107 commit;
108
109 # this should lock the entire table
110 select * from t1 where a=3 lock in share mode;
111
112 connection b;
113 -- error ER_LOCK_WAIT_TIMEOUT
114 # updated table must be first in plan for it to do semi-consistent read.
115 # To force the use of a tmp table for storing rowids of t1,
116 # we make the updates to t2 dependent on the value of t1.a,
117 # which itself is updated. The dummy "+1" in "a+1=5+1" is to avoid constant
118 # propagation which would cancel this dependency.
119 update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a;
120 commit;
121 connection a;
122 commit;
123 connection b;
124 flush status;
125 # perform a semi-consistent read (and unlock non-matching rows)
126 set session transaction isolation level read committed;
127 update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a;
128 connection a;
129 -- error ER_LOCK_WAIT_TIMEOUT
130 select * from t1 where a=2 for update;
131 # this should lock the records (1),(2)
132 select * from t1 where a=2 limit 1 for update;
133 connection b;
134 # semi-consistent read will skip non-matching locked rows a=1, a=2
135 update t1 straight_join t2 set a=11,t2.col=t2.col+1 where a+1=6+1 and t2.col>0*a;
136 -- error ER_LOCK_WAIT_TIMEOUT
137 update t1 straight_join t2 set a=12,t2.col=t2.col+1 where a+1=2+1 and t2.col>0*a;
138 -- error ER_LOCK_WAIT_TIMEOUT
139 update t1 straight_join t2 set a=13,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a;
140 connection a;
141 commit;
142 connection b;
143 update t1 straight_join t2 set a=14,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a;
144 commit;
145 # 10 tmp tables (5 stmts, 2 tmp tables each): no update was done on the fly.
146 show status like "created_tmp_tables";
147 connection a;
148 select * from t1;
149
150
151 drop table t1,t2;
152
153 if (0)
154 {
155 # gbi todo delete this portion.
156 # Test different access methods to t1 in the multi-table UPDATE
157 # (table scan, ref, eq_ref, range), to see if they all
158 # do semi-consistent read.
159
160 create table t1(a int not null, b int not null, primary key(a,b)) engine=innodb;
161 insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1);
162 insert into t1 select a,2 from t1;
163 commit;
164
165 # this should lock two rows (3,1) and (3,2).
166 select * from t1 where a=3 and b=1 lock in share mode;
167
168 connection b;
169 # show the access method
170 explain update t1 straight_join t2 set a=10 where a=3 and b=1;
171 -- error ER_LOCK_WAIT_TIMEOUT
172 update t1 straight_join t2 set a=10 where a=3 and b=1;
173 update t1 straight_join t2 set a=10 where a=3 and b=3;
174 commit;
175 connection a;
176 commit;
177 connection b;
178 # perform a semi-consistent read (and unlock non-matching rows)
179 set session transaction isolation level read committed;
180 update t1 straight_join t2 set a=10 where a=5;
181 connection a;
182 -- error ER_LOCK_WAIT_TIMEOUT
183 select * from t1 where a=2 for update;
184 # this should lock the records (1),(2)
185 select * from t1 where a=2 limit 1 for update;
186 connection b;
187 # semi-consistent read will skip non-matching locked rows a=1, a=2
188 update t1 straight_join t2 set a=11 where a=6;
189 -- error ER_LOCK_WAIT_TIMEOUT
190 update t1 straight_join t2 set a=12 where a=2;
191 -- error ER_LOCK_WAIT_TIMEOUT
192 update t1 straight_join t2 set a=13 where a=1;
193 connection a;
194 commit;
195 connection b;
196 update t1 straight_join t2 set a=14 where a=1;
197 commit;
198 connection a;
199 select * from t1;
200
201
202 # clean up
203 drop table t1;
204
205 }
206
207 connection default;
208 disconnect a;
209 disconnect b;
210
211 # Bug 39320
212 create table t1 (a int, b int) engine=myisam;
213 create table t2 (c int, d int, key (c)) engine=innodb;
214 insert into t1 values (1,1);
215 insert into t2 values (1,2);
216 connect (a,localhost,root,,);
217 connection a;
218 set session transaction isolation level read committed;
219 delete from t1 using t1 join t2 on t1.a = t2.c where t2.d in (1);
220 connection default;
221 disconnect a;
222 drop table t1, t2;