1

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit?

example:

pseudo procedure proc1

create procedure proc1(in var1 int, out var2 int)
begin
  start transaction;
  -- some code here
  commit;
end

pseudo procedure proc2, the one will call the proc1

create procedure proc2()
lbl_begin:
begin
  start transaction;
    -- modify some data here
    call proc1(0, var2);

    if var != 0 then
      rollback;
      leave lbl_begin;
    end;
  commit;
end;

Even the rollback on proc2 is executed, the data modified before the call of the proc1 is still committed.

Is it a bug or its an intended behavior?

  • 1
    Normal behavior. You need to learn to commit ALL your work...or none of it. See ACID Compliant for more info. – Michael Kutz Feb 8 at 12:23
  • @MichaelKutz no my concern is when I tried to rollback the data is still committed. – zer09 Feb 9 at 4:59
  • 1
    Thsts because you called commit – Michael Kutz Feb 9 at 11:18
  • I edited the question few hours ago, and set if var != 0 then rollback and exit the stored procedure that wont execute the commit. – zer09 Feb 9 at 13:03
  • Do you get the same thing if you avoid using stored procs? – Rick James Feb 13 at 3:04
1

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

-- https://dev.mysql.com/doc/refman/8.0/en/commit.html

and

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

-- https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

That is, proc1's START closed proc2's START.

The first ROLLBACK closes the only open transaction, hence the second ROLLBACK has nothing to "roll back".

|improve this answer|||||
  • I started over and found documentation. – Rick James Feb 13 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.