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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
--source include/have_debug.inc
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
SET @debug_saved = @@global.debug;
--echo
START TRANSACTION;
--echo
CREATE TABLE t1(a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(255)) engine = innodb;
--echo
INSERT INTO t1(b) VALUES ('aaa');
INSERT INTO t1(b) VALUES ('bbb');
INSERT INTO t1(b) VALUES ('ccc');
--echo
COMMIT;
###########################################################################
--echo
--echo # 1. Start outer transaction with SERIALIZABLE isolation level.
--echo # Check that SELECT in inner transaction will not take locks.
--echo
--echo # Start transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
--echo # Insert a new row into t1.
INSERT INTO t1(b) VALUES ('ddd');
SELECT * FROM t1;
--echo
--echo # [another connection]
--connect (con1,localhost,root,,)
--echo
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SET @@global.debug = '+d,use_attachable_trx';
SELECT * FROM t1;
SET @@global.debug = '-d,use_attachable_trx';
--disconnect con1
--echo
--echo # [default connection]
--connection default
--echo
ROLLBACK;
###########################################################################
--echo
--echo # 2. Check that inner transaction has different visibility scope than
--echo # the outer transaction.
--echo # Start READ ONLY transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION READ ONLY;
--echo # SELECT to actually start a transaction.
SELECT * FROM t1;
--echo
--echo # [another connection]
--connect (con1,localhost,root,,)
--echo
START TRANSACTION;
UPDATE t1 SET b = 'zzz' WHERE a = 2;
COMMIT;
--disconnect con1
--echo
--echo # [default connection]
--connection default
--echo
--echo # SELECT in the outer transaction doesn't see the changes.
SELECT * FROM t1;
--echo
--echo # SELECT in the inner transaction sees the changes.
SET @@global.debug = '+d,use_attachable_trx';
SELECT * FROM t1;
SET @@global.debug = '-d,use_attachable_trx';
--echo
--echo # COMMIT the outer transaction.
COMMIT;
--echo # SELECT in the outer transaction now sees the changes.
SELECT * FROM t1;
COMMIT;
###########################################################################
--echo
--echo # 3. Check that the inner transaction does not reset a save point set in
--echo # the outer transaction.
--echo
--echo # Start transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t1;
--echo
--echo # Set save point.
SAVEPOINT sp1;
--echo
--echo # Do some changes.
UPDATE t1 SET b = 'xxx' WHERE a = 2;
SELECT * FROM t1;
--echo
--echo # Do anything in the inner transaction.
SET @@global.debug = '+d,use_attachable_trx';
SELECT * FROM t1;
SET @@global.debug = '-d,use_attachable_trx';
--echo
--echo # Just make sure the changes are still there.
SELECT * FROM t1;
--echo
--echo # Rollback to the save point to make sure it was not reset.
ROLLBACK TO SAVEPOINT sp1;
--echo
--echo # Check that the changes have been reverted.
SELECT * FROM t1;
--echo
--echo # Commit.
COMMIT;
###########################################################################
--echo
--echo # Cleanup.
DROP TABLE t1;
--echo
SET @@global.debug = @debug_saved;
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
|
