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