KingbaseES中的ora_statement_level_rollback参数
文章概述
参数 ora_statement_level_rollback 控制KingbaseES 是否实现类似oracle 语句级的回滚。当该参数打开时，如果事务操作失败，仅会回滚触发异常的语句，避免了全部操作的回滚。默认情况下该参数为OFF，也就是说DML语句在PLSQL块中出错后默认是全部回滚：
---查看数据库默认的ora_statement_level_rollback配置为OFF
test=# show ora_statement_level_rollback
test-# /
ORA_STATEMENT_LEVEL_ROLLBACK
------------------------------
off
(1 row)
test=#
以下举例在不同数据库上的执行异同：
一，KES
1，开启ora_statement_level_rollback 参数测试
set ora_statement_level_rollback to on;
DROP TABLE IF EXISTS mytab CASCADE;
CREATE TABLE mytab(firstname TEXT, lastname TEXT);
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PROC() AS
x int := 1;
BEGIN
INSERT INTO mytab(firstname, lastname) VALUES('Jack', 'Ben');
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
RAISE NOTICE 'caught division_by_zero';
END;
/
调用存储过程,查看测试结果:
test=# select * from mytab; --执行前的检查
test-# /
FIRSTNAME | LASTNAME
-----------+----------
Tom | Jones
(1 row)
test=# call proc(); ----执行存储过程
test-# /
NOTICE: caught division_by_zero
CALL
test=# select * from mytab; --执行后的检查
test-# /
FIRSTNAME | LASTNAME
-----------+----------
Jack | Ben
Joe | Jones
(2 rows)
test=#
可以看出，并没回滚除零异常前的DML语句。
提问：这里如果没有异常处理，会回滚update嘛？会的。
----接着上面的代码和表继续测试
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PROC2() AS
x int := 1;
BEGIN
UPDATE mytab SET firstname = 'Lucy' WHERE lastname = 'Ben';
x := x / 0;
END;
/
执行并查看结果：
---执行没异常处理的存储过程
test=# call proc2();
test-# /
ERROR: division by zero
CONTEXT: PL/SQL function proc2() line 5 at assignment
test=# select * from mytab;
test-# /
FIRSTNAME | LASTNAME
-----------+----------
Jack | Ben
Joe | Jones
(2 rows)
test=#
可以看到，全局回滚了，UPDATE 语句执行生效。
2，关闭ora_statement_level_rollback 参数测试
先完成以下创建
set ora_statement_level_rollback to off;
DROP TABLE IF EXISTS mytab CASCADE;
CREATE TABLE mytab(firstname TEXT, lastname TEXT);
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
\set SQLTERM /
CREATE OR REPLACE PROCEDURE PROC() AS
x int := 1;
BEGIN
INSERT INTO mytab(firstname, lastname) VALUES('Jack', 'Ben');
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
RAISE NOTICE 'caught division_by_zero';
END;
/
执行测试：
test=# select * from mytab;
test-# /
FIRSTNAME | LASTNAME
-----------+----------
Tom | Jones
(1 row)
test=# call proc();
test-# /
NOTICE: caught division_by_zero
CALL
test=# select * from mytab;
test-# /
FIRSTNAME | LASTNAME
-----------+----------
Tom | Jones
(1 row)
可以看到，即使有exception也全局回滚，符合预期。同理执行了没有exception处理下的proc2()测试，也是全局回滚。
因此KES默认下PLSQL块中就是事务全局回滚
二，Oracle
均是在默认参数情况下测试，测试准备代码：
DROP TABLE mytab ;
CREATE TABLE mytab(firstname varchar(20), lastname varchar(20));
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
CREATE OR REPLACE PROCEDURE PROC AS
x int := 1;
BEGIN
INSERT INTO mytab(firstname, lastname) VALUES('Jack', 'Ben');
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('caught division_by_zero');
END;
执行测试：
call PROC();
select * from mytab;
--测试结果
Joe Jones
Jack Ben
和开启参数的KES一样，语句级回滚，未全局回滚。
接着测试：
---没有exception的函数PROC2
CREATE OR REPLACE PROCEDURE PROC2 AS
x int := 1;
BEGIN
UPDATE mytab SET firstname = 'Lucy' WHERE lastname = 'Ben';
x := x / 0;
END;
call PROC2();
select * from mytab;
---测试结果，UPDATE 未生效，全局回滚了。
Joe Jones
Jack Ben
三，SqlServer
我们先把PLSQL代码翻译成SQLServer的语法，准备如下代码：
DROP TABLE IF EXISTS mytab ;
CREATE TABLE mytab(firstname varchar(20), lastname varchar(20));
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
go
create or alter procedure proc1 ---吐槽sqlsevrer的proc是保留字，是procedure的缩写，这里不能命名为proc
as
begin
begin tran trans1
declare @x int = 1
begin try
insert into mytab(firstname, lastname) values('jack', 'ben')
update mytab set firstname = 'joe' where lastname = 'jones'
set @x = @x / 0
end try
begin catch
select error_line() as 错误代码行号,
error_message() as 错误消息,
error_number() as 错误代码
end catch;
end;
go
执行结果：
--测试前查看结果
select * from mytab;
--执行结果
Tom Jones
exec PROC1; --调用存储过程
---查看执行结果
select * from mytab;
--执行结果
Joe Jones
Jack Ben
显然，SQLServer默认情况下是语句级回滚。
四，小结：
Oracle和SQLServer默认情况下一样，PLSQL块中是语句级回滚。KingbaseES默认情况下是全局回滚，当开启ora_statement_level_rollback为on时则和
Oracle相同，实际测试如下：
1，有exception处理时，如果事务操作失败，仅会回滚触发异常的语句，不会全部操作的回滚。
2，没有exception处理时，如果事务操作失败，会全部操作的回滚。
即可以做到和oracle默认事务控制一致
KINGBASE研究院
