What happens by default if an error occurs during a transaction in a stored procedure?

Question

Let's say you have a stored procedure that looks something like this:

CREATE OR REPLACE
PROCEDURE usp_do_something (
    IN param1 INT,
    IN param2 INT,
    IN param3 INT
)
MODIFIES SQL DATA
BEGIN
    START TRANSACTION;

    INSERT `table1` ( `ID` ) VALUES (param1);
    INSERT `table2` ( `ID` ) VALUES (param2);
    INSERT `table3` ( `ID` ) VALUES (param3);

    COMMIT;
END;

And then the stored procedure is called with a set of parameter values which cause one of the INSERT operations to fail, raising an SQLEXCEPTION.

There is no explicit ROLLBACK command in this example. Testing shows that the transaction is not committed when the SQLEXCEPTION occurs before the COMMIT; statement. Is the transaction implicitly rolled back when the stored procedure ends? Or will the transaction be left hanging until some kind of timeout occurs? Could this cause the stored proc to hold locks for some time after it's failed?

I know that I can explicitly trigger ROLLBACK on failure using DECLARE HANDLER as described in this question but I can't find any documentation that describes what MariaDB does if a stored-procedure transaction fails before reaching a COMMIT statement without any explicit ROLLBACK.

Is there a MariaDB/MySQL log or transaction state table that I could look at to see what the server is doing with this transaction?

Answer 1

When a stored procedure fails execution is aborted, but the transaction IS NOT FINALIZED.

See an example:

CREATE TABLE table1 (ID INT CHECK (id < 100));
CREATE TABLE table2 LIKE table1;
CREATE TABLE table3 LIKE table1;

CREATE PROCEDURE usp_do_something (
    IN param1 INT,
    IN param2 INT,
    IN param3 INT
)
MODIFIES SQL DATA
BEGIN
    START TRANSACTION;

    INSERT `table1` ( `ID` ) VALUES (param1);
    INSERT `table2` ( `ID` ) VALUES (param2);
    INSERT `table3` ( `ID` ) VALUES (param3);

    COMMIT;
END;

Insert data without errors. All data is inserted.

CALL usp_do_something (10, 20, 30);

SELECT *, '1' tablenum FROM table1 UNION ALL
SELECT *, '2' tablenum FROM table2 UNION ALL
SELECT *, '3' tablenum FROM table3 ORDER BY ID;

ID | tablenum
-: | :-------
10 | 1       
20 | 2       
30 | 3       

Insert data with error in 2nd statement. All data before error (40) is inserted.

CALL usp_do_something (40, 500, 60);

Check constraint 'table2_chk_1' is violated.

SELECT *, '1' tablenum FROM table1 UNION ALL
SELECT *, '2' tablenum FROM table2 UNION ALL
SELECT *, '3' tablenum FROM table3 ORDER BY ID;

ID | tablenum
-: | :-------
10 | 1       
20 | 2       
30 | 3       
40 | 1       

Insert data with error in 2nd statement. All data before error (70) is inserted. This last call may be rollbacked due to non-committed transaction. But the data inserted in previous block (40) is committed implicitly by the transaction start and cannot be rollbacked.

CALL usp_do_something (70, 800, 90);

Check constraint 'table2_chk_1' is violated.

SELECT *, '1' tablenum FROM table1 UNION ALL
SELECT *, '2' tablenum FROM table2 UNION ALL
SELECT *, '3' tablenum FROM table3 ORDER BY ID;

ROLLBACK;

SELECT *, '1' tablenum FROM table1 UNION ALL
SELECT *, '2' tablenum FROM table2 UNION ALL
SELECT *, '3' tablenum FROM table3 ORDER BY ID;

ID | tablenum
-: | :-------
10 | 1       
20 | 2       
30 | 3       
40 | 1       
70 | 1       

✓

ID | tablenum
-: | :-------
10 | 1       
20 | 2       
30 | 3       
40 | 1       

db<>fiddle here