如何验证GBase8s数据库ACID特性？

Question

如何验证GBase8s数据库ACID特性？

这四个特性是通过什么原理来验证的？

Answer 1

事务原子性验证：
Set environment sqlmode 'gbase';
--创建测试表，并插入数据
Drop table if exists test_acid_a;
create table test_acid_a(id int,col1 int);
insert into test_acid_a values(1,0);
insert into test_acid_a values(2,0);
insert into test_acid_a values(3,0);
insert into test_acid_a values(4,0);
insert into test_acid_a values(5,0);
insert into test_acid_a values(6,0);
insert into test_acid_a values(7,0);
insert into test_acid_a values(8,0);
insert into test_acid_a values(9,0);
insert into test_acid_a values(10,0);
insert into test_acid_a values(11,0);
insert into test_acid_a values(12,0);
select * from test_acid_a;
--随机commit 和rollback
--创建随机函数
drop function if exists sp_random;
create function sp_random(m int,n int) returning int;
define global seed decimal(10) default 1;
define d decimal(20,0);
let d =(seed * 1103515245)+ 12345;
let seed =d - 4294967296 * TRUNC( d / 4294967296);
if(mod(trunc(seed / 65536),n)<m)
then return m;
else
return mod(trunc(seed/65536),n);
end if;
end function;

--适配样例中的存储过程，实现随机commit\rollback
drop procedure protest ;
create procedure protest()
define i int;
define rand int;
for i in (1 to 99999) loop
begin work;
update test_acid_a set col1=i where id=1;
update test_acid_a set col1=i where id=2;
update test_acid_a set col1=i where id=3;
update test_acid_a set col1=i where id=4;
update test_acid_a set col1=i where id=5;
update test_acid_a set col1=i where id=6;
update test_acid_a set col1=i where id=7;
update test_acid_a set col1=i where id=8;
update test_acid_a set col1=i where id=9;
update test_acid_a set col1=i where id=10;
update test_acid_a set col1=i where id=11;
update test_acid_a set col1=i where id=12;
select sp_random(1,99999) into rand from dual;
if mod(rand,2)=1 then
commit;
else
rollback;
end if;
end loop;
end procedure;

call protest();
通过ctrl+c等方式中断运行，并查询每行的col1的都一致
select * from test_acid_a;

事务一致性验证
创建测试表，并插入数据
create table test_acid_c (id int,col1 varchar2 (10));
登录不同会话，执行以下操作：
会话1 会话2
begin work;
insert into test_acid_c values(1,'a');
insert into test_acid_c values(2,'b');
select * from test_acid_c;
--等待或查出0条数
commit;
select * from test_acid_c;
--查询出两条记录

事务隔离性验证
创建测试表，并插入数据
create table test_acid_i(id int,col varchar2(10));
登录不同会话，执行以下操作：
会话1 会话2
begin work; begin work;
insert into test_acid_i values(1,'a'); insert into test_acid_i values(2,'b');
select * from test_acid_i;
--查询出一条记录 select * from test_acid_i;
--查询出一条记录
commit; commit;
select * from test_acid_i;
--查询出两条记录 select * from test_acid_i;
--查询出两条记录

事务持久性验证
退出会话，重新登录后查询
select * from test_acid_i;
--查询出两条记录