更新时间:2025-08-19 GMT+08:00
分享

事务

存储过程可以通过使用SAVEPOINT以及COMMIT/ROLLBACK来进行事务管理,如果使用不当,可能会引发以下问题:

  • 每次在事务中创建SAVEPOINT都会分配资源,若不及时释放,资源占用将逐渐累积。
  • 事务的COMMIT和ROLLBACK操作需要同步数据库的元数据和日志,频繁执行可能增加I/O开销,从而影响性能。

建议:

  • 在使用完SAVEPOINT后,应及时使用RELEASE SAVEPOINT来释放资源。
  • 避免在循环中创建SAVEPOINT,因为同名的SAVEPOINT不会覆盖,而是会重新创建,这可能导致资源迅速累积。
    gaussdb=# create schema best_practices_for_procedure;
    CREATE SCHEMA
    gaussdb=# create table best_practices_for_procedure.tb1(id int, name varchar2(20));
    CREATE TABLE
    --创建使用savepoint的存储过程。
    gaussdb=# create or replace procedure best_practices_for_procedure.proc1() as
    begin
    savepoint sp1; --不要在循环中使用SAVEPOINT。
    for i in 1..10 loop
    insert into best_practices_for_procedure.tb1 values(i, 'name'|| i);
    end loop;
    release savepoint sp1; --释放savepoint。
    end;
    /
    CREATE PROCEDURE
    gaussdb=# call best_practices_for_procedure.proc1();
     proc1
    -------
    
    (1 row)
    
    gaussdb=# drop schema best_practices_for_procedure cascade;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to table best_practices_for_procedure.tb1
    drop cascades to function best_practices_for_procedure.proc1()
    DROP SCHEMA
  • 避免频繁使用COMMIT/ROLLBACK。
    gaussdb=# create schema best_practices_for_procedure;
    CREATE SCHEMA
    gaussdb=# create table best_practices_for_procedure.tb1(id int, name varchar2(20));
    CREATE TABLE
    gaussdb=# create or replace procedure best_practices_for_procedure.proc1() as
    begin
    for i in 1..10 loop
    insert into best_practices_for_procedure.tb1 values(i, 'name'|| i);
    end loop;
    commit; --执行完循环之后commit,而不是在循环内重复commit。
    end;
    /
    CREATE PROCEDURE
    gaussdb=# call best_practices_for_procedure.proc1();
     proc1
    -------
    
    (1 row)
    
    gaussdb=# drop schema best_practices_for_procedure cascade;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to table best_practices_for_procedure.tb1
    drop cascades to function best_practices_for_procedure.proc1()
    DROP SCHEMA

相关文档