Commit intermediate queries without committing the entire procedure

Help Procedural SQL
,
1

Hi All, @hanson

I want to commit a transaction without committing the main block because main block may have several validations and based on the validation result, we may want to rollback the main block.
However we still want to commit the inner transaction.

I was trying this…

insert into arr1 values (1, “udit”);
insert into arr2 values (2, “khemka”);

create or replace procedure seperate_transaction() returns VARCHAR(50) as
declare
message VARCHAR(50);
begin
update arr1 set name = ‘john’ where id = 1; – first statement
message = seperate_transaction_inner(); – calling inner transaction
rollback; – This should rollback the first statement but inner transaction should be committed.
return “success rollback”;
end;
/

create or replace procedure seperate_transaction_inner() returns VARCHAR(50) as
begin
insert into table1 values(2);
start transaction;
update arr2 set name = ‘smith’ where id = 2;
commit;
return “success commit”;
end ;
/

call seperate_transaction();

When I check arr1 and arr2 tables, data is commited in both the tables…

What is the solution to this issue?

Regards
Himanshu

2

We don’t currently have the ability to do separate top-level transactions from within another transaction. There can only be one active multi-statement transaction on your session. When you commit, it commits all changes on your session since the last commit.

If you really need to do top-level transactions, I’d recommend breaking things into chunks and controlling it from an external client program. E.g a SQL statement in a transaction could return a status code to your client app, and it could do another transaction on a separate thread with a different connection.

You could use external functions to do a separate top-level transaction, but be aware that feature is currently in preview, not GA.

3

We are tracking this as a feature request. Could you tell us about your use case?

1 Like