How do transactions within stored procedures affect each other? Is it possible to use this structure or will the second procedure interrupt the transaction of the first one?
CREATE PROCEDURE some_procedure() begin ... CALL some_procedure1(); CALL some_procedure2(); ... end
CREATE PROCEDURE some_procedure1() begin START TRANSACTION; ... COMMIT; end
CREATE PROCEDURE some_procedure2() begin START TRANSACTION; ... COMMIT; end
There’s only one thread for a given MySQL session, so the procedures run serially.
some_procedure1() commits its transaction before it returns, and that must finish before
some_procedure2() is called.