Início / dba / Perguntas / 280695
Accepted
Hannah Vernon
Asked: 2020-12-02 11:56:16 +0800 CST

Transação não revertida no MySQL

  • 772

Considere a seguinte configuração, que você pode mexer em dbfiddle.uk :

No primeiro lote, configuramos três tabelas simples e adicionamos uma linha a cada tabela:

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);

Em seguida, criaremos três procedimentos armazenados:

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
///

Em seguida, iniciaremos uma transação, executaremos os três procs e, em seguida, reverteremos a transação. Observe que estamos desabilitando o autocommit explicitamente, embora isso não seja estritamente necessário, pois temos uma transação explícita:

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

Agora, se inspecionarmos o conteúdo das três tabelas, notamos que a reversão parece não ter ocorrido. Na verdade, as linhas são exatamente como se não houvesse nenhuma transação, ou seja, parece que o autocommit estava ativado.

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

Os resultados:

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

O que está acontecendo aqui e como posso reverter as ações dos dois primeiros procs quando o terceiro claramente falha?

Para sua informação, isso foi testado no MySQL 5.7.2 e via DBFiddle.uk no MySQL 8.0.22.

mysql transaction

1 respostas

  1. Best Answer

    Parece que o MySQL está falhando silenciosamente na chamada para proc3()e, portanto, nunca executando a instrução de reversão.

    É assim que você deve construir o código para realmente realizar a reversão, passando alguns detalhes de volta para o chamador. Você pode testar este código em 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);

    Criando os três procs originais:

    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 ;

    Crie um novo proc para chamar os três procs acima:

    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 ;

    Chame o novo proc e verifique os resultados:

    CALL call_procs();

SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

    A saída agora parece ser o que você esperaria:

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

    E um pequeno bônus, você obtém a seguinte saída alertando-o para um problema:

    ╔═══════════════════════════════════╗
║ @full_error ║
╠═══════════════════════════════════╣
║ ERRO 1365 (22012): Divisão por 0 ║
╚═══════════════════════════════════╝
    • 3

relate perguntas