create table txntable1(c1 int);

# Begin transaction -> commit transaction
txn#!#begin
select @@trancount;
#txn#!#begin#!#isolation#!#rc
#select @@trancount;
insert into txntable1 values(1);
txn#!#commit
select @@trancount;
#txn#!#commit
#select @@trancount;
select c1 from txntable1;

# Begin transaction -> rollback transaction
txn#!#begin
insert into txntable1 values(2);
txn#!#rollback
select c1 from txntable1;

#Begin tran -> commit tran
txn#!#begin
insert into txntable1 values(2);
txn#!#commit
select c1 from txntable1;

# Begin tran -> rollback tran
txn#!#begin
select @@trancount;
#txn#!#begin#!#isolation#!#ru
#show transaction_isolation;
#show default_transaction_isolation;
insert into txntable1 values(3);
select @@trancount;
txn#!#rollback
select @@trancount;
select c1 from txntable1;

# Begin transaction -> commit
txn#!#begin
insert into txntable1 values(4);
txn#!#commit;
select c1 from txntable1;

# Begin transaction -> commit work
txn#!#begin
insert into txntable1 values(5);
txn#!#commit
select c1 from txntable1;

# Begin transaction -> rollback
txn#!#begin
insert into txntable1 values(6);
txn#!#rollback
select c1 from txntable1;

# Begin transaction -> rollback work
txn#!#begin
insert into txntable1 values(7);
txn#!#rollback
select c1 from txntable1;

# Begin transaction name -> commit transaction name
txn#!#begin#!#tx1
insert into txntable1 values(8);
txn#!#commit#!#tx1
select c1 from txntable1;

# Begin transaction name -> rollback transaction name
txn#!#begin#!#tx1
insert into txntable1 values(9);
txn#!#rollback#!#tx1
select c1 from txntable1;

# Begin tran name -> commit tran name
txn#!#begin#!#tx1
insert into txntable1 values(10);
txn#!#commit#!#tx1
select c1 from txntable1;

# Begin tran name -> rollback tran name
txn#!#begin#!#tx1
insert into txntable1 values(10);
txn#!#rollback#!#tx1
select c1 from txntable1;

truncate table txntable1;

# save tran name -> rollback tran name
txn#!#begin#!#tx1#!#isolation#!#ss
insert into txntable1 values(1);
txn#!#savepoint#!#sp1
select @@trancount;
insert into txntable1 values(2);
txn#!#savepoint#!#sp2
insert into txntable1 values(3);
txn#!#savepoint#!#sp2
select @@trancount;
insert into txntable1 values(4);
select c1 from txntable1;
txn#!#rollback#!#sp2
select @@trancount;
select c1 from txntable1;
txn#!#rollback#!#sp2
select @@trancount;
select c1 from txntable1;
txn#!#rollback#!#sp1
select @@trancount;
select c1 from txntable1;
txn#!#rollback#!#tx1
select @@trancount;
select c1 from txntable1;

# begin transaction name -> save transaction name -> rollback to first savepoint
txn#!#begin#!#tx1
insert into txntable1 values(1);
txn#!#savepoint#!#sp1
insert into txntable1 values(2);
txn#!#savepoint#!#sp2
insert into txntable1 values(3);
txn#!#savepoint#!#sp3
insert into txntable1 values(4);
txn#!#rollback#!#sp1
#txn#!#rollback#!#sp1
txn#!#rollback
select c1 from txntable1;

# begin transaction name -> save transaction name -> rollback tran name, Rollback whole transaction
txn#!#begin#!#isolation#!#ss#!#tx1
insert into txntable1 values(1);
txn#!#savepoint#!#sp1
#txn#!#begin#!#tx1
insert into txntable1 values(2);
txn#!#savepoint#!#sp1
insert into txntable1 values(3);
select @@trancount;
txn#!#rollback#!#tx1
select @@trancount;
select c1 from txntable1;

# begin transaction -> save transaction name -> rollback to savepoint, commit transaction
txn#!#begin#!#tx1
insert into txntable1 values(1);
txn#!#savepoint#!#sp1
insert into txntable1 values(2);
select c1 from txntable1;
txn#!#rollback#!#sp1
txn#!#rollback#!#tx1
select c1 from txntable1;

# begin transaction -> save transaction name -> rollback to savepoint
# save transaction name -> commit transaction
txn#!#begin#!#tx1
insert into txntable1 values(3);
txn#!#savepoint#!#sp1
insert into txntable1 values(4);
select c1 from txntable1;
txn#!#rollback#!#sp1
txn#!#savepoint#!#sp2
insert into txntable1 values(5);
txn#!#commit ### not working
select c1 from txntable1;

# begin transaction -> save transaction name -> error -> rollback to savepoint
# commit transaction
txn#!#begin#!#tx1
insert into txntable1 values(6);
txn#!#savepoint#!#sp1
insert into txntable1 values(7);
select c1 frm txntable1;
txn#!#rollback#!#sp1
txn#!#commit
select c1 from txntable1;

# negative test cases for begin transaction
txn#!#begin#!#1txn
select @@trancount;
txn#!#commit

txn#!#begin#!#txn1;txn2
select @@trancount;
txn#!#commit

txn#!#begin#!#txn1()
select @@trancount;
txn#!#commit

DROP TABLE txntable1;

CREATE TABLE txntable2 (col1 INT, col2 TEXT);

# begin transaction -> save transaction name -> SELECT -> rollback to savepoint --> INSERT --> commit tran
txn#!#begin#!#tx1
txn#!#savepoint#!#sp1
select * from txntable2 order by col1;
txn#!#rollback#!#sp1
insert into txntable2 values(1, 'AAA');
txn#!#commit$!#tx1
txn#!#commit$!#tx1

select * from txntable2;

delete from txntable2;

# begin transaction -> save transaction name -> SELECT -> rollback to savepoint --> INSERT --> rollback tran
txn#!#begin#!#tx1
txn#!#savepoint#!#sp1
select * from txntable2 order by col1;
txn#!#rollback#!#sp1
insert into txntable2 values(1, 'AAA');
txn#!#rollback

select * from txntable2;

delete from txntable2;

# begin transaction -> save transaction name -> SELECT -> rollback top level tran --> INSERT --> rollback tran
txn#!#begin#!#tx1
txn#!#savepoint#!#sp1
select * from txntable2 order by col1;
txn#!#rollback#!#tx1
insert into txntable2 values(1, 'AAA');
txn#!#rollback

select * from txntable2;

delete from txntable2;

# begin transaction -> save transaction name -> SELECT -> rollback top level tran --> INSERT --> commit tran
txn#!#begin#!#tx1
txn#!#savepoint#!#sp1
select * from txntable2 order by col1;
txn#!#rollback#!#tx1
insert into txntable2 values(1, 'AAA');
txn#!#commit
select * from txntable2;

DROP TABLE txntable2;
