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 24Warnings: 25Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 26Should be REPEATABLE READ 27SELECT * FROM t1; 28s1 291 302 31SELECT @@tx_isolation; 32@@tx_isolation 33REPEATABLE-READ 34Warnings: 35Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 36Should be REPEATABLE READ 37INSERT INTO t1 VALUES (-1); 38SELECT @@tx_isolation; 39@@tx_isolation 40REPEATABLE-READ 41Warnings: 42Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 43Should be REPEATABLE READ 44COMMIT; 45START TRANSACTION; 46SELECT * FROM t1; 47s1 481 492 50-1 51SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 52connection con1 53START TRANSACTION; 54INSERT INTO t1 VALUES (1000); 55COMMIT; 56connection default 57We should not be able to read the '1000' 58SELECT * FROM t1; 59s1 601 612 62-1 63COMMIT; 64Now, the '1000' should appear. 65START TRANSACTION; 66SELECT * FROM t1; 67s1 681 692 70-1 711000 72COMMIT; 73SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 74connection default 75SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 76START TRANSACTION; 77connection con1 78START TRANSACTION; 79INSERT INTO t1 VALUES (1001); 80COMMIT; 81connection default 82SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 83COUNT(*) 841 85Should be 1 86COMMIT AND CHAIN; 87connection con1 88INSERT INTO t1 VALUES (1002); 89COMMIT; 90connection default 91SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 92COUNT(*) 931 94Should be 1 95COMMIT; 96SELECT * FROM t1; 97s1 981 992 100-1 1011000 1021001 1031002 104DELETE FROM t1 WHERE s1 >= 1000; 105COMMIT; 106connection default 107SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 108START TRANSACTION; 109connection con1 110START TRANSACTION; 111INSERT INTO t1 VALUES (1001); 112COMMIT; 113connection default 114SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 115COUNT(*) 1161 117Should be 1 118ROLLBACK AND CHAIN; 119connection con1 120INSERT INTO t1 VALUES (1002); 121COMMIT; 122connection default 123SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 124COUNT(*) 1251 126Should be 1 127COMMIT; 128SELECT * FROM t1; 129s1 1301 1312 132-1 1331001 1341002 135DELETE FROM t1 WHERE s1 >= 1000; 136COMMIT; 137SET @@completion_type=1; 138connection default 139SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 140START TRANSACTION; 141connection con1 142START TRANSACTION; 143INSERT INTO t1 VALUES (1001); 144COMMIT; 145connection default 146SELECT * FROM t1 WHERE s1 >= 1000; 147s1 1481001 149Should see 1001 150COMMIT AND NO CHAIN; 151default transaction is now in REPEATABLE READ 152connection con1 153INSERT INTO t1 VALUES (1002); 154COMMIT; 155connection default 156SELECT * FROM t1 WHERE s1 >= 1000; 157s1 1581001 1591002 160Should see 1001 and 1002 161connection con1 162INSERT INTO t1 VALUES (1003); 163COMMIT; 164connection default 165SELECT * FROM t1 WHERE s1 >= 1000; 166s1 1671001 1681002 169Should see 1001 and 1002, but NOT 1003 170COMMIT; 171SELECT * FROM t1; 172s1 1731 1742 175-1 1761001 1771002 1781003 179DELETE FROM t1 WHERE s1 >= 1000; 180COMMIT AND NO CHAIN; 181SET @@completion_type=0; 182COMMIT; 183connection default 184SET @@completion_type=1; 185COMMIT AND NO CHAIN; 186SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 187START TRANSACTION; 188connection con1 189START TRANSACTION; 190INSERT INTO t1 VALUES (1001); 191COMMIT; 192connection default 193SELECT * FROM t1 WHERE s1 >= 1000; 194s1 1951001 196Should see 1001 197ROLLBACK AND NO CHAIN; 198default transaction is now in REPEATABLE READ 199connection con1 200INSERT INTO t1 VALUES (1002); 201COMMIT; 202connection default 203SELECT * FROM t1 WHERE s1 >= 1000; 204s1 2051001 2061002 207Should see 1001 and 1002 208connection con1 209INSERT INTO t1 VALUES (1003); 210COMMIT; 211connection default 212SELECT * FROM t1 WHERE s1 >= 1000; 213s1 2141001 2151002 216Should see 1001 and 1002, but NOT 1003 217COMMIT; 218SELECT * FROM t1; 219s1 2201 2212 222-1 2231001 2241002 2251003 226DELETE FROM t1 WHERE s1 >= 1000; 227COMMIT AND NO CHAIN; 228SET @@completion_type=0; 229COMMIT; 230connection default 231SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 232SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 233START TRANSACTION; 234SELECT * FROM t1; 235s1 2361 2372 238-1 239connection con1 240INSERT INTO t1 VALUES (1000); 241COMMIT; 242connection default 243SELECT * FROM t1; 244s1 2451 2462 247-1 248Should get same result as above (i.e should not read '1000') 249COMMIT; 250DELETE FROM t1 WHERE s1 >= 1000; 251COMMIT; 252SET @@completion_type=1; 253COMMIT AND NO CHAIN; 254SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 255START TRANSACTION; 256TRUNCATE TABLE t1; 257INSERT INTO t1 VALUES (1000); 258SELECT * FROM t1; 259s1 2601000 261Should read '1000' 262connection con1 263INSERT INTO t1 VALUES (1001); 264COMMIT; 265connection default 266SELECT * FROM t1; 267s1 2681000 269Should only read the '1000' as this transaction is now in REP READ 270COMMIT AND NO CHAIN; 271SET @@completion_type=0; 272COMMIT AND NO CHAIN; 273SET @@autocommit=1; 274COMMIT; 275DROP TABLE t1; 276# 277# End of test cases for Bug#20837 278# 279# 280# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); 281# 282# 283# Test 1: Check supported syntax 284START TRANSACTION; 285COMMIT; 286START TRANSACTION READ ONLY; 287COMMIT; 288START TRANSACTION READ WRITE; 289COMMIT; 290START TRANSACTION READ ONLY, READ WRITE; 291ERROR 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 292START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT; 293COMMIT; 294START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT; 295COMMIT; 296START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY; 297COMMIT; 298START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE; 299COMMIT; 300START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE; 301ERROR 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 302SET TRANSACTION READ ONLY; 303SET TRANSACTION READ WRITE; 304SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 305SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE; 306SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED; 307SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED; 308SET TRANSACTION READ ONLY, READ WRITE; 309ERROR 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 310COMMIT; 311# 312# Test 2: Check setting of variable. 313SET SESSION TRANSACTION READ WRITE; 314SELECT @@tx_read_only; 315@@tx_read_only 3160 317Warnings: 318Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 319SET SESSION TRANSACTION READ ONLY; 320SELECT @@tx_read_only; 321@@tx_read_only 3221 323Warnings: 324Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 325SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 326SELECT @@tx_read_only; 327@@tx_read_only 3280 329Warnings: 330Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 331SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; 332SELECT @@tx_read_only; 333@@tx_read_only 3341 335Warnings: 336Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 337START TRANSACTION; 338# Not allowed inside a transaction 339SET TRANSACTION READ ONLY; 340ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress 341# But these are allowed. 342SET SESSION TRANSACTION READ ONLY; 343SET GLOBAL TRANSACTION READ ONLY; 344COMMIT; 345SET SESSION TRANSACTION READ WRITE; 346SET GLOBAL TRANSACTION READ WRITE; 347# 348# Test 3: Test that write operations are properly blocked. 349CREATE TABLE t1(a INT); 350CREATE TEMPORARY TABLE temp_t2(a INT); 351SET SESSION TRANSACTION READ ONLY; 352# 1: DDL should be blocked, also on temporary tables. 353CREATE TABLE t3(a INT); 354ERROR 25006: Cannot execute statement in a READ ONLY transaction. 355ALTER TABLE t1 COMMENT "Test"; 356ERROR 25006: Cannot execute statement in a READ ONLY transaction. 357DROP TABLE t1; 358ERROR 25006: Cannot execute statement in a READ ONLY transaction. 359CREATE TEMPORARY TABLE temp_t3(a INT); 360ERROR 25006: Cannot execute statement in a READ ONLY transaction. 361ALTER TABLE temp_t2 COMMENT "Test"; 362ERROR 25006: Cannot execute statement in a READ ONLY transaction. 363DROP TEMPORARY TABLE temp_t2; 364ERROR 25006: Cannot execute statement in a READ ONLY transaction. 365CREATE FUNCTION f1() RETURNS INT RETURN 1; 366ERROR 25006: Cannot execute statement in a READ ONLY transaction. 367DROP FUNCTION f1; 368ERROR 25006: Cannot execute statement in a READ ONLY transaction. 369CREATE PROCEDURE p1() BEGIN END; 370ERROR 25006: Cannot execute statement in a READ ONLY transaction. 371DROP PROCEDURE p1; 372ERROR 25006: Cannot execute statement in a READ ONLY transaction. 373CREATE VIEW v1 AS SELECT 1; 374ERROR 25006: Cannot execute statement in a READ ONLY transaction. 375SET SESSION TRANSACTION READ WRITE; 376CREATE VIEW v1 AS SELECT 1; 377SET SESSION TRANSACTION READ ONLY; 378DROP VIEW v1; 379ERROR 25006: Cannot execute statement in a READ ONLY transaction. 380SET SESSION TRANSACTION READ WRITE; 381DROP VIEW v1; 382SET SESSION TRANSACTION READ ONLY; 383RENAME TABLE t1 TO t2; 384ERROR 25006: Cannot execute statement in a READ ONLY transaction. 385RENAME TABLE temp_t2 TO temp_t3; 386ERROR 25006: Cannot execute statement in a READ ONLY transaction. 387TRUNCATE TABLE t1; 388ERROR 25006: Cannot execute statement in a READ ONLY transaction. 389CREATE DATABASE db1; 390ERROR 25006: Cannot execute statement in a READ ONLY transaction. 391DROP DATABASE db1; 392ERROR 25006: Cannot execute statement in a READ ONLY transaction. 393SET SESSION TRANSACTION READ WRITE; 394# 2: DML should be blocked on non-temporary tables. 395START TRANSACTION READ ONLY; 396INSERT INTO t1 VALUES (1), (2); 397ERROR 25006: Cannot execute statement in a READ ONLY transaction. 398UPDATE t1 SET a= 3; 399ERROR 25006: Cannot execute statement in a READ ONLY transaction. 400DELETE FROM t1; 401ERROR 25006: Cannot execute statement in a READ ONLY transaction. 402# 3: DML should be allowed on temporary tables. 403INSERT INTO temp_t2 VALUES (1), (2); 404UPDATE temp_t2 SET a= 3; 405DELETE FROM temp_t2; 406# 4: Queries should not be blocked. 407SELECT * FROM t1; 408a 409SELECT * FROM temp_t2; 410a 411HANDLER t1 OPEN; 412HANDLER t1 READ FIRST; 413a 414HANDLER t1 CLOSE; 415HANDLER temp_t2 OPEN; 416HANDLER temp_t2 READ FIRST; 417a 418HANDLER temp_t2 CLOSE; 419# 5: Prepared statements 420PREPARE stmt FROM "DELETE FROM t1"; 421ERROR 25006: Cannot execute statement in a READ ONLY transaction. 422PREPARE stmt FROM "DELETE FROM temp_t2"; 423EXECUTE stmt; 424DEALLOCATE PREPARE stmt; 425COMMIT; 426# 6: Stored routines 427CREATE FUNCTION f1() RETURNS INT 428BEGIN 429DELETE FROM t1; 430RETURN 1; 431END| 432CREATE FUNCTION f2() RETURNS INT 433BEGIN 434DELETE FROM temp_t2; 435RETURN 1; 436END| 437CREATE PROCEDURE p1() DELETE FROM t1; 438CREATE PROCEDURE p2() DELETE FROM temp_t2; 439START TRANSACTION READ ONLY; 440SELECT f1(); 441ERROR 25006: Cannot execute statement in a READ ONLY transaction. 442SELECT f2(); 443f2() 4441 445CALL p1(); 446ERROR 25006: Cannot execute statement in a READ ONLY transaction. 447CALL p2(); 448COMMIT; 449DROP FUNCTION f1; 450DROP FUNCTION f2; 451DROP PROCEDURE p1; 452DROP PROCEDURE p2; 453# 7: Views 454CREATE VIEW v1 AS SELECT a FROM t1; 455START TRANSACTION READ ONLY; 456INSERT INTO v1 VALUES (1), (2); 457ERROR 25006: Cannot execute statement in a READ ONLY transaction. 458SELECT * FROM v1; 459a 460COMMIT; 461DROP VIEW v1; 462# 8: LOCK TABLE 463SET SESSION TRANSACTION READ ONLY; 464LOCK TABLE t1 WRITE; 465ERROR 25006: Cannot execute statement in a READ ONLY transaction. 466LOCK TABLE t1 READ; 467UNLOCK TABLES; 468SET SESSION TRANSACTION READ WRITE; 469DROP TABLE temp_t2, t1; 470# 471# Test 4: SET TRANSACTION, CHAINing transactions 472CREATE TABLE t1(a INT); 473SET SESSION TRANSACTION READ ONLY; 474START TRANSACTION; 475DELETE FROM t1; 476ERROR 25006: Cannot execute statement in a READ ONLY transaction. 477COMMIT; 478START TRANSACTION READ WRITE; 479DELETE FROM t1; 480COMMIT; 481SET SESSION TRANSACTION READ WRITE; 482SET TRANSACTION READ ONLY; 483START TRANSACTION; 484DELETE FROM t1; 485ERROR 25006: Cannot execute statement in a READ ONLY transaction. 486COMMIT; 487START TRANSACTION READ WRITE; 488DELETE FROM t1; 489COMMIT; 490START TRANSACTION READ ONLY; 491SELECT * FROM t1; 492a 493COMMIT AND CHAIN; 494DELETE FROM t1; 495ERROR 25006: Cannot execute statement in a READ ONLY transaction. 496COMMIT; 497START TRANSACTION READ ONLY; 498SELECT * FROM t1; 499a 500ROLLBACK AND CHAIN; 501DELETE FROM t1; 502ERROR 25006: Cannot execute statement in a READ ONLY transaction. 503COMMIT; 504DROP TABLE t1; 505# 506# Test 5: Test that reserved keyword ONLY is still allowed as 507# identifier - both directly and in SPs. 508SET @only= 1; 509CREATE TABLE t1 (only INT); 510INSERT INTO t1 (only) values (1); 511SELECT only FROM t1 WHERE only = 1; 512only 5131 514DROP TABLE t1; 515CREATE PROCEDURE p1() 516BEGIN 517DECLARE only INT DEFAULT 1; 518END| 519CALL p1(); 520DROP PROCEDURE p1; 521# 522# Test 6: Check that XA transactions obey default access mode. 523CREATE TABLE t1(a INT); 524SET TRANSACTION READ ONLY; 525XA START 'test1'; 526INSERT INTO t1 VALUES (1); 527ERROR 25006: Cannot execute statement in a READ ONLY transaction. 528UPDATE t1 SET a=2; 529ERROR 25006: Cannot execute statement in a READ ONLY transaction. 530DELETE FROM t1; 531ERROR 25006: Cannot execute statement in a READ ONLY transaction. 532XA END 'test1'; 533XA PREPARE 'test1'; 534XA COMMIT 'test1'; 535DROP TABLE t1; 536# 537# Test 7: SET TRANSACTION inside stored routines 538CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; 539CALL p1(); 540SELECT @@tx_read_only; 541@@tx_read_only 5421 543Warnings: 544Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 545SET SESSION TRANSACTION READ WRITE; 546DROP PROCEDURE p1; 547CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, 548ISOLATION LEVEL SERIALIZABLE; 549CALL p1(); 550SELECT @@tx_read_only; 551@@tx_read_only 5521 553Warnings: 554Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 555SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; 556DROP PROCEDURE p1; 557CREATE FUNCTION f1() RETURNS INT 558BEGIN 559SET SESSION TRANSACTION READ ONLY; 560RETURN 1; 561END| 562SELECT f1(); 563f1() 5641 565SELECT @@tx_read_only; 566@@tx_read_only 5671 568Warnings: 569Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 570SET SESSION TRANSACTION READ WRITE; 571DROP FUNCTION f1; 572CREATE FUNCTION f1() RETURNS INT 573BEGIN 574SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 575RETURN 1; 576END| 577SELECT f1(); 578f1() 5791 580SELECT @@tx_read_only; 581@@tx_read_only 5821 583Warnings: 584Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead 585SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 586DROP FUNCTION f1; 587# 588# Test 8: SET TRANSACTION and auto-commit 589SELECT @@autocommit; 590@@autocommit 5911 592CREATE TABLE t1(a INT) engine=InnoDB; 593SET TRANSACTION READ ONLY; 594SELECT * FROM t1; 595a 596# This statement should work, since last statement committed. 597INSERT INTO t1 VALUES (1); 598DROP TABLE t1; 599