Debian Codegraph @

mysql-test/suite/storage_engine/trx/transaction_isolation.inc

raw
1 #
2 # Basic check for transaction isolation.
3 # The results should be different depending on the isolation level.
4 # For some isolation levels, some statements will end with a timeout.
5 # If the engine has its own timeout parameters, reduce them to minimum,
6 # otherwise the test will take very long.
7 # If the timeout value is greater than the testcase-timeout the test is run with,
8 # it might fail due to the testcase timeout.
9 #
10
11 --source ../have_engine.inc
12
13 # Save the initial number of concurrent sessions
14 --source include/count_sessions.inc
15
16 --disable_warnings
17 DROP TABLE IF EXISTS t1;
18 --enable_warnings
19
20 connect (con1,localhost,root,,);
21 eval SET SESSION TRANSACTION ISOLATION LEVEL $trx_isolation;
22 connect (con2,localhost,root,,);
23 eval SET SESSION TRANSACTION ISOLATION LEVEL $trx_isolation;
24
25 connection con1;
26
27 let $create_definition = a $int_col;
28 --source ../create_table.inc
29
30 START TRANSACTION;
31 --sorted_result
32 SELECT a FROM t1; # First snapshot
33
34 connection con2;
35
36 BEGIN;
37 --let $error_codes = 0,ER_LOCK_WAIT_TIMEOUT
38 INSERT INTO t1 (a) VALUES(1);
39 --source ../strict_check_errors.inc
40
41 connection con1;
42 --sorted_result
43 SELECT a FROM t1; # Second snapshot
44
45 connection con2;
46 --let $error_codes = 0,ER_LOCK_WAIT_TIMEOUT
47 INSERT INTO t1 (a) VALUES (2);
48 --source ../strict_check_errors.inc
49
50 connection con1;
51 --sorted_result
52 SELECT a FROM t1; # Third snapshot
53
54 --let $error_codes = 0,ER_LOCK_WAIT_TIMEOUT
55 INSERT INTO t1 (a) SELECT a+100 FROM t1;
56 --source ../strict_check_errors.inc
57
58 --sorted_result
59 SELECT a FROM t1;
60
61 connection con2;
62 --sorted_result
63 SELECT a FROM t1; # Inside the transaction
64 COMMIT;
65 --sorted_result
66 SELECT a FROM t1; # Outside the transaction
67
68 connection con1;
69 --sorted_result
70 SELECT a FROM t1; # Inside the transaction
71
72 # Note: INSERT .. SELECT might be tricky, for example for InnoDB
73 # even with REPEATABLE-READ it works as if it is executed with READ COMMITTED.
74 # The test will have a 'logical' result for repeatable read, even although
75 # we currently don't have an engine which works this way.
76
77 --let $error_codes = 0,ER_LOCK_WAIT_TIMEOUT
78 INSERT INTO t1 (a) SELECT a+200 FROM t1;
79 --source ../strict_check_errors.inc
80
81 --sorted_result
82 SELECT a FROM t1;
83 COMMIT;
84 --sorted_result
85 SELECT a FROM t1; # Outside the transaction
86
87 connection con2;
88 --sorted_result
89 SELECT a FROM t1; # After both transactions have committed
90
91 connection default;
92 disconnect con1;
93 disconnect con2;
94 DROP TABLE t1;
95
96 --source include/wait_until_count_sessions.inc
97