126 lines
4.9 KiB
Plaintext
126 lines
4.9 KiB
Plaintext
--disable_query_log
|
|
set @@session.explicit_defaults_for_timestamp=off;
|
|
--enable_query_log
|
|
#
|
|
# 原则：
|
|
# 1. 无论是何种表类型，可串行化隔离级别统一采用事务级别快照
|
|
# 2. 目前仅支持读最新数据，不支持弱一致性读
|
|
# 3. 不支持指定快照读场景，指定快照读不应该出现在可串行化隔离级别中
|
|
|
|
# 创建database
|
|
--disable_warnings
|
|
drop database if exists xm_test;
|
|
drop database if exists xm_test_db1;
|
|
--enable_warnings
|
|
create database xm_test;
|
|
use xm_test;
|
|
# sleep一下，保证连接成功
|
|
sleep 5;
|
|
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,xm_test,$OBMYSQL_PORT);
|
|
connect (conn2,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,xm_test,$OBMYSQL_PORT);
|
|
connection conn1;
|
|
#设置可串行化隔离级别
|
|
set tx_isolation = 'SERIALIZABLE';
|
|
set autocommit = 1;
|
|
connection conn2;
|
|
#设置可串行化隔离级别
|
|
set tx_isolation = 'SERIALIZABLE';
|
|
set autocommit = 1;
|
|
|
|
connection conn1;
|
|
|
|
#建表
|
|
create table xm_test_t1 (c1 int primary key, c2 int);
|
|
create table xm_test_t2 (c1 int primary key, c2 int);
|
|
#准备数据
|
|
insert into xm_test_t1 values(1,1);
|
|
insert into xm_test_t1 values(2,1);
|
|
insert into xm_test_t2 values(2,1);
|
|
|
|
begin;
|
|
select * from xm_test_t1;
|
|
insert into xm_test_t1 values (3, 1);
|
|
# 并行的session执行事务，插入数据
|
|
connection conn2;
|
|
insert into xm_test_t1 values (4, 1);
|
|
select * from xm_test_t1;
|
|
# 切回session，要求看不到并发事务的修改
|
|
connection conn1;
|
|
select * from xm_test_t1;
|
|
commit;
|
|
|
|
begin;
|
|
--error 1235
|
|
select /*+read_consistency(weak) */ * from xm_test_t1;
|
|
commit;
|
|
|
|
# SHOW语句的内部实现实际上是在查询内部表，采用的是用户SESSION
|
|
# 大部分的SHOW语句查询的都是虚拟表，直接采用内存中的schema结构，这种情况下，SHOW语句与隔离级别无关，始终看到最新的数据；
|
|
# 对于show database like语句，实际查询的是__all_database，采用用户SESSION，受用户事务隔离级别影响。
|
|
begin;
|
|
select * from xm_test_t1;
|
|
insert into xm_test_t1 values (5, 1);
|
|
# show database like语句会查询__all_database表，这里会采用事务级别快照
|
|
show databases like 'xm_%';
|
|
# 新的连接上建database
|
|
connection conn2;
|
|
create database xm_test_db1;
|
|
show databases like 'xm_%';
|
|
insert into xm_test_t1 values (6, 1);
|
|
# 切回SESSION
|
|
# 再次show database like，应该看不到新创建的database
|
|
connection conn1;
|
|
show databases like 'xm_%';
|
|
select * from xm_test_t1;
|
|
commit;
|
|
|
|
# 无论事务隔离级别是否是可串行化，都支持建索引
|
|
# 内部实现上，建索引语句采用内部SESSION事务，采用RC隔离级别
|
|
connection conn1;
|
|
create table t_global_index (pk int primary key) partition by hash(pk) partitions 10;
|
|
insert into t_global_index values (1), (2), (3);
|
|
create index index1 on t_global_index (pk) global;
|
|
--source mysql_test/include/check_all_idx_ok.inc
|
|
show index from t_global_index;
|
|
connection conn1;
|
|
|
|
# 要求始终读取一致的schema数据
|
|
connection conn1;
|
|
begin;
|
|
insert into xm_test_t1 values (7, 1);
|
|
select * from xm_test_t1;
|
|
# 查询内部表SQL
|
|
select table_name from oceanbase.__all_table_v2 as t, oceanbase.__all_database as d where d.database_name='xm_test' and d.database_id = t.database_id and (t.table_name='xm_test_t1' or t.table_name='xm_test_t4');
|
|
# 并行的session创建表，修改xm_test_t1
|
|
connection conn2;
|
|
create table xm_test_t4 (pk int primary key);
|
|
insert into xm_test_t1 values (8, 1);
|
|
select * from xm_test_t1;
|
|
# 切回session，再次查询内部表，查到的数据与之前一致，看不到新增的表
|
|
connection conn1;
|
|
select table_name from oceanbase.__all_table_v2 as t, oceanbase.__all_database as d where d.database_name='xm_test' and d.database_id = t.database_id and (t.table_name='xm_test_t1' or t.table_name='xm_test_t4');
|
|
# 弱一致性读的内部表语句要求也是一样的
|
|
select /*+read_consistency(weak) */ table_name from oceanbase.__all_table_v2 as t, oceanbase.__all_database as d where d.database_name='xm_test' and d.database_id = t.database_id and (t.table_name='xm_test_t1' or t.table_name='xm_test_t4');
|
|
select * from xm_test_t1;
|
|
commit;
|
|
|
|
# gv$table表实现上查询的是__all_virtual_table，__all_virtual_table实现上采用内部SESSION查询__all_table_v2
|
|
# 期望每次读取都读到最新的数据
|
|
connection conn1;
|
|
begin;
|
|
insert into xm_test_t1 values (9, 1);
|
|
select * from xm_test_t1;
|
|
select t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
|
|
# 并行的session创建表，修改xm_test_t1
|
|
connection conn2;
|
|
create table xm_test_t5 (pk int primary key);
|
|
insert into xm_test_t1 values (10, 1);
|
|
select * from xm_test_t1;
|
|
# 切回session，再次查询内部表，要求看到新建的表
|
|
connection conn1;
|
|
select t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
|
|
# 弱一致性读的内部表语句要求也是一样的
|
|
select /*+read_consistency(weak) */ t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
|
|
select * from xm_test_t1;
|
|
commit;
|