事务出错,要求在出错语句处报错,并回滚整个事务。此功能与语句级功能有冲突。22.3语句级回滚是由插件提供的,要求参数lightdb_aborted_rollback和语句级回滚不能同时生效。
设计方法:在两个参数同时打开时,不能begin,end, rollback, rollback to操作事务,如这种情况发生,则报错:
测试
test1
正常情况和打开参数lightdb_aborted_rollback对比。
create table test_lightdb_aborted_rollback(id int, empname varchar(100));insert into test_lightdb_aborted_rollback values (1, 'songqingshu');
-- lightdb_aborted_rollback default off
--error
begin;
insert into test_lightdb_aborted_rollback values (2, 'zhangwuji');
insert into test_lightdb_aborted_rollback1 values (2, 'zhangwuji'); --error
rollback;
-- lightdb_aborted_rollback set to on
set lightdb_aborted_rollback to on;
begin;
insert into test_lightdb_aborted_rollback values (2, 'zhangwuji');
-- report error and rollback
-insert into test_lightdb_aborted_rollback1 values (2, 'zhangwuji');
test2
语句级回滚不生效:
set lightdb_aborted_rollback to on;
setlt_statement_rollback.enabled = on;
begin;
insert into test_lightdb_aborted_rollback values (2, 'zhangwuji');
-- report error and rollback
insert into test_lightdb_aborted_rollback1 values (2, 'zhangwuji');
test3
切换到oracle,语句级回滚生效:
set lightdb_aborted_rollback to on;
setlt_statement_rollback.enabled = on;
/* switch oracle*/
LOAD 'lt_statement_rollback.so';
create extension lt_statement_rollback ;
set lightdb_syntax_compatible_type to oracle;
set lt_statement_rollback.enabled = on;
--test1
set lightdb_aborted_rollback to off;
set lightdb_aborted_rollback to on;
set lt_statement_rollback.enabled = off;
set lt_statement_rollback.enabled = on;
--test2
--error, begin transcation
begin;
--test3
--error, end transcation
rollback;
--test4
--error, end transcation
end;
--test5
-- 关闭语句级回滚功能
set lt_statement_rollback.enabled to off;
--测试lightdb_aborted_rollback使能
begin;
insert into test_lightdb_aborted_rollback values (2, 'zhangwuji');
-- report error and rollback
insert into test_lightdb_aborted_rollback1 values (2, 'zhangwuji'); --error
--test6
-- lightdb_aborted_rollback使能
set lightdb_aborted_rollback to off;
set lt_statement_rollback.enabled = on;
--测试语句级回滚功能
begin;
insert into test_lightdb_aborted_rollback values (1, 'zhaomin');
insert into test_lightdb_aborted_rollback1 values (2, 'zhangwuji'); --error
select * from test_lightdb_aborted_rollback;
insert into test_lightdb_aborted_rollback values (2, 'zhangwuji');
select * from test_lightdb_aborted_rollback;
end;
# 注意
如果不关掉 lightdb_aborted_rollback,lt_statement_rollback.enabled其中一个,drop extension lt_statement_rollback后,事务依旧无法执行。