解读SQL一些语句执行后出现异常不会回滚的问题

在 SQL 中，一些语句执行后出现异常是非常常见的问题。特别是在大型应用程序中，由于 SQL 查询和更新是不可避免的，所以在这些场景中出现问题更为普遍。在处理 SQL 执行异常时，回滚操作是个非常核心的概念。它可以确保当一些未知的错误导致 SQL 执行失败时，系统能够恢复到操作前的状态。然而，在某些情况下，SQL 语句的异常不会触发回滚操作。在以下内容中，我们将会详细讲解解决这个问题需要采取的步骤。

了解事务的概念

在理解 SQL 异常不会回滚的问题之前，我们需要了解事务的概念。当 SQL 语句执行失败时，事务可以确保对数据库的更改操作不会在系统中失去同步。简而言之，事务是一组 SQL 操作，这些操作要么全部执行，要么全部回滚。因此，如果一组 SQL 语句的某个部分失败，则整个事务将回滚到其起始状态。

理解 Autocommit 模式

在某些情况下，SQL 执行异常不会回滚的问题是由 Autocommit 模式引起的。在此模式下，每个 SQL 语句都会被看作一个独立的事务，而不是一组事务。这意味着，当 SQL 语句失败时，整个事务并不会回滚，也就是说，未提交的更改也不会被还原。

解决 Autocommit 模式下的异常问题

如果在 Autocommit 模式下遇到异常问题，可以采取以下步骤来解决：

1. 将 Autocommit 模式关闭。这样可以确保 SQL 语句被看作一个整体事务，而不是独立的语句执行，当语句出现异常时整个事务会回滚。

SET AUTOCOMMIT=0;

1. 在 SQL 语句执行前手动开启一个事务。

START TRANSACTION;

1. 如果 SQL 语句执行成功，则将更改提交到数据库。

COMMIT;

而当 SQL 执行时出现异常时，则会回滚到事务的初始状态。

ROLLBACK;

这些语句确保 Autocommit 模式关闭，将一组 SQL 语句看作整体事务，并手动提交或回滚事务以确保更改操作的同步性。

示例说明

以下是两个使用 SQL 查询和更新操作的示例，它们可以帮助你更好地理解 Autocommit 模式下 SQL 执行异常问题不会回滚的原因及解决方法。

示例一

在以下示例中，我们创建一个示例表 employee，该表有两个字段 id 和 name。然后我们插入一组数据并将 name 字段中多余的字符清除，更新操作时将会出现异常，进而演示异常不会触发回滚的情况。

CREATE TABLE employee (id INT NOT NULL, name VARCHAR(30) NOT NULL);
INSERT INTO employee (id, name) VALUES (1, "Jupyter Sr."), (2, "Jupyter Jr.");

UPDATE employee SET name = SUBSTR(name, 1, 7) WHERE id = 2;
UPDATE employee SET name = SUBSTR(name, 1, 7) WHERE id = 3;

可以看到，在第三个 SQL 语句中，我们在查询 employee 表中 id 为 3 的记录，而该记录并不存在。这时就会引发异常，但并没有触发回滚，其他数据仍然被更新成了 SUBSTR(name, 1, 7) 的值。

示例二

在以下示例中，我们创建一个 bank 表，并将其设置为 InnoDB 引擎。我们将其插入一组数据，并利用 HANDLER 语句锁住 bank 表所有记录，然后我们发起另一个并发的 SQL 事务，使用 UPDATE 更新 bank 表中所有记录，此时会造成死锁的情况，我们演示死锁异常不触发回滚的问题。

首先我们创建表和插入数据：

CREATE TABLE bank (
    account_id INT(11) NOT NULL,
    balance INT(11) DEFAULT 0,
    PRIMARY KEY (account_id)
)ENGINE=InnoDB;

INSERT INTO bank (account_id, balance)
VALUES (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);

然后我们使用 HANDLER 语句锁住 bank 表中所有的账户记录：

HANDLER bank OPEN;
HANDLER bank READ FIRST;
REPEAT
    HANDLER bank READ NEXT;
UNTIL HANDLER_READ_STATUS() <> 0 END REPEAT;

在另一个会话中，我们发起 SQL UPDATE 语句更新 bank 表中所有账户余额：

UPDATE bank SET balance = balance + 100;

由于 bank 表中的所有账户记录都被上面的锁定语句加锁，而 UPDATE 语句需要锁定所有行，因此就发生了死锁的情况。这时，该 SQL 会引发异常并未触发回滚操作，仍然会造成死锁的问题。

为了解决这个问题，我们可以在事务中捕获该死锁异常并对其进行回滚：

START TRANSACTION;
UPDATE bank SET balance = balance + 100;
COMMIT;

因为在这里我们手动启动了一个事务，并且 Autocommit 模式已关闭，所以即使 UPDATE 语句出现了异常，这个事务也会回滚并且所有的更改都会撤销。