Nested transaction

Does memsql support nested transactions? I wish I could cut-paste my test from my office login. Anyway, I am using 7.14

create table sid_tran
col1 int primary key,
col2 timestamp(6) not null,
tran_cnt smallint not null

delimiter $$
create or replace procedure tran_test () as
delete from sid_tran;
start transaction;
insert into sid_tran
select 10, now(6), @@trancount;

select * from sid_tran;

start transaction;
insert into sid_tran
select 20, now(6), @@trancount;

select * from sid_tran;
select * from sid_tran;

select * from sid_tran;

insert into sid_tran
select 20, now(6), @@trancount;

 select * from sid_tran;

end; $$
delimiter ;


No, we don’t currently support real nested transactions. Starting a 2nd transaction will automatically commit an already open transaction (Similar to MySQL whose behavior we are reasonably compatible with).


@sid2sarkar Let’s ignore the precise feature you mentioned – “nested transactions” for the moment. Can you tell us what you want to achieve?

Hi Hanson, I have a similar problem.

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 trasaction.

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
message VARCHAR(50);
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”;

create or replace procedure seperate_transaction_inner() returns VARCHAR(50) as
insert into table1 values(2);
start transaction;
update arr2 set name = ‘smith’ where id = 2;
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?


Hi Himanshu!

SingleStore does not support nested transactions. Unfortunately, trying to do so can lead to strange behavior, such as automatically committing an open transaction upon starting a new one.

It would be best to break out this logic into multiple transactions.

The desired functionality can be achieved in SingleStore by leveraging the following documentation:

We hope this documentation is helpful. Please keep us posted on how it works out for you. Thanks