# 配置事务参数
# 设置默认事务隔离级别
gs_guc set -D /opengauss/fgdata -c “default_transaction_isolation = ‘read committed'”

# 设置事务超时时间
gs_guc set -D /opengauss/fgdata -c “idle_in_transaction_session_timeout = ‘300s'”

# 设置死锁检测时间
gs_guc set -D /opengauss/fgdata -c “deadlock_timeout = ‘1s'”

# 重启数据库使参数生效
gs_ctl restart -D /opengauss/fgdata

风哥提示：
# 案例1：使用显式事务
BEGIN;
— 执行操作
INSERT INTO fgedu.fgedu_test (id, name) VALUES (1, ‘test’);
UPDATE fgedu.fgedu_test SET name = ‘updated’ WHERE id = 1;
— 提交事务
COMMIT;

# 案例2：使用保存点
BEGIN;
— 执行操作
INSERT INTO fgedu.fgedu_test (id, name) VALUES (2, ‘test2’);
— 创建保存点
SAVEPOINT my_savepoint;
— 执行其他操作
UPDATE fgedu.fgedu_test SET name = ‘updated2’ WHERE id = 2;
— 回滚到保存点
ROLLBACK TO SAVEPOINT my_savepoint;
— 提交事务
COMMIT;

# 案例3：使用事务隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
— 执行操作
SELECT * FROM fgedu.fgedu_test;
— 其他操作
SELECT pg_sleep(10);
— 再次查询，结果一致
SELECT * FROM fgedu.fgedu_test;
学习交流加群风哥微信: itpux-com
COMMIT;

# 查看事务执行情况
SELECT * FROM pg_stat_activity WHERE state = ‘active’;

# 查看长事务
SELECT now() – xact_start AS duration, current_query
FROM pg_stat_activity
WHERE state = ‘active’ AND xact_start IS NOT NULL
ORDER BY duration DESC;

# 优化事务：减少事务大小
# 优化前：大事务
BEGIN;
FOR i IN 1..10000 LOOP
INSERT INTO fgedu.fgedu_test (id, name) VALUES (i, ‘test’ || i);
END LOOP;
COMMIT;

# 优化后：小事务
DO $$
BEGIN
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO fgedu.fgedu_test (id, name) VALUES (i, ‘test’ || i);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END;
END LOOP;
END $$;