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
|
-- source include/have_partition.inc
-- source include/not_embedded.inc
-- source include/have_innodb.inc
--disable_warnings
drop table if exists t1;
--enable_warnings
# basic tests of semi-consistent reads
# for verifying Bug#40595: Non-matching rows not released with READ-COMMITTED
# on tables with partitions
connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
set binlog_format=mixed;
set session transaction isolation level repeatable read;
create table t1(a int not null)
engine=innodb
DEFAULT CHARSET=latin1
PARTITION BY RANGE(a)
(PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN MAXVALUE);
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
set autocommit=0;
# 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;
-- error ER_LOCK_WAIT_TIMEOUT
update t1 set a=10 where a=5;
commit;
connection a;
#DELETE FROM t1 WHERE a=5;
commit;
connection b;
# perform a semi-consisent 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;
--sorted_result
select * from t1;
drop table t1;
connection default;
disconnect a;
disconnect b;
#
# Bug #31310: Locked rows silently skipped in read-committed isolation level.
# (This also tests the '*_semi_consistent*' functions in partitioning)
# Copied from include/mix1.inc
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
SET SESSION AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set binlog_format=mixed;
connection con1;
eval
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
ENGINE = InnoDB
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (300),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1,2);
--echo # 1. test for locking:
BEGIN;
--enable_info
UPDATE t1 SET b = 12 WHERE a = 1;
--disable_info
SELECT * FROM t1;
connection con2;
--enable_info
--disable_abort_on_error
--error ER_LOCK_WAIT_TIMEOUT
UPDATE t1 SET b = 21 WHERE a = 1;
--disable_info
ROLLBACK;
connection con1;
SELECT * FROM t1;
ROLLBACK;
--echo # 2. test for serialized update:
CREATE TABLE t2 (a INT);
TRUNCATE t1;
INSERT INTO t1 VALUES (1,'init');
DELIMITER |;
CREATE PROCEDURE p1()
BEGIN
# retry the UPDATE in case it times out the lock before con1 has time
# to COMMIT.
DECLARE do_retry INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
retry_loop:LOOP
UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
IF do_retry = 0 THEN
LEAVE retry_loop;
END IF;
SET do_retry = 0;
END LOOP;
INSERT INTO t2 VALUES ();
END|
DELIMITER ;|
BEGIN;
--enable_info
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
--disable_info
SELECT * FROM t1;
connection con2;
--send CALL p1;
connection con1;
SELECT * FROM t1;
COMMIT;
let $bug31310 = 1;
while ($bug31310)
{
let $bug31310= `SELECT 1 - COUNT(*) FROM t2`;
}
SELECT * FROM t1;
connection con2;
--reap
SELECT * FROM t1;
COMMIT;
connection con1;
--echo # 3. test for updated key column:
TRUNCATE t1;
DELETE FROM t2;
INSERT INTO t1 VALUES (1,'init');
BEGIN;
--enable_info
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
--disable_info
SELECT * FROM t1;
connection con2;
--send CALL p1;
connection con1;
SELECT * FROM t1;
COMMIT;
let $bug31310 = 1;
while ($bug31310)
{
let $bug31310= `SELECT 1 - COUNT(*) FROM t2`;
}
SELECT * FROM t1;
connection con2;
reap;
SELECT * FROM t1;
connection default;
disconnect con1;
disconnect con2;
DROP PROCEDURE p1;
DROP TABLE t1, t2;
|
