Transactions

Stored procedures can use SAVEPOINT and COMMIT/ROLLBACK to manage transactions. Improper use of SAVEPOINT and COMMIT/ROLLBACK may cause the following problems:

• Resources are allocated each time a savepoint is created in a transaction. If the resources are not released promptly, resource consumption will gradually accumulate.
• The COMMIT and ROLLBACK operations of a transaction require synchronization of the database's metadata and logs, and frequent execution may increase I/O overhead, thereby affecting performance.

Suggestions:

• After using a savepoint, use RELEASE SAVEPOINT to release resources promptly.
• Do not create savepoints in a loop because savepoints with the same name will not overwrite each other but will be created again, potentially leading to rapid resource accumulation.

  gaussdb=# create schema best_practices_for_procedure;
  CREATE SCHEMA
  gaussdb=# create table best_practices_for_procedure.tb1(id int, name varchar2(20));
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  -- Create a stored procedure that uses a savepoint.
  gaussdb=# create or replace procedure best_practices_for_procedure.proc1() as
  begin
  savepoint sp1; -- Do not use the savepoint in a loop.
  for i in 1..10 loop
  insert into best_practices_for_procedure.tb1 values(i, 'name'|| i);
  end loop;
  release savepoint sp1; -- Release the 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

• Do not perform COMMIT or ROLLBACK frequently.

  gaussdb=# create schema best_practices_for_procedure;
  CREATE SCHEMA
  gaussdb=# create table best_practices_for_procedure.tb1(id int, name varchar2(20));
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  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 after the loop is executed, instead of repeatedly committing in the loop.
  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