Transaction not rolling back in MySQL

Question

Consider the following setup, which you can tinker with at dbfiddle.uk:

In the first batch we setup three simple tables, and add a row to each table:

CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Next, we'll create three stored procedures:

DELIMITER //

CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
///

Next we'll start a transaction, run the three procs, then rollback the transaction. Note we're disabling autocommit explicitly even though that is not strictly required since we have an explicit transaction:

SET autocommit = 0;
START TRANSACTION
CALL proc1();
CALL proc2();
CALL proc3();
ROLLBACK

Now, if we inspect the content of the three tables, we note that the rollback appears to have not taken place. In fact, the rows are exactly as if there was no transaction, i.e. it looks like autocommit was on.

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The results:

╔═══╗
║ x ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝

What is happening here, and how can I rollback the actions of the first two procs when the third clearly fails?

FYI, this has been tested on MySQL 5.7.2, and via DBFiddle.uk on MySQL 8.0.22.

Answer 1

It appears MySQL is silently failing at the call to proc3(), and therefore never running the rollback statement.

This is how you should construct the code to actually perform the rollback, passing some details back to the caller. You can test this code at dbfiddle.uk:

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Creating the original three procs:

DROP PROCEDURE IF EXISTS proc1;
DROP PROCEDURE IF EXISTS proc2;
DROP PROCEDURE IF EXISTS proc3;

DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
//
DELIMITER ;

Create a new proc to call the three procs above:

DELIMITER //

DROP PROCEDURE IF EXISTS call_procs;

CREATE PROCEDURE call_procs()
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
            @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        ROLLBACK;
        SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
        SELECT @full_error;
    END;
    SET autocommit = 0;
    START TRANSACTION;
    CALL proc1();
    CALL proc2();
    CALL proc3();
END
//
DELIMITER ;

Call the new proc, and check the results:

CALL call_procs();

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The output now appears to be what you'd expect:

╔═══╗
║ x ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝

And a nice little bonus, you get the following output alerting you to an issue:

╔═══════════════════════════════════╗
║            @full_error            ║
╠═══════════════════════════════════╣
║ ERROR 1365 (22012): Division by 0 ║
╚═══════════════════════════════════╝