'Do DMLs in a stored procedure run in a single transaction?
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)?
Solution 1:[1]
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.
Solution 2:[2]
Tests done in the following SQL Fiddle, shows that by not explicitly handle transactions are handled separately when the variable autocommit is 1 (TRUE).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | wchiquito |