oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: shanyan.g
5
#owner group: transaction 
6
#description: 测试可串行化隔离级别在各种语句类型场景下的表现，及其限制
7
#
8
# 原则：
9
# 1. 无论是何种表类型，可串行化隔离级别统一采用事务级别快照
10
# 2. 目前仅支持读最新数据，不支持弱一致性读
11
# 3. 不支持指定快照读场景，指定快照读不应该出现在可串行化隔离级别中
12

13
# 创建database
14
--disable_warnings
15
drop database if exists xm_test;
16
drop database if exists xm_test_db1;
17
--enable_warnings
18
create database xm_test;
19
use xm_test;
20
# sleep一下，保证连接成功
21
sleep 5;
22
connect (conn1,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,xm_test,$OBMYSQL_PORT);
23
connect (conn2,$OBMYSQL_MS0,$OBMYSQL_USR,$OBMYSQL_PWD,xm_test,$OBMYSQL_PORT);
24
connection conn1;
25
#设置可串行化隔离级别
26
set tx_isolation = 'SERIALIZABLE';
27
set autocommit = 1;
28
connection conn2;
29
#设置可串行化隔离级别
30
set tx_isolation = 'SERIALIZABLE';
31
set autocommit = 1;
32

33
connection conn1;
34

35
#建表
36
create table xm_test_t1 (c1 int primary key, c2 int);
37
create table xm_test_t2 (c1 int primary key, c2 int);
38
#准备数据
39
insert into xm_test_t1 values(1,1);
40
insert into xm_test_t1 values(2,1);
41
insert into xm_test_t2 values(2,1);
42

43
########### case 1: 用户表强一致性读
44
begin;
45
select * from xm_test_t1;
46
insert into xm_test_t1 values (3, 1);
47
# 并行的session执行事务，插入数据
48
connection conn2;
49
insert into xm_test_t1 values (4, 1);
50
select * from xm_test_t1;
51
# 切回session，要求看不到并发事务的修改
52
connection conn1;
53
select * from xm_test_t1;
54
commit;
55

56
########### case 2: 用户表弱一致性读，期望报错，不支持
57
begin;
58
--error 1235
59
select /*+read_consistency(weak) */ * from xm_test_t1;
60
commit;
61

62
########### case 3: 事务中执行SHOW语句
63
# SHOW语句的内部实现实际上是在查询内部表，采用的是用户SESSION
64
# 大部分的SHOW语句查询的都是虚拟表，直接采用内存中的schema结构，这种情况下，SHOW语句与隔离级别无关，始终看到最新的数据；
65
# 对于show database like语句，实际查询的是__all_database，采用用户SESSION，受用户事务隔离级别影响。
66
begin;
67
select * from xm_test_t1;
68
insert into xm_test_t1 values (5, 1);
69
# show database like语句会查询__all_database表，这里会采用事务级别快照
70
show databases like 'xm_%';
71
# 新的连接上建database
72
connection conn2;
73
create database xm_test_db1;
74
show databases like 'xm_%';
75
insert into xm_test_t1 values (6, 1);
76
# 切回SESSION
77
# 再次show database like，应该看不到新创建的database
78
connection conn1;
79
show databases like 'xm_%';
80
select * from xm_test_t1;
81
commit;
82

83
########### case 4: 建索引语句
84
# 无论事务隔离级别是否是可串行化，都支持建索引
85
# 内部实现上，建索引语句采用内部SESSION事务，采用RC隔离级别
86
connection conn1;
87
create table t_global_index (pk int primary key) partition by hash(pk) partitions 10;
88
insert into t_global_index values (1), (2), (3);
89
create index index1 on t_global_index (pk) global;
90
--source mysql_test/include/check_all_idx_ok.inc
91
show index from t_global_index;
92
connection conn1;
93

94
########### case 5: 用户发起的内部表语句
95
# 要求始终读取一致的schema数据
96
connection conn1;
97
begin;
98
insert into xm_test_t1 values (7, 1);
99
select * from xm_test_t1;
100
# 查询内部表SQL
101
select table_name from oceanbase.__all_table 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');
102
# 并行的session创建表，修改xm_test_t1
103
connection conn2;
104
create table xm_test_t4 (pk int primary key);
105
insert into xm_test_t1 values (8, 1);
106
select * from xm_test_t1;
107
# 切回session，再次查询内部表，查到的数据与之前一致，看不到新增的表
108
connection conn1;
109
select table_name from oceanbase.__all_table 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');
110
# 弱一致性读的内部表语句要求也是一样的
111
select /*+read_consistency(weak) */ table_name from oceanbase.__all_table 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');
112
select * from xm_test_t1;
113
commit;
114

115
########### case 6: 内部发起的内部表语句，采用独立的内部SESSION，与现有事务没有关系，采用READ-COMMITTED隔离级别
116
# gv$table表实现上查询的是__all_virtual_table，__all_virtual_table实现上采用内部SESSION查询__all_table
117
# 期望每次读取都读到最新的数据
118
connection conn1;
119
begin;
120
insert into xm_test_t1 values (9, 1);
121
select * from xm_test_t1;
122
#select t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
123
# 并行的session创建表，修改xm_test_t1
124
connection conn2;
125
create table xm_test_t5 (pk int primary key);
126
insert into xm_test_t1 values (10, 1);
127
select * from xm_test_t1;
128
# 切回session，再次查询内部表，要求看到新建的表
129
connection conn1;
130
#select t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
131
# 弱一致性读的内部表语句要求也是一样的
132
#select /*+read_consistency(weak) */ t.table_name from oceanbase.gv$table as t where t.database_name='xm_test' and t.table_type=3;
133
select * from xm_test_t1;
134
commit;
135

136
disconnect conn1;
137
disconnect conn2;
138