Do DMLs in a stored procedure run in a single transaction?

Question

My MySQL 5.5 server has set autocommit=1.

My stored procedure has several DMLs but without explicit transaction management.

When I issue call the_procedure() from MySQL CLI (autocommit is still 1), do all the procedure's DMLs run in one transaction?

Or do they run in separate transactions, and causing implicit transaction commit after every DML (due to autocommit)?

Unless they've been willfully perverse, MySQL should probably behave the same as most other SQL databases - transactions and stored routines are orthogonal concepts - a single transaction can span multiple routines, a single routine can create and commit multiple transactions. — Damien_The_Unbeliever, Sep 25 '13 at 06:17

MK. · Answer 1 · 2022-05-05T13:42:55.160

2

This is surprising to me but:

Although MySQL will automatically initiate a transaction on your behalf when you issue DML statements, you should issue an explicit START TRANSACTION statement in your program to mark the beginning of your transaction.

It's possible that your stored program might be run within a server in which autocommit is set to TRUE, and by issuing an explicit START TRANSACTION statement you ensure that autocommit does not remain enabled during your transaction. START TRANSACTION also aids readability by clearly delineating the scope of your transactional code.

edited May 05 '22 at 13:42

answered Sep 25 '13 at 02:46

MK.

33,605
18
74
111

Thank you, does it suggest that DMLs in a procedure call run in one transaction? – Sep 25 '13 at 03:27
1

O'reilly has renamed Safari Books Online to O'Reilly Learning, the link is now https://learning.oreilly.com/library/view/mysql-stored-procedure/0596100892/re31.html – AnotherParker May 04 '22 at 04:12

score 0 · Answer 2 · answered Sep 25 '13 at 10:11

0

Tests done in the following SQL Fiddle, shows that by not explicitly handle transactions are handled separately when the variable autocommit is 1 (TRUE).

answered Sep 25 '13 at 10:11

wchiquito

16,177
2
34
45

1

Thanks for that, but my question is more about whether there is an implicit commit for each individual DMLs in a procedure, not a procedure call as a whole. – Sep 27 '13 at 04:06

score 0 · Accepted Answer · answered Sep 27 '13 at 10:47

They run in separate transactions if autocommit=1. Suppose you define

CREATE TABLE test ( id int PRIMARY KEY )//

CREATE PROCEDURE sp_test_trans()
BEGIN
  INSERT INTO test (id) VALUES (1);
  INSERT INTO test (id) VALUES (2);
  ROLLBACK;
END//

If you run this procedure with autocommit=0, the ROLLBACK will undo the insertions. If you run it with autocommit=1, the ROLLBACK will do nothing. Fiddle here.

Another example:

CREATE PROCEDURE sp_test_trans_2()
BEGIN
  INSERT INTO test (id) VALUES (1);
  INSERT INTO test (id) VALUES (1);
END//

If you run this procedure with autocommit=0, failure of the second insert will cause a ROLLBACK undoing the first insertion. If you run it with autocommit=1, the second insert will fail but the effects of the first insert will not be undone.

Do DMLs in a stored procedure run in a single transaction?

3 Answers3