2

If I'm in a transaction, will calling a stored procedure implicitly commit the current transaction and start a new one?

1

Let's find out:

In session 1:

DELIMITER ;;
CREATE PROCEDURE p1() BEGIN SELECT 1; END;;
DELIMITER ;

CREATE TABLE t1 (id int unsigned auto_increment PRIMARY KEY);
START TRANSACTION;
INSERT INTO t1() VALUES ();
CALL p1();

In session 2:

SELECT * FROM t1;

which gives:

Empty set (0.000 sec)

If the procedure call had caused the current transaction to be committed in order to start a new one, then we would have expected to see a row from the above SELECT.

So, back in session 1:

COMMIT;

And then session 2, run the SELECT again:

SELECT * FROM t1;

which now gives the expected result:

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.001 sec)

Conclusion: No, calling a stored procedure does not commit the current transaction and does not start a new transaction.

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.