1######################################################################## 2# Tests for START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION 3######################################################################## 4 5--source include/have_innodb.inc 6--source include/count_sessions.inc 7 8--connect (con1,localhost,root,,) 9--connect (con2,localhost,root,,) 10--connect (con3,localhost,root,,) 11 12--connection con1 13--echo # connection con1 14 15--let $con1_id=`SELECT CONNECTION_ID()` 16CREATE TABLE t1 (a INT) ENGINE=InnoDB; 17INSERT INTO t1 VALUES(1); 18START TRANSACTION WITH CONSISTENT SNAPSHOT; 19 20--connection con2 21--echo # connection con2 22 23INSERT INTO t1 VALUES(2); 24 25--connection con3 26--echo # connection con3 27 28SELECT * FROM t1; 29 30--echo # Basic syntax tests 31 32--error ER_PARSE_ERROR 33START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION; 34--replace_regex /id: [0-9]+/id: ID/ 35--error ER_NO_SUCH_THREAD 36START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION CONNECTION_ID(); 37--error ER_NOT_SUPPORTED_YET 38START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION 39 (SELECT MAX(a) FROM t1); 40--error ER_OPERAND_COLUMNS 41START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION (1,1); 42--error ER_NO_SUCH_THREAD 43START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION "foo"; 44--error ER_NO_SUCH_THREAD 45START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION NULL; 46 47--echo # Functional tests 48 49COMMIT; 50SET SESSION transaction_isolation='READ-COMMITTED'; 51--disable_query_log 52--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con1_id; 53--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con1_id 54--enable_query_log 55COMMIT; 56 57SET SESSION transaction_isolation='REPEATABLE-READ'; 58 59--disable_query_log 60--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con1_id; 61--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con1_id 62--enable_query_log 63SELECT * FROM t1; 64 65--connection con2 66--echo # connection con2 67 68INSERT INTO t1 VALUES(3); 69SELECT * FROM t1; 70 71--connection con3 72--echo # connection con3 73 74SELECT * FROM t1; 75INSERT INTO t1 VALUES(4); 76SELECT * FROM t1; 77 78--connection con1 79--echo # connection con1 80 81INSERT INTO t1 VALUES(5); 82SELECT * FROM t1; 83 84--connection con3 85--echo # connection con3 86 87--let $con3_id=`SELECT CONNECTION_ID()` 88 89SELECT * FROM t1; 90 91--connection con1 92--echo # connection con1 93 94COMMIT; 95 96--echo # Test cascading snapshot cloning 97--connection con2 98--echo # connection con2 99--disable_query_log 100--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 101--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 102--enable_query_log 103 104SELECT * FROM t1; 105 106COMMIT; 107 108SELECT * FROM t1; 109 110--connection con3 111--echo # connection con3 112 113SELECT * FROM t1; 114 115COMMIT; 116 117SELECT * FROM t1; 118 119--echo # The following should fail to clone a read view as con1 does not have an 120--echo # active transaction 121--disable_query_log 122--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con1_id; 123--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con1_id 124--enable_query_log 125 126--connection con1 127--echo # connection con1 128 129INSERT INTO t1 VALUES(6); 130 131--connection con3 132--echo # connection con3 133SELECT * FROM t1; 134 135--connection con3 136--echo # connection con3 137 138COMMIT; 139 140--echo # Now test cloning read view from a regular transaction 141 142--connection con1 143--echo # connection con1 144 145START TRANSACTION; 146INSERT INTO t1 VALUES(7); 147 148--connection con3 149--echo # connection con3 150--echo # The following should fail to clone a read view as con1 does not yet have an 151--echo # active transaction 152--disable_query_log 153--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con1_id; 154--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con1_id 155--enable_query_log 156SELECT * FROM t1; 157 158--connection con1 159--echo # connection con1 160SELECT * FROM t1; 161INSERT INTO t1 VALUES(8); 162 163--connection con3 164--echo # connection con3 165--disable_query_log 166--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con1_id; 167--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con1_id 168--enable_query_log 169 170SELECT * FROM t1; 171 172--echo # Test cloning the same transaction twice 173START TRANSACTION WITH CONSISTENT SNAPSHOT; 174 175--connection con2 176--echo # connection con2 177INSERT INTO t1 VALUES(9); 178--disable_query_log 179--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 180--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 181--enable_query_log 182 183SELECT * FROM t1; 184 185--connection con1 186--echo # connection con1 187INSERT INTO t1 VALUES(10); 188--disable_query_log 189--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 190--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 191--enable_query_log 192 193SELECT * FROM t1; 194 195COMMIT; 196 197--connection con2 198--echo # connection con2 199COMMIT; 200 201--let $con2_id=`SELECT CONNECTION_ID()` 202 203 204--echo # Test cascading cloning with RO/RW transactions 205 206# RO -> RO -> already tested above 207 208--echo # RO (con3) -> RW (con2) -> con1 209--disable_query_log 210--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 211--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 212--enable_query_log 213 214INSERT INTO t1 VALUES (11); 215 216SELECT * FROM t1; 217 218--connection con1 219--echo # connection con1 220 221--disable_query_log 222--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con2_id; 223--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con2_id 224--enable_query_log 225 226SELECT * FROM t1; 227 228COMMIT; 229 230--echo # RW (con3) -> RW (con2) -> con1 231 232--connection con3 233--echo # connection con3 234 235INSERT INTO t1 VALUES (12); 236 237SELECT * FROM t1; 238 239--connection con2 240--echo # connection con2 241COMMIT; 242 243--disable_query_log 244--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 245--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 246--enable_query_log 247 248INSERT INTO t1 VALUES (13); 249 250SELECT * FROM t1; 251 252--connection con1 253--echo # connection con1 254 255--disable_query_log 256--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con2_id; 257--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con2_id 258--enable_query_log 259 260SELECT * FROM t1; 261 262COMMIT; 263 264--echo # RW (con3) -> RO (con2) -> con1 265 266--connection con2 267--echo # connection con2 268COMMIT; 269 270--disable_query_log 271--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con3_id; 272--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con3_id 273--enable_query_log 274 275SELECT * FROM t1; 276 277--connection con1 278--echo # connection con1 279 280--disable_query_log 281--echo START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION \$con2_id; 282--eval START TRANSACTION WITH CONSISTENT SNAPSHOT FROM SESSION $con2_id 283--enable_query_log 284 285SELECT * FROM t1; 286 287COMMIT; 288 289--connection con3 290--echo # connection con3 291COMMIT; 292 293--connection con2 294--echo # connection con2 295COMMIT; 296 297--connection default 298--disconnect con1 299--disconnect con2 300--disconnect con3 301 302DROP TABLE t1; 303 304--source include/wait_until_count_sessions.inc 305