AntDB 快速入门 / 常用高级特性5

分布式支持子事物

AntDB 只有 CN 节点可以运行子事物相关 SQL。其功能与集中式类似。但是下述场景排除在外。

• 分布式不支持子事务内 set 参数
• 分布式不支持 savepoint 之后 prepare transaction
• 分布式不支持嵌套子事务
• 分布式不支持匿名块，函数中定义子事物

示例如下：

-- 分布式不支持子事务内 set 参数
antdb=# BEGIN;
BEGIN
antdb=*# SAVEPOINT first_sp;
SAVEPOINT
antdb=*# SET vacuum_cost_delay TO 80.1;         --error
ERROR:  parameters cannot be set in subtransactions
antdb=!# rollback to SAVEPOINT first_sp;
ROLLBACK
antdb=*# release SAVEPOINT first_sp;
RELEASE
antdb=*# SET vacuum_cost_delay TO 80.1;         --ok
SET
antdb=*# rollback;
ROLLBACK


-- 分布式不支持 savepoint 之后 prepare transaction
antdb=# create table test_sp_prepared(a int, b text);
CREATE TABLE
antdb=# begin;
BEGIN
antdb=*# savepoint s1;
SAVEPOINT
antdb=*# prepare transaction 'x1';              --error
ERROR:  PREPARE TRANSACTION statement is not supported in transaction which has executed savepoint.
antdb=!# rollback to savepoint s1;
ROLLBACK
antdb=*# release s1;
RELEASE
antdb=*# insert into test_sp_prepared values (1,'aaa');
INSERT 0 1
antdb=*# update test_sp_prepared set b = 'bbb' where a = '1';
UPDATE 1
antdb=*# prepare transaction 'x1';               --error
ERROR:  PREPARE TRANSACTION statement is not supported in transaction which has executed savepoint.
antdb=!# rollback;
ROLLBACK
antdb=# drop table test_sp_prepared;
DROP TABLE


-- 分布式不支持嵌套子事务
antdb=# create table t_save(id int, num int);
CREATE TABLE
antdb=# BEGIN;
BEGIN
antdb=*#     INSERT INTO t_save VALUES (1,1);
INSERT 0 1
antdb=*#     SAVEPOINT s1;
SAVEPOINT
antdb=*#     INSERT INTO t_save VALUES (2,2);
INSERT 0 1
antdb=*#     SAVEPOINT s2;                              --error
ERROR:  do not support nested savepoint for now
antdb=!#     INSERT INTO t_save VALUES (3,3);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
antdb=!#     ROLLBACK TO s1;
ROLLBACK
antdb=*#     INSERT INTO t_save VALUES (4,4);
INSERT 0 1
antdb=*# COMMIT;
COMMIT
antdb=# select * from t_save order by 1;
 id | num
----+-----
  1 |   1
  4 |   4
(2 rows)

antdb=# drop table t_save;
DROP TABLE


-- 分布式不支持匿名块中定义子事物
antdb=# -- 匿名块
antdb=# DROP TABLE if exists temp_table;
NOTICE:  table "temp_table" does not exist, skipping
DROP TABLE
antdb=# CREATE TABLE temp_table (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE
antdb=# DO $$
antdb$# DECLARE
antdb$#   counter INTEGER := 0;
antdb$# BEGIN
antdb$#   -- 开始事务
antdb$#   BEGIN
antdb$#
antdb$#   -- 执行一系列操作
antdb$#   INSERT INTO temp_table (name) VALUES ('John'), ('Jane');
antdb$#
antdb$#   -- 增加计数器
antdb$#   counter := counter + 1;
antdb$#
antdb$#   -- 提交事务
antdb$#   -- COMMIT;
antdb$#   END;
antdb$#   -- 输出结果
antdb$#   RAISE NOTICE 'Inserted and updated % rows', counter;
antdb$# EXCEPTION
antdb$#   -- 处理异常
antdb$#   WHEN OTHERS THEN
antdb$#     -- 回滚事务
antdb$#     ROLLBACK;
antdb$#     RAISE NOTICE 'An error occurred: %', SQLERRM;
antdb$# END $$;
NOTICE:  An error occurred: cannot assign XIDs in child transaction 
DO
antdb=#
antdb=# select * from temp_table;          -- 表格仍为空，说明上述匿名块执行无效
 id | name
----+------
(0 rows)


-- 分布式不支持函数中定义子事物
antdb=# drop table if exists t1;
NOTICE:  table "t1" does not exist, skipping
DROP TABLE
antdb=# create table t1(a int, b text);
CREATE TABLE
antdb=#
antdb=# CREATE OR REPLACE FUNCTION custom_function() RETURNS VOID AS $$
antdb$# DECLARE
antdb$#   -- 声明变量或常量
antdb$# BEGIN
antdb$#   -- 开始事务
antdb$#   BEGIN
antdb$#     -- 在事务块内执行您的逻辑
antdb$#     -- 例如：
antdb$#     insert into t1 values (1,'aaa');
antdb$#     -- 其他数据库操作语句...
antdb$#   EXCEPTION
antdb$#     WHEN OTHERS THEN
antdb$#       -- 处理异常
antdb$#       RAISE NOTICE '发生异常: %', SQLERRM;
antdb$#   END;
antdb$#
antdb$#   -- 提交事务
antdb$# END;
antdb$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
antdb=#
antdb=# select custom_function();     --NOTICE:  发生异常: cannot assign XIDs in child transaction
NOTICE:  发生异常: cannot assign XIDs in child transaction
 custom_function
-----------------

(1 row)

antdb=# select * from t1;            -- 表格仍为空，说明上述函数执行无效
 a | b
---+---
(0 rows)
复制