create table TxnTable(c1 int);

# Begin transaction -> commit transaction
begin transaction;
set transaction isolation level read committed;
select @@trancount;
begin transaction;
select @@trancount;
#show transaction_isolation;
#show default_transaction_isolation;
insert into TxnTable values(1);
commit transaction;
select @@trancount;
commit transaction;
select @@trancount;
select c1 from TxnTable;

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

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

# Begin tran -> rollback tran
begin tran;
set transaction isolation level read uncommitted;
select @@trancount;
begin tran;
#show transaction_isolation;
#show default_transaction_isolation;
insert into TxnTable values(3);
select @@trancount;
rollback tran;
select @@trancount;
select c1 from TxnTable;

set transaction isolation level repeatable read;
#show transaction_isolation;
#show default_transaction_isolation;

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

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

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

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

# Begin transaction name -> commit transaction name
begin transaction txn1;
insert into TxnTable values(8);
commit transaction txn1;
select c1 from TxnTable;

# Begin transaction name -> rollback transaction name
begin transaction txn1;
insert into TxnTable values(9);
rollback transaction txn1;
select c1 from TxnTable;

# Begin tran name -> commit tran name
begin tran txn1;
insert into TxnTable values(10);
commit tran txn1;
select c1 from TxnTable;

# Begin tran name -> rollback tran name
begin tran txn1;
insert into TxnTable values(10);
rollback tran txn1;
select c1 from TxnTable;

truncate table TxnTable;

# save tran name -> rollback tran name
set transaction isolation level snapshot;
#show transaction_isolation;
#show default_transaction_isolation;
begin transaction txn1;
insert into TxnTable values(1);
save transaction sp1;
select @@trancount;
insert into TxnTable values(2);
save tran sp2;
insert into TxnTable values(3);
save tran sp2;
select @@trancount;
insert into TxnTable values(4);
select c1 from TxnTable;
rollback tran sp2;
select @@trancount;
select c1 from TxnTable;
rollback tran sp2;
select @@trancount;
select c1 from TxnTable;
rollback tran sp1;
select @@trancount;
select c1 from TxnTable;
rollback tran txn1;
select @@trancount;
select c1 from TxnTable;

# begin transaction name -> save transaction name -> rollback to first savepoint
begin transaction txn1;
insert into TxnTable values(1);
save transaction sp1;
insert into TxnTable values(2);
save transaction sp2;
insert into TxnTable values(3);
save transaction sp3;
insert into TxnTable values(4);
rollback tran sp1;
#rollback tran sp1; -- this will give an error
rollback tran;
select c1 from TxnTable;

# begin transaction name -> save transaction name -> rollback tran name, Rollback whole transaction
set transaction isolation level serializable;
#show transaction_isolation;
#show default_transaction_isolation;
begin transaction txn1;
insert into TxnTable values(1);
save transaction sp1;
begin transaction txn1;
insert into TxnTable values(2);
save transaction sp1;
insert into TxnTable values(3);
select @@trancount;
rollback tran txn1;
select @@trancount;
select c1 from TxnTable;

# begin transaction -> save transaction name -> rollback to savepoint, commit transaction
begin transaction txn1;
insert into TxnTable values(1);
save transaction sp1;
insert into TxnTable values(2);
select c1 from TxnTable;
rollback tran sp1;
commit transaction;
select c1 from TxnTable;

# begin transaction -> save transaction name -> rollback to savepoint
# save transaction name -> commit transaction
begin transaction txn1;
insert into TxnTable values(3);
save transaction sp1;
insert into TxnTable values(4);
select c1 from TxnTable;
rollback tran sp1;
save transaction sp2;
insert into TxnTable values(5);
commit transaction;
select c1 from TxnTable;

# begin transaction -> save transaction name -> error -> rollback to savepoint
# commit transaction
begin transaction txn1;
insert into TxnTable values(6);
save transaction sp1;
insert into TxnTable values(7);
#select c1 frm TxnTable; -- error
rollback tran sp1;
commit transaction;
select c1 from TxnTable;

Drop table TxnTable;