更新时间: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
父主题: 事务管理