MySQL 事务提交 --不良好的事务习惯。

悦光阴 2020-12-13

展开全文

MySQL 事务提交 --不良好的事务习惯

　　我们知道"事务"是数据库区别于文件系统的重要特性之一。MySQL的InnoDB引擎中的事务也完全符合ACID(原子性一致性隔离性持久性)的特性。事务以及事务提交等一些内容不可避免的会出现在我们的日常工作当中。这篇文章我们就来简单的聊聊一些不良好的事务习惯。

循环中提交事务

　　我们先来比较两个存储过程

CREATE PROCEDURE load1 (count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END;

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80)
START TRANSACTION;
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
COMMIT;
END;

　　因为InnoDB存储引擎默认是自动提交的，所以load1中如果去掉Commit语句，结果也是一样的。单纯比较两个存储过程我们就可以知道，load1中存在一个问题，例如当用户需要插入10000条记录，但是在插入第5000条时，发生了错误，这时前5000条记录已经存放在数据库中。而load2中整体都是放在一个事务中，所以不会出现这个问题。除此之外，这两个存储过程的性能也存在差别，load2要比load1快出许多，这是因为每一次提交都要写一次redo log，存储过程load1实际上写了10000次重做日志，而对于存储过程load2来说，实际只写了1次redo log。因此执行时间会有巨大的差距。

　　在别的一些数据库中，可能总是要求对事务尽快地释放，不能有长时间的事务；其次，可能担心存在Oracle数据库中由于没有足够undo产生的Snapshot Too Old的经典问题。MySQL的InnoDB引擎没有上述的问题，因此无论是何种角度，都不应该在一个循环中反复进行提交操作，不论是显式的还是隐式的提交。

使用自动提交

　　自动提交不是一个好的习惯，因为这可能使开发人员产生错误的理解。我们可以使用如下语句来改变当前自动提交的方式：

SET autocommit=0;

　　也可以使用START TRANSACTION, BEGIN来显式地开启一个事务。在显式开启事务后，在默认设置下（即参数completion_type等于0），MySQL会自动地执行SET AUTOCOMMIT=0的命令，并在COMMIT或ROLLBACK结束一个事务后执行SET AUTOCOMMIT=1。此外对于不同语言的API，自动提交是不同的，因此在选用不同的语言来编写数据库应用程序前，应该对连接MySQL的API做好研究。

使用自动回滚

　　InnoDB存储引擎支持通过定义一个HANDLER来进行自动事务的回滚操作，如在一个存储过程中发生了错误会自动对其进行回滚操作。因此我发现很多开发人员喜欢在应用程序的存储过程中使用自动回滚操作，例如下面所示的一个存储过程：

CREATE TABLE 'b' (
'a' int(11) NOT NULL DEFAULT '0',
PRIMARY KEY ('a')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
END;

　　存储过程sp_auto_rollback_demo首先定义了一个exit类型的HANDLER,当捕获到错误时进行回滚。因此插入第二个记录1时会发生错误，但是因为启用了自动回滚操作，因此这个存储过程的执行结果是没有问题的，看起来非常正常。但我们并不能看出这个存储过程的结果到底是正确还是错误的。为了得到执行正确与否的结果，开发人员可能会进行这样的处理：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; SELECT -1; END;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
SELECT 1;
END;

　　这样如果发生错误，先回滚然后返回-1，表示运行有错误。运行正常返回值1。但是其实问题还是没有解决，对开发人员来说，重要的不仅是知道是否发生了错误，还要知道发生了什么错误。因此自动回滚存在这样一个问题。这里其实有一个建议是：对事务的BEGIN,COMMIT和ROLLBACK操作应该交给程序端来完成，存储过程需要完成的只是一个逻辑操作，即对逻辑进行封装。在程序中控制事务的好处是，用户可以得知发生错误的原因。

长事务

　　顾名思义，长事务是执行时间较长的事务。比如，对于银行系统的数据库，每过一个阶段可能需要更新对应账户的利息。如果对应账号的数量非常大，例如对有1亿用户的表account，需要执行下列语句：

UPDATE account
SET account_total = account_total + (1 + interest_rate)

　　这个事务可能需要非常长的时间来完成。由于事务ACID的特性，这个操作被封装在一个事务中完成，这就产生了一个问题，在执行过程中，当数据库或操作系统，硬件等发生问题时，重新开始事务的代价变得不可接受。数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时候可以通过转化为小批量的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已经完成的事务继续进行。

小结

　　事务是数据库区别于文件系统的一个关键特性。事务必须遵循ACID特性，即Atomicity(原子性), Consistency(一致性), Isolation(隔离性) 和 Durability(持久性)。隔离性通过锁来完成，原子性，一致性，隔离性通过redo和undo来完成。在默认配置下，MySQL InnoDB总是自动提交的，如果不知道这点，可能会带来不好的结果。此外，在应用程序中，最好的做法是把事务的START TRANSACTION, COMMIT, ROLLBACK操作交给程序段来完成，而不是在存储过程中完成。