DROP TABLE IF EXISTS t1;
## Creating new table ##
CREATE TABLE t1
(
id INT NOT NULL,
PRIMARY KEY (id),
name VARCHAR(30)
) ENGINE = INNODB;
## Creating new connections test_con1, test_con2 ##
#########################################################
#    Setting initial value of completion_type to zero   #
#########################################################
INSERT INTO t1 VALUES(1,'Record_1');
SELECT * FROM t1;
id	name
1	Record_1
## Setting value of variable to 0 ##
SET @@session.completion_type = 0;
## Here commit & rollback should work normally ##
## test commit ##
START TRANSACTION;
INSERT INTO t1 VALUES(2,'Record_2');
INSERT INTO t1 VALUES(3,'Record_3');
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
Switching to connection test_con1
## Don't expect to see id's 2 and 3 in the table w/o COMMIT ##
SELECT * FROM t1;
id	name
1	Record_1
Switching to default connection
COMMIT;
## test rollback ##
START TRANSACTION;
INSERT INTO t1 VALUES(4,'Record_4');
INSERT INTO t1 VALUES(5,'Record_5');
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
4	Record_4
5	Record_5
Switching to connection test_con1
## Don't expect to see id's 4 and 5 here ##
## Expect to see 3, Record_3 ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
Switching to connection default;
ROLLBACK;
## Don't expect to see id's 4 and 5 now ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3

#########################################################
#    Setting initial value of completion_type to one    #
#########################################################
Switching to connection test_con1;
SET @@session.completion_type = 1;
START TRANSACTION;
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
INSERT INTO t1 VALUES(6,'Record_6');
INSERT INTO t1 VALUES(7,'Record_7');
COMMIT;
## Expect to immediately have a new transaction ##
INSERT INTO t1 VALUES(8,'Record_8');
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
switching to test_con2
## Do not expect to see 8, Record_8 as no COMMIT has occurred ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
switch to connection test_con1
## Testing ROLLBACK behavior
START TRANSACTION;
INSERT INTO t1 VALUES(9, 'Record_9');
INSERT INTO t1 VALUES(10, 'Record_10');
## Expect to see id's 8, 9, 10 here ##
## 8, Record_8 COMMITted with the start of this transaction ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
9	Record_9
10	Record_10
ROLLBACK;
## id's 9 and 10 are gone now due to ROLLBACK ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
## Expect a new transaction ##
INSERT INTO t1 VALUES(9, 'Record_9');
Switching to connection test_con2
## Don't expect to see 9, Record_9 due to no COMMIT yet ##
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
Switching to connection test_con1
ROLLBACK;
## Don't expect to see 9, Record_9
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
#########################################################
#    Setting initial value of completion_type to 2      #
#########################################################
SET @@session.completion_type = 2;
## Here commit should work as COMMIT RELEASE ##
START TRANSACTION;
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
INSERT INTO t1 VALUES(9,'Record_9');
INSERT INTO t1 VALUES(10,'Record_10');
COMMIT;
## Inserting rows should give error here because connection should ##
## disconnect after using COMMIT ##
INSERT INTO t1 VALUES(4,'Record_4');
Got one of the listed errors
switch to connection test_con2
SET @@session.completion_type = 2;
## Inserting rows and using Rollback which should Rollback & release ##
START TRANSACTION;
SELECT * FROM t1;
id	name
1	Record_1
2	Record_2
3	Record_3
6	Record_6
7	Record_7
8	Record_8
9	Record_9
10	Record_10
INSERT INTO t1 VALUES(11,'Record_11');
INSERT INTO t1 VALUES(12,'Record_12');
ROLLBACK;
## Expect a failure due to COMMIT/ROLLBACK AND RELEASE behavior ##
INSERT INTO t1 VALUES(4,'Record_4');
Got one of the listed errors
DROP TABLE t1;
