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
begin
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;
rollback;
select * from sid_tran;
rollback;
select * from sid_tran;
insert into sid_tran
select 20, now(6), @@trancount;
select * from sid_tran;
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).
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
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…
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: