1# 2# Bug#20837 Apparent change of isolation level 3# during transaction 4# 5# Bug#53343 completion_type=1, COMMIT/ROLLBACK 6# AND CHAIN don't preserve the isolation 7# level 8connection default; 9SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 10CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; 11INSERT INTO t1 VALUES (1),(2); 12COMMIT; 13START TRANSACTION; 14SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 15ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress 16COMMIT; 17SET @@autocommit=0; 18COMMIT; 19SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 20START TRANSACTION; 21SELECT @@tx_isolation; 22@@tx_isolation 23REPEATABLE-READ 24Should be REPEATABLE READ 25SELECT * FROM t1; 26s1 271 282 29SELECT @@tx_isolation; 30@@tx_isolation 31REPEATABLE-READ 32Should be REPEATABLE READ 33INSERT INTO t1 VALUES (-1); 34SELECT @@tx_isolation; 35@@tx_isolation 36REPEATABLE-READ 37Should be REPEATABLE READ 38COMMIT; 39START TRANSACTION; 40SELECT * FROM t1; 41s1 421 432 44-1 45SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 46connection con1 47START TRANSACTION; 48INSERT INTO t1 VALUES (1000); 49COMMIT; 50connection default 51We should not be able to read the '1000' 52SELECT * FROM t1; 53s1 541 552 56-1 57COMMIT; 58Now, the '1000' should appear. 59START TRANSACTION; 60SELECT * FROM t1; 61s1 621 632 64-1 651000 66COMMIT; 67SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 68connection default 69SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 70START TRANSACTION; 71connection con1 72START TRANSACTION; 73INSERT INTO t1 VALUES (1001); 74COMMIT; 75connection default 76SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 77COUNT(*) 781 79Should be 1 80COMMIT AND CHAIN; 81connection con1 82INSERT INTO t1 VALUES (1002); 83COMMIT; 84connection default 85SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 86COUNT(*) 871 88Should be 1 89COMMIT; 90SELECT * FROM t1; 91s1 921 932 94-1 951000 961001 971002 98DELETE FROM t1 WHERE s1 >= 1000; 99COMMIT; 100connection default 101SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 102START TRANSACTION; 103connection con1 104START TRANSACTION; 105INSERT INTO t1 VALUES (1001); 106COMMIT; 107connection default 108SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 109COUNT(*) 1101 111Should be 1 112ROLLBACK AND CHAIN; 113connection con1 114INSERT INTO t1 VALUES (1002); 115COMMIT; 116connection default 117SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 118COUNT(*) 1191 120Should be 1 121COMMIT; 122SELECT * FROM t1; 123s1 1241 1252 126-1 1271001 1281002 129DELETE FROM t1 WHERE s1 >= 1000; 130COMMIT; 131SET @@completion_type=1; 132connection default 133SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 134START TRANSACTION; 135connection con1 136START TRANSACTION; 137INSERT INTO t1 VALUES (1001); 138COMMIT; 139connection default 140SELECT * FROM t1 WHERE s1 >= 1000; 141s1 1421001 143Should see 1001 144COMMIT AND NO CHAIN; 145default transaction is now in REPEATABLE READ 146connection con1 147INSERT INTO t1 VALUES (1002); 148COMMIT; 149connection default 150SELECT * FROM t1 WHERE s1 >= 1000; 151s1 1521001 1531002 154Should see 1001 and 1002 155connection con1 156INSERT INTO t1 VALUES (1003); 157COMMIT; 158connection default 159SELECT * FROM t1 WHERE s1 >= 1000; 160s1 1611001 1621002 163Should see 1001 and 1002, but NOT 1003 164COMMIT; 165SELECT * FROM t1; 166s1 1671 1682 169-1 1701001 1711002 1721003 173DELETE FROM t1 WHERE s1 >= 1000; 174COMMIT AND NO CHAIN; 175SET @@completion_type=0; 176COMMIT; 177connection default 178SET @@completion_type=1; 179COMMIT AND NO CHAIN; 180SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 181START TRANSACTION; 182connection con1 183START TRANSACTION; 184INSERT INTO t1 VALUES (1001); 185COMMIT; 186connection default 187SELECT * FROM t1 WHERE s1 >= 1000; 188s1 1891001 190Should see 1001 191ROLLBACK AND NO CHAIN; 192default transaction is now in REPEATABLE READ 193connection con1 194INSERT INTO t1 VALUES (1002); 195COMMIT; 196connection default 197SELECT * FROM t1 WHERE s1 >= 1000; 198s1 1991001 2001002 201Should see 1001 and 1002 202connection con1 203INSERT INTO t1 VALUES (1003); 204COMMIT; 205connection default 206SELECT * FROM t1 WHERE s1 >= 1000; 207s1 2081001 2091002 210Should see 1001 and 1002, but NOT 1003 211COMMIT; 212SELECT * FROM t1; 213s1 2141 2152 216-1 2171001 2181002 2191003 220DELETE FROM t1 WHERE s1 >= 1000; 221COMMIT AND NO CHAIN; 222SET @@completion_type=0; 223COMMIT; 224connection default 225SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 226SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 227START TRANSACTION; 228SELECT * FROM t1; 229s1 2301 2312 232-1 233connection con1 234INSERT INTO t1 VALUES (1000); 235COMMIT; 236connection default 237SELECT * FROM t1; 238s1 2391 2402 241-1 242Should get same result as above (i.e should not read '1000') 243COMMIT; 244DELETE FROM t1 WHERE s1 >= 1000; 245COMMIT; 246SET @@completion_type=1; 247COMMIT AND NO CHAIN; 248SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 249START TRANSACTION; 250TRUNCATE TABLE t1; 251INSERT INTO t1 VALUES (1000); 252SELECT * FROM t1; 253s1 2541000 255Should read '1000' 256connection con1 257INSERT INTO t1 VALUES (1001); 258COMMIT; 259connection default 260SELECT * FROM t1; 261s1 2621000 263Should only read the '1000' as this transaction is now in REP READ 264COMMIT AND NO CHAIN; 265SET @@completion_type=0; 266COMMIT AND NO CHAIN; 267SET @@autocommit=1; 268COMMIT; 269DROP TABLE t1; 270# 271# End of test cases for Bug#20837 272# 273# 274# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); 275# 276# 277# Test 1: Check supported syntax 278START TRANSACTION; 279COMMIT; 280START TRANSACTION READ ONLY; 281COMMIT; 282START TRANSACTION READ WRITE; 283COMMIT; 284START TRANSACTION READ ONLY, READ WRITE; 285ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 286START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT; 287COMMIT; 288START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT; 289COMMIT; 290START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY; 291COMMIT; 292START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE; 293COMMIT; 294START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE; 295ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 296SET TRANSACTION READ ONLY; 297SET TRANSACTION READ WRITE; 298SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 299SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE; 300SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED; 301SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED; 302SET TRANSACTION READ ONLY, READ WRITE; 303ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'READ WRITE' at line 1 304COMMIT; 305# 306# Test 2: Check setting of variable. 307SET SESSION TRANSACTION READ WRITE; 308SELECT @@tx_read_only; 309@@tx_read_only 3100 311SET SESSION TRANSACTION READ ONLY; 312SELECT @@tx_read_only; 313@@tx_read_only 3141 315SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 316SELECT @@tx_read_only; 317@@tx_read_only 3180 319SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; 320SELECT @@tx_read_only; 321@@tx_read_only 3221 323START TRANSACTION; 324# Not allowed inside a transaction 325SET TRANSACTION READ ONLY; 326ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress 327# But these are allowed. 328SET SESSION TRANSACTION READ ONLY; 329SET GLOBAL TRANSACTION READ ONLY; 330COMMIT; 331SET SESSION TRANSACTION READ WRITE; 332SET GLOBAL TRANSACTION READ WRITE; 333# 334# Test 3: Test that write operations are properly blocked. 335CREATE TABLE t1(a INT); 336CREATE TEMPORARY TABLE temp_t2(a INT); 337SET SESSION TRANSACTION READ ONLY; 338# 1: DDL should be blocked, also on temporary tables. 339CREATE TABLE t3(a INT); 340ERROR 25006: Cannot execute statement in a READ ONLY transaction. 341ALTER TABLE t1 COMMENT "Test"; 342ERROR 25006: Cannot execute statement in a READ ONLY transaction. 343DROP TABLE t1; 344ERROR 25006: Cannot execute statement in a READ ONLY transaction. 345CREATE TEMPORARY TABLE temp_t3(a INT); 346ERROR 25006: Cannot execute statement in a READ ONLY transaction. 347ALTER TABLE temp_t2 COMMENT "Test"; 348ERROR 25006: Cannot execute statement in a READ ONLY transaction. 349DROP TEMPORARY TABLE temp_t2; 350ERROR 25006: Cannot execute statement in a READ ONLY transaction. 351CREATE FUNCTION f1() RETURNS INT RETURN 1; 352ERROR 25006: Cannot execute statement in a READ ONLY transaction. 353DROP FUNCTION f1; 354ERROR 25006: Cannot execute statement in a READ ONLY transaction. 355CREATE PROCEDURE p1() BEGIN END; 356ERROR 25006: Cannot execute statement in a READ ONLY transaction. 357DROP PROCEDURE p1; 358ERROR 25006: Cannot execute statement in a READ ONLY transaction. 359CREATE VIEW v1 AS SELECT 1; 360ERROR 25006: Cannot execute statement in a READ ONLY transaction. 361SET SESSION TRANSACTION READ WRITE; 362CREATE VIEW v1 AS SELECT 1; 363SET SESSION TRANSACTION READ ONLY; 364DROP VIEW v1; 365ERROR 25006: Cannot execute statement in a READ ONLY transaction. 366SET SESSION TRANSACTION READ WRITE; 367DROP VIEW v1; 368SET SESSION TRANSACTION READ ONLY; 369RENAME TABLE t1 TO t2; 370ERROR 25006: Cannot execute statement in a READ ONLY transaction. 371RENAME TABLE temp_t2 TO temp_t3; 372ERROR 25006: Cannot execute statement in a READ ONLY transaction. 373TRUNCATE TABLE t1; 374ERROR 25006: Cannot execute statement in a READ ONLY transaction. 375CREATE DATABASE db1; 376ERROR 25006: Cannot execute statement in a READ ONLY transaction. 377DROP DATABASE db1; 378ERROR 25006: Cannot execute statement in a READ ONLY transaction. 379SET SESSION TRANSACTION READ WRITE; 380# 2: DML should be blocked on non-temporary tables. 381START TRANSACTION READ ONLY; 382INSERT INTO t1 VALUES (1), (2); 383ERROR 25006: Cannot execute statement in a READ ONLY transaction. 384UPDATE t1 SET a= 3; 385ERROR 25006: Cannot execute statement in a READ ONLY transaction. 386DELETE FROM t1; 387ERROR 25006: Cannot execute statement in a READ ONLY transaction. 388# 3: DML should be allowed on temporary tables. 389INSERT INTO temp_t2 VALUES (1), (2); 390UPDATE temp_t2 SET a= 3; 391DELETE FROM temp_t2; 392# 4: Queries should not be blocked. 393SELECT * FROM t1; 394a 395SELECT * FROM temp_t2; 396a 397HANDLER t1 OPEN; 398HANDLER t1 READ FIRST; 399a 400HANDLER t1 CLOSE; 401HANDLER temp_t2 OPEN; 402HANDLER temp_t2 READ FIRST; 403a 404HANDLER temp_t2 CLOSE; 405# 5: Prepared statements 406PREPARE stmt FROM "DELETE FROM t1"; 407ERROR 25006: Cannot execute statement in a READ ONLY transaction. 408PREPARE stmt FROM "DELETE FROM temp_t2"; 409EXECUTE stmt; 410DEALLOCATE PREPARE stmt; 411COMMIT; 412# 6: Stored routines 413CREATE FUNCTION f1() RETURNS INT 414BEGIN 415DELETE FROM t1; 416RETURN 1; 417END| 418CREATE FUNCTION f2() RETURNS INT 419BEGIN 420DELETE FROM temp_t2; 421RETURN 1; 422END| 423CREATE PROCEDURE p1() DELETE FROM t1; 424CREATE PROCEDURE p2() DELETE FROM temp_t2; 425START TRANSACTION READ ONLY; 426SELECT f1(); 427ERROR 25006: Cannot execute statement in a READ ONLY transaction. 428SELECT f2(); 429f2() 4301 431CALL p1(); 432ERROR 25006: Cannot execute statement in a READ ONLY transaction. 433CALL p2(); 434COMMIT; 435DROP FUNCTION f1; 436DROP FUNCTION f2; 437DROP PROCEDURE p1; 438DROP PROCEDURE p2; 439# 7: Views 440CREATE VIEW v1 AS SELECT a FROM t1; 441START TRANSACTION READ ONLY; 442INSERT INTO v1 VALUES (1), (2); 443ERROR 25006: Cannot execute statement in a READ ONLY transaction. 444SELECT * FROM v1; 445a 446COMMIT; 447DROP VIEW v1; 448# 8: LOCK TABLE 449SET SESSION TRANSACTION READ ONLY; 450LOCK TABLE t1 WRITE; 451ERROR 25006: Cannot execute statement in a READ ONLY transaction. 452LOCK TABLE t1 READ; 453UNLOCK TABLES; 454SET SESSION TRANSACTION READ WRITE; 455DROP TABLE temp_t2, t1; 456# 457# Test 4: SET TRANSACTION, CHAINing transactions 458CREATE TABLE t1(a INT); 459SET SESSION TRANSACTION READ ONLY; 460START TRANSACTION; 461DELETE FROM t1; 462ERROR 25006: Cannot execute statement in a READ ONLY transaction. 463COMMIT; 464START TRANSACTION READ WRITE; 465DELETE FROM t1; 466COMMIT; 467SET SESSION TRANSACTION READ WRITE; 468SET TRANSACTION READ ONLY; 469START TRANSACTION; 470DELETE FROM t1; 471ERROR 25006: Cannot execute statement in a READ ONLY transaction. 472COMMIT; 473START TRANSACTION READ WRITE; 474DELETE FROM t1; 475COMMIT; 476START TRANSACTION READ ONLY; 477SELECT * FROM t1; 478a 479COMMIT AND CHAIN; 480DELETE FROM t1; 481ERROR 25006: Cannot execute statement in a READ ONLY transaction. 482COMMIT; 483START TRANSACTION READ ONLY; 484SELECT * FROM t1; 485a 486ROLLBACK AND CHAIN; 487DELETE FROM t1; 488ERROR 25006: Cannot execute statement in a READ ONLY transaction. 489COMMIT; 490DROP TABLE t1; 491# 492# Test 5: Test that reserved keyword ONLY is still allowed as 493# identifier - both directly and in SPs. 494SET @only= 1; 495CREATE TABLE t1 (only INT); 496INSERT INTO t1 (only) values (1); 497SELECT only FROM t1 WHERE only = 1; 498only 4991 500DROP TABLE t1; 501CREATE PROCEDURE p1() 502BEGIN 503DECLARE only INT DEFAULT 1; 504END| 505CALL p1(); 506DROP PROCEDURE p1; 507# 508# Test 6: Check that XA transactions obey default access mode. 509CREATE TABLE t1(a INT); 510SET TRANSACTION READ ONLY; 511XA START 'test1'; 512INSERT INTO t1 VALUES (1); 513ERROR 25006: Cannot execute statement in a READ ONLY transaction. 514UPDATE t1 SET a=2; 515ERROR 25006: Cannot execute statement in a READ ONLY transaction. 516DELETE FROM t1; 517ERROR 25006: Cannot execute statement in a READ ONLY transaction. 518XA END 'test1'; 519XA PREPARE 'test1'; 520XA COMMIT 'test1'; 521DROP TABLE t1; 522# 523# Test 7: SET TRANSACTION inside stored routines 524CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; 525CALL p1(); 526SELECT @@tx_read_only; 527@@tx_read_only 5281 529SET SESSION TRANSACTION READ WRITE; 530DROP PROCEDURE p1; 531CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, 532ISOLATION LEVEL SERIALIZABLE; 533CALL p1(); 534SELECT @@tx_read_only; 535@@tx_read_only 5361 537SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; 538DROP PROCEDURE p1; 539CREATE FUNCTION f1() RETURNS INT 540BEGIN 541SET SESSION TRANSACTION READ ONLY; 542RETURN 1; 543END| 544SELECT f1(); 545f1() 5461 547SELECT @@tx_read_only; 548@@tx_read_only 5491 550SET SESSION TRANSACTION READ WRITE; 551DROP FUNCTION f1; 552CREATE FUNCTION f1() RETURNS INT 553BEGIN 554SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 555RETURN 1; 556END| 557SELECT f1(); 558f1() 5591 560SELECT @@tx_read_only; 561@@tx_read_only 5621 563SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 564DROP FUNCTION f1; 565# 566# Test 8: SET TRANSACTION and auto-commit 567SELECT @@autocommit; 568@@autocommit 5691 570CREATE TABLE t1(a INT) engine=InnoDB; 571SET TRANSACTION READ ONLY; 572SELECT * FROM t1; 573a 574# This statement should work, since last statement committed. 575INSERT INTO t1 VALUES (1); 576DROP TABLE t1; 577