SET @base_seq = (SELECT CAST(variable_value AS UNSIGNED) FROM performance_schema.global_status WHERE variable_name = 'Lizard_commit_gcn');
show variables like "transaction_isolation";
Variable_name	Value
transaction_isolation	REPEATABLE-READ
# gcn and dd table
set innodb_snapshot_seq = @base_seq +1500;
select * from mysql.db limit 1;
create database dd_db;
create table dd_db.t1 (a int, b int, primary key (a));
set innodb_snapshot_seq = @base_seq +1600;
select * from dd_db.t1;
a	b
create table dd_db.t2 (a int, b int, primary key (a));
set innodb_snapshot_seq = @base_seq +1500;
select * from dd_db.t2;
ERROR HY000: The definition of the table required by the flashback query has changed
create table dd_db.t3 (a int, b int, primary key (a));
set innodb_snapshot_seq = @base_seq +1400;
select * from dd_db.t3;
ERROR HY000: The definition of the table required by the flashback query has changed
drop database dd_db;
#
# Insert ... select & create .... select & update
# 
create table t1 (a int, b int , primary key (a));
create table t2 (a int, b int , primary key (a));
# scn and snapshot
begin;
select * from t1;
a	b
insert into t1 values (10,0),(11,0);
select * from t1;
a	b
insert into t2 select * from t1;
create table t3 select * from t1;
select * from t2;
a	b
10	0
11	0
select * from t3;
a	b
10	0
11	0
delete from t1;
delete from t2;
drop table t3;
# gcn and snapshot
xa begin 'x1';
set innodb_snapshot_seq = @base_seq +2000;
select * from t1;
a	b
xa begin 'x2';
insert into t1 values (2101,0),(2102,0);
xa end 'x2';
xa prepare 'x2';
set innodb_commit_seq = @base_seq +2100;
xa commit 'x2';
select * from t1;
a	b
insert into t2 select * from t1;
select * from t2;
a	b
2101	0
2102	0
xa end 'x1';
xa commit 'x1' one phase;
select * from t2;
a	b
2101	0
2102	0
set innodb_snapshot_seq = @base_seq +2000;
select * from t1;
a	b
delete from t2;
set innodb_snapshot_seq = @base_seq +2000;
insert into t2 select * from t1;
select * from t2;
a	b
2101	0
2102	0
set innodb_snapshot_seq = @base_seq +2000;
create table t3 select * from t1;
select * from t3;
a	b
2101	0
2102	0
drop table t1,t2,t3;
#
# update 
#
create table u1 (a int , b int, primary key (a));
#scn
insert into u1 values (30,0);
begin;
select * from u1;
a	b
30	0
begin;
select * from u1;
a	b
30	0
insert into u1 values (31,0);
select * from u1;
a	b
30	0
update u1 set b=30;
select * from u1;
a	b
30	30
31	30
select * from u1;
a	b
30	0
rollback;
select * from u1;
a	b
30	0
31	0
commit;
select * from u1;
a	b
30	30
31	30
delete from u1;
#gcn
set innodb_commit_seq = @base_seq +2900;
insert into u1 values (2900, 0);
xa begin 'x1';
set innodb_snapshot_seq = @base_seq +3000;
select * from u1;
a	b
2900	0
xa begin 'x3';
set innodb_snapshot_seq = @base_seq +3000;
select * from u1;
a	b
2900	0
set innodb_commit_seq = @base_seq +3100;
insert into u1 values (3101, 0),(3102, 0);
select * from u1;
a	b
2900	0
update u1 set b = 3000;
select * from u1;
a	b
2900	3000
3101	3000
3102	3000
select * from u1;
a	b
2900	0
xa end 'x3';
xa commit 'x3' one phase;
select * from u1;
a	b
2900	0
3101	0
3102	0
xa end 'x1';
xa commit 'x1' one phase;
select * from u1;
a	b
2900	3000
3101	3000
3102	3000
delete from u1;
# update with snapshot_gcn/commit_gcn
set innodb_commit_seq = @base_seq +3200;
insert into u1 values (3201, 0),(3202, 0);
set innodb_snapshot_seq = @base_seq +3300;
update u1 set b = 3300 where a = 3201;
set innodb_commit_seq = @base_seq +3400;
update u1 set b = 3400 where a = 3202;
set innodb_snapshot_seq = @base_seq +3210;
select * from u1;
a	b
3201	3300
3202	0
set innodb_snapshot_seq = @base_seq +3410;
select * from u1;
a	b
3201	3300
3202	3400
delete from u1;
#
# external xa commit
#
xa begin '4000';
insert into u1 values (4000, 0);
xa end '4000';
xa prepare '4000';
set innodb_commit_seq = @base_seq +4000;
xa commit '4000';
xa begin '4100';
insert into u1 values (4100, 0);
xa end '4100';
xa prepare '4100';
set innodb_commit_seq = @base_seq +4100;
xa commit '4100';
set innodb_snapshot_seq = @base_seq +4050;
select * from u1;
a	b
4000	0
set innodb_snapshot_seq = @base_seq +4150;
select * from u1;
a	b
4000	0
4100	0
delete from u1;
#
# /*+ SET_VAR(innodb_snapshot_seq= ... ) */ && count(*)
# base_seq = 1000000;
#
xa begin '1000000';
insert into u1 values (1000000, 0);
xa end '1000000';
xa prepare '1000000';
set innodb_commit_seq = 1000000;
xa commit '1000000';
set innodb_snapshot_seq = 999999;
select * from u1;
a	b
set innodb_snapshot_seq = 999999;
select count(*) from u1;
count(*)
1
select /*+ SET_VAR(innodb_snapshot_seq= 999999) */ count(*) from u1;
count(*)
1
select /*+ SET_VAR(innodb_snapshot_seq= 999999) */ * from u1;
a	b
delete from u1;
drop table u1;