Debian Codegraph @

mysql-test/suite/innodb/t/innodb-read-view.test

raw
1 # DEBUG_SYNC must be compiled in.
2 --source include/have_debug_sync.inc
3 --source include/have_debug.inc
4 # We need to test the use case:
5 # a. Create a transaction T1 that will be promoted to RW.
6 # b. Create a transaction T2 that will be promoted to RW.
7 # a. Create a RO transaction T3
8 # d. T3 does a select - creates a read view that doesn't include T1 and T2
9 # e. T1 & T2 do some updates - this promotes T1 & T2 to RW transactions
10 # f. T1 & T2 Commit
11 # g. T3 Does a select - it should not see the changes of T1 & T2
12
13 --source include/have_innodb.inc
14 --source include/count_sessions.inc
15
16 CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
17 INSERT INTO t1 VALUES(0, "0");
18 INSERT INTO t1 VALUES(1, "1");
19 INSERT INTO t1 VALUES(2, "2");
20 INSERT INTO t1 VALUES(3, "3");
21
22 CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
23 INSERT INTO t2 VALUES(0, "a");
24 INSERT INTO t2 VALUES(1, "b");
25 INSERT INTO t2 VALUES(2, "c");
26 INSERT INTO t2 VALUES(3, "d");
27
28 --connect (con1,localhost,root,,)
29 SET AUTOCOMMIT=0;
30 BEGIN;
31 SELECT * FROM t2;
32
33 connection default;
34 SET AUTOCOMMIT=0;
35 BEGIN;
36 SELECT * FROM t1;
37
38 --connect (con2,localhost,root,,)
39 SET AUTOCOMMIT=0;
40 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
41 BEGIN;
42 SELECT * FROM t1;
43 SELECT * FROM t2;
44
45 connection con1;
46 UPDATE t2 SET c1 = c1 + 100;
47 SELECT * FROM t2;
48 COMMIT;
49
50 connection default;
51 UPDATE t1 SET c1 = c1 + 100;
52 SELECT * FROM t1;
53 COMMIT;
54
55 connection con2;
56 SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
57 --send SELECT * FROM t1;
58
59 connection default;
60 SET DEBUG_SYNC='now SIGNAL waiting1';
61
62 connection con2;
63 reap;
64
65 connection con2;
66 SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
67 --send SELECT * FROM t2;
68
69 connection default;
70 SET DEBUG_SYNC='now SIGNAL waiting1';
71
72 connection con2;
73 reap;
74
75 connection default;
76
77 # We need to test the use case:
78 # a. Create a transaction T1 that will be promoted to RW.
79 # b. Create a transaction T2 that will be promoted to RW.
80 # c. T2 does some updates - this promotes T2 to RW transactions
81 # d. T2 Commits
82 # e. Create a RO transaction T3
83 # f. T3 does a select - creates a read view that doesn't include T1
84 # g. T1 does some updates - this promotes T1 to RW transactions
85 # h. T1 Commits
86 # i. T3 Does a select - it should not see the changes made by T1 but should
87 # see the changes by T2
88
89 connection con1;
90 SET AUTOCOMMIT=0;
91 BEGIN;
92 SELECT * FROM t1;
93
94 connection default;
95 SET AUTOCOMMIT=0;
96 BEGIN;
97 SELECT * FROM t2;
98 UPDATE t2 SET c1 = c1 + 100;
99 SELECT * FROM t2;
100 COMMIT;
101
102 connection con2;
103 SET AUTOCOMMIT=0;
104 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
105 BEGIN;
106 SELECT * FROM t1;
107 SELECT * FROM t2;
108
109 connection con1;
110 UPDATE t1 SET c1 = c1 + 100;
111 SELECT * FROM t1;
112 COMMIT;
113
114 connection con2;
115 SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
116 --send SELECT * FROM t1;
117
118 connection con1;
119 SET DEBUG_SYNC='now SIGNAL waiting1';
120
121 connection con2;
122 reap;
123 SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
124 --send SELECT * FROM t2;
125
126 connection default;
127 SET DEBUG_SYNC='now SIGNAL waiting1';
128
129 connection con2;
130 reap;
131 disconnect con2;
132
133 connection default;
134
135 DROP TABLE t1;
136 DROP TABLE t2;
137
138 --echo #
139 --echo # Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION
140 --echo #
141
142 connection con1;
143 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB;
144 INSERT INTO t1 values (1, 0), (2, 0);
145 SELECT * FROM t1 ORDER BY col1;
146
147 START TRANSACTION;
148 UPDATE t1 SET col2 = 100;
149 SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2';
150 --send COMMIT;
151
152 connection default;
153 SET DEBUG_SYNC = 'now WAIT_FOR s1';
154 UPDATE t1 SET col2 = col2 + 10 where col1 = 1;
155 COMMIT;
156
157 SELECT * FROM t1 ORDER BY col1;
158 SET DEBUG_SYNC = 'now SIGNAL s2';
159
160 connection con1;
161 reap;
162 disconnect con1;
163
164 connection default;
165 SET DEBUG_SYNC= 'RESET';
166 DROP TABLE t1;
167
168 --source include/wait_until_count_sessions.inc