【MySQL技术内幕】7.8-不好的事物习惯和长事务

1.不好的事务习惯

1.1在循环中提交

开发人员非常喜欢在循环中进行事务的提交,下面是他们可能常写的一个存储过程:

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;复制代码

其实,在上述的例子中,是否加上提交命令 COMMIT并不关键。因为 InnoDB存储引擎默认为自动提交,所以在上述的存储过程中去掉 COMMIT,结果其实是完全一样的。这也是另一个容易被开发人员忽视的问题:

CREATE PROCEDURE load2(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;
SET s =s+1;
END WHILE;
END;复制代码

不论上面哪个存储过程都存在一个问题,当发生错误时,数据库会停留在一个未知的位置。例如,用户需要插入10000条记录,但是在插入5000条时,发生了错误,这时前5000条记录已经存放在数据库中,那应该怎么处理呢?另一个问题是性能问题,上面两个存储过程都不会比下面的存储过程load3快,因为下面的存储过程将所有的INSERT都放在一个事务中:

CREATE PROCEDURE load3(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;复制代码

比较这3个存储过程的执行时间:

显然,第三种方法要快得多!这是因为每一次提交都要写一次重做日志,存储过程load1和load2实际写了10000次重做日志文件,而对于存储过程load3来说,实际只写了1次。

大多数程序员会使用第一种或第二种方法,有人可能不知道 InnoDB存储引擎自动提交的情况,另外有些人可能持有以下两种观点:首先,在他们曾经使用过的数据库中,对事务的要求总是尽快地进行释放,不能有长时间的事务;其次,他们可能担心存在 Oracle数据库中由于没有足够undo产生的 Snapshot Too old的经典问题。 MySQL的InnoDB存储引擎没有上述两个问题,因此程序员不论从何种角度出发,都不应该在一个循环中反复进行提交操作,不论是显式的提交还是隐式的提交。

1.2使用自动提交

自动提交并不是一个好的习惯,因为这会使初级DBA容易犯错,另外还可能使一些开发人员产生错误的理解,如我们在前一小节中提到的循环提交问题。 MySQL数据库默认设置使用自动提交(autocommit),可以使用如下语句来改变当前自动提交的方式:

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)复制代码

也可以使用 START TRANSACTION,BEGN来显式地开启一个事务。在显式开启事务后,在默认设置下(即参数completion_type等于0), MySQL会自动地执行SET AUTOCOMMIT=0的命令,并在 COMMIT或 ROLLBACK结束一个事务后执行SET AUTOCOMMIT=1。

另外,对于不同语言的API,自动提交是不同的。 MySQL C API默认的提交方式是自动提交,而 MySQL Python API则会自动执行 SET AUTOCOMMIT=0,以禁用自动提交。因此在选用不同的语言来编写数据库应用程序前,应该对连接 MySQL的API做好研究。

我认为,在编写应用程序开发时,最好把事务的控制权限交给开发人员,即在程序端进行事务的开始和结束。同时,开发人员必须了解自动提交可能带来的问题。我曾经见过很多开发人员没有意识到自动提交这个特性,等到出现错误时应用就会遇到大麻烦

1.3使用自动回滚

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

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时会发生错误,但是因为启用了自动回滚的操作,因此这个存储过程的执行结果如下:

mysql>CALL sp_auto_rollback_demo;
Query OK,0 rows affected (0.06 sec)
mysql>SELECT * FROM b;
Empty set (0.00 sec)复制代码

看起来运行没有问题,非常正常。但是,执行sp_auto_rollback_demo这个存储过程的结果到底是正确的还是错误的?对于同样的存储过程sp_auto_rollback_demo,为了得到执行正确与否的结果,开发人员可能会进行这样的处理:

CREATE PROCEDURE sp_auto_rollback_demo (
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN 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。因此这次运行的结果就会变成:

看起来用户可以得到运行是否准确的信息。但问题还没有最终解决,对于开发人员来说,重要的不仅是知道发生了错误,而是发生了什么样的错误。因此自动回滚存在这样的一个问题。

习惯使用自动回滚的人大多是以前使用 Microsoft SQL Server数据库的开发人员。在Microsoft SQL Server数据库中,可以使用 SET XABORT ON来自动回滚一个事务。但是 Microsoft SQL Server数据库不仅会自动回滚当前的事务,还会抛出异常,开发人员可以捕获到这个异常。因此, Microsoft SQL Server数据库和 MySQL数据库在这方面是有所不同的。

就像之前小节中所讲到的,对事务的 BEGIN、 COMMIT和 ROLLBACK操作应该交给程序端来完成,存储过程需要完成的只是一个逻辑的操作,即对逻辑进行封装。

2.长事务

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

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

这时这个事务可能需要非常长的时间来完成。可能需要1个小时,也可能需要4、5个小时,这取决于数据库的硬件配置。DBA和开发人员本身能做的事情非常少。然而,由于事务ACID的特性,这个操作被封装在一个事务中完成。这就产生了一个问题,在执行过程中,当数据库或操作系统、硬件等发生问题时,重新开始事务的代价变得不可接受。

数据库需要回滚所有已经发生的变化,而这个过程可能比产生这些变化的时间还要长。因此,对于长事务的问题,有时可以通过转化为小批量(mini batch)的事务来进行处理。当事务发生错误时,只需要回滚一部分数据,然后接着上次已完成的事务继续进行。

例如,对于前面讨论的银行利息计算问题,我们可以通过分解为小批量事务来完成。