234 lines
4.5 KiB
Plaintext
234 lines
4.5 KiB
Plaintext
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;
|