1
DROP TABLE IF EXISTS t1;
2
## Creating new table ##
9
## Creating new connections test_con1, test_con2 ##
10
#########################################################
11
# Setting initial value of completion_type to zero #
12
#########################################################
13
INSERT INTO t1 VALUES(1,'Record_1');
17
## Setting value of variable to 0 ##
18
SET @@session.completion_type = 0;
19
## Here commit & rollback should work normally ##
22
INSERT INTO t1 VALUES(2,'Record_2');
23
INSERT INTO t1 VALUES(3,'Record_3');
29
Switching to connection test_con1
30
## Don't expect to see id's 2 and 3 in the table w/o COMMIT ##
34
Switching to default connection
38
INSERT INTO t1 VALUES(4,'Record_4');
39
INSERT INTO t1 VALUES(5,'Record_5');
47
Switching to connection test_con1
48
## Don't expect to see id's 4 and 5 here ##
49
## Expect to see 3, Record_3 ##
55
Switching to connection default;
57
## Don't expect to see id's 4 and 5 now ##
64
#########################################################
65
# Setting initial value of completion_type to one #
66
#########################################################
67
Switching to connection test_con1;
68
SET @@session.completion_type = 1;
75
INSERT INTO t1 VALUES(6,'Record_6');
76
INSERT INTO t1 VALUES(7,'Record_7');
78
## Expect to immediately have a new transaction ##
79
INSERT INTO t1 VALUES(8,'Record_8');
88
switching to test_con2
89
## Do not expect to see 8, Record_8 as no COMMIT has occurred ##
97
switch to connection test_con1
98
## Testing ROLLBACK behavior
100
INSERT INTO t1 VALUES(9, 'Record_9');
101
INSERT INTO t1 VALUES(10, 'Record_10');
102
## Expect to see id's 8, 9, 10 here ##
103
## 8, Record_8 COMMITted with the start of this transaction ##
115
## id's 9 and 10 are gone now due to ROLLBACK ##
124
## Expect a new transaction ##
125
INSERT INTO t1 VALUES(9, 'Record_9');
126
Switching to connection test_con2
127
## Don't expect to see 9, Record_9 due to no COMMIT yet ##
136
Switching to connection test_con1
138
## Don't expect to see 9, Record_9
147
#########################################################
148
# Setting initial value of completion_type to 2 #
149
#########################################################
150
SET @@session.completion_type = 2;
151
## Here commit should work as COMMIT RELEASE ##
161
INSERT INTO t1 VALUES(9,'Record_9');
162
INSERT INTO t1 VALUES(10,'Record_10');
164
## Inserting rows should give error here because connection should ##
165
## disconnect after using COMMIT ##
166
INSERT INTO t1 VALUES(4,'Record_4');
167
Got one of the listed errors
168
switch to connection test_con2
169
SET @@session.completion_type = 2;
170
## Inserting rows and using Rollback which should Rollback & release ##
182
INSERT INTO t1 VALUES(11,'Record_11');
183
INSERT INTO t1 VALUES(12,'Record_12');
185
## Expect a failure due to COMMIT/ROLLBACK AND RELEASE behavior ##
186
INSERT INTO t1 VALUES(4,'Record_4');
187
Got one of the listed errors