验证Inceptor ACID 原子性，一致性，隔离性

1 创建普通ORC

set transaction.type=inceptor;
set plsql.compile.dml.check.semantic=false;
create database dbname;
use dbname;
drop table if exists orc_table;
create table orc_table(key int,value string) stored as orc;

原子性验证

操作：在任务中向 ORC事务表，和ORC表插入数据，然后检查ORC表是否有数据。

因为ORX表不支持单挑插入，会进行回滚，应该没有数据

--创建ORC事务表
drop table if exists atomicity_table;
create table atomicity_table(
key int,
value string
) 
clustered by (key) 
into 8 buckets 
stored as orc 
tblproperties('transactional'='true');

创建存储过程 验证原子性

create or replace procedure test_atomicity()
is
declare
	cnt int:=0;
begin
	begin transaction;
	  insert into atomicity_table values(1,'v1');
	  insert into atomicity_table values(2,'v2');
	  insert into orc_table values(1,'v1);
	commit;
 exception
 	when others then
 	 rollback;
 	 dbms_output.put_line('insert failed.')
 	 dbms_output.put_line('There is nothing be altered.');
 end;

 begin
 	set_env('plsql.catch.hive.exception',true);
 	test_atomicity();
 end;

 查看orc事务表 应该也没有数据

 select * from atomicity_table;

一致性验证

 任务：在事务中向ORC表插入数据，

 然后检查ORC事务表consistency_table中的数据是否被更新。

 ORC表不支持单条插入

 预期结果为数据未更新

创建orc事务表 并插入两条数据
 drop table id exists consistency_table;
 create table consistency_table(key int,value string)
 clustered by (key) into 8 buckets
 stored as orc tblproperties('transactional'='true');

 insert into consistency_table values(1,'v1');
 insert into consistency_table values(2,'v2');

 创建存储过程，验证事务一致性

 create or replace procedure test_consistency()
 is 
 declare
 	cnt int:=0;
 begin 
 	begin transaction;
 		update consistency_table set value='v1' where key = 1;
 		insert into orc_table value(3,'v3');
 		update consistency_table set value='v22' where key = 2;
 	commit;

 exception
 	when others then
 	rollback;
 	dbms_output.put_line('update failed');
 	dbms_output.put_line('There is nothing be altered.');
 end;

 begin 
 	set_env('plsql.catch.hive.exception',true);
 	test_consistency();
 end;

 查看由于插入orc表失败导致update操作也应该发生回滚

 select * from consistenc_table;

隔离性验证

任务：在第一个事务中Update ORC事务表但未提交，在第二个事务中再次Update表，
 最后集中提交事务。
 预期结果为第二个事务会等待第一个事务先提交。

 创建orc事务表，插入一条数据
 drop table if exists isolation_table;

 create table isolation_table(key int,value string)
 clustered by(key) into 8 buckets
 stored as orc
 tblproperties('transactional'='true');

 insert into isolation_table values(1,'v1');

 验证隔离性
create or replace procudure test_isolation()
is
declare
	cnt int:=0;
begin
	begin transaction;
		update isolation_table set value='v11' where key = 1;
	begin transaction;
		update isolation_table set value='updated2' where key = 1;
	commit;
	commit;

exception
	when other then
	rollback;
	dbms_output.put_line('update failed.');
	dbms_output.put_line('Threre is nothing be altered.');
end;

调用隔离验证存储过程
begin
	set_env('plsql.catch.hive.exception',true);
	test_isolation();
end;