Help Center/
GaussDB/
Best Practices/
Best Practices for Stored Procedures/
Best Practices for Stored Procedures (Distributed Instances)/
Transaction Management/
Transactions
Updated on 2025-09-04 GMT+08:00
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
Parent topic: Transaction Management
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot