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