Day11-解决MySQL_ERROR参考指南（事务）-同乐学堂

2.3　事务

MySQL在存储引擎级别提供事务支持。在官方提供的存储引擎中，最受欢迎的InnoDB引擎就提供了事务的支持。本书将会讨论如何解决InnoDB事务问题。在MySQL中，可以通过START TRANSACTION或BEGIN语句启动事务；通过COMMIT语句提交事务；通过ROOLBACK语句回滚事务（取消事务）。另一种启动多语句事务的方法是将autocommit变量的值设置为0。这将覆盖MySQL的默认行为，即在每条语句后发送一个隐式提交指令。当autocommit设置为0之后，需要显式调用COMMIT或ROLLBACK命令。在那之后，下一条语句会自动开始新事务。MySQL也提供了SAVAPOINT和XA事务接口。尽管InnoDB引擎支持两者，但是本书不打算介绍它们，因为这不会给我们带来任何解决问题的额外技术。换句话说，我介绍的相同技术也适用于这类事务。

2.3.1　隐藏查询

InnoDB存储引擎把对数据的每个请求都作为事务进行处理。无论事务是单语句或者多语句均可。对于排错来说，可以像2.2.2节中介绍的那样处理单个查询事务。你需要知道哪个是当前执行的查询以及哪个锁阻碍了其他查询。当你的事务是由很多语句组成的时候情况就不同了。在这种情况下，即使你在 SHOW PROCESSLIST的输出中看不到任何查询，事务也有可能锁住了行。为了举例说明这个问题，再次修改示例。现在我们甚至无须调用sleep命令来产生延迟。在运行这个示例前，我恢复了修改的行，因此该表中的值与初始测试设置中的值相同。

请注意，此时事务没有关闭。从另一个连接启动另一个事务：

现在，运行SHOW PROCESSLIST命令。该查询与行锁示例中的状态一样，都是Updating，不过这次不清楚是什么阻止了更新操作：

下面是从SHOW ENGINE INNODB STATUS命令的输出中筛选出的信息：

同样的方法对被LOCK TABLE查询锁住的MyISAM表也适用，因而这部分信息没有必要显示在SHOW PROCESSLIST命令的输出中。InnoDB会在其状态输出中会输出有关这些表的信息：

尽管上述信息没有明确指出谁持有锁，但是仍清楚地显示出该事务在等待锁。如果你使用的是5.0版本或者5.1版本中捆绑的InnoDB引擎，你有两种选择：自己找出原因，或者使用InnoDB锁监控器。在该示例里，仅有两个事务，所以很容易得到答案，也就是说“自己找到答案”是可行的。不过，如果你有很多连接在使用同一个表中的不同行，那就不会这么简单了。2.8.2节中将会介绍InnoDB锁监控器。这里采用第三种选择，仅适用于InnoDB插件的情况。

InnoDB插件丰富了监控功能，在INFORMATION_SCHEMA库中包含一些相关表，分别叫做INNODB_LOCKS、INNODB_LOCK_WAITS（保存已获取的锁和等待的锁的信息）和INNODB_TRX（保存正在执行的事务的信息）。

对于该示例，可以查询下面这些表：

这是关于锁的信息。两个事务都对同一条记录设置锁，不过从上述信息中无法得知哪个事务持有锁，哪个事务在等待锁。对于这部分细节信息，可以从INNODB_ LOCK_WAITS表中获得：

requesting_trx_id列的值就是我们“挂起”的事务的ID，blocking_trx_id列的值为持有锁的事务的ID，而requested_lock_id列和blocking_lock_id列的值分别代表被请求和阻塞的锁的ID信息。现在我们所需要知道的就是正在阻塞中的事务的MySQL进程的ID，因此我们需要想点办法。INNODB_TRX表可以帮助我们找到该ID：

正在阻塞的事务的ID是3B85。所以，输出中的第二行是有关该事务的行，从中可得知trx_mysql_thread_id为28544。可以通过SHOW PROCESSLIST命令来确认该信息：

既然我们已经知道了MySQL线程的ID，我们就可以对该阻塞的事务做任何想要的操作：继续等待其完成还是终止该事务均可。如果我们在应用程序中执行该命令，我们还可以分析是什么导致了此类锁定问题，并可以进行修正以避免今后再发生问题。实际上，INNODB_TRX表包含了很多关于事务的有用信息。如果回顾该示例，可以看到等待的事务的状态是trx_state：LOCK WAIT，而正在运行的事务的状态是trx_state：RUNNING。这里不会过多地讨论这部分信息，不过第6章会继续探讨它。

我们刚刚学习了一个未提交的事务会持有锁，哪怕该影响到特定行的查询在数小时前已经结束了。

你在编码时应牢记这些知识。我曾见过用户在环境中设置autocommit=0，从而导致事务运行数小时。这会导致很难发现和理解的问题，特别是当用户没有做好准备的时候。这种环境通常在流行的Java的框架中使用，即默认在URL中添加autocommit=0。

概括来说，当执行多语句事务的时候，应尽可能及时提交事务。哪怕事务不会修改任何行，也不要在事务最后的更新都已经完成的情况下仍不提交事务。

2.3.2　死锁

死锁是指当两个或多个竞争事务彼此等待对方释放锁，从而导致事务永远无法终止的情况。在行锁级别，死锁是无法100%避免的。

InnoDB引擎有内部的死锁探测器。当其发现有死锁的时候，它会回滚其中的一个事务，并会报告一个立即可见的错误。当设计应用程序的时候，你需要对此情况有所准备并合理地处理回滚。关于死锁的信息可通过SHOW ENGINE INNODB STATUS命令获取。我们将通过一个简单的死锁示例来说明这部分内容。初始化数据如下所示。

现在启动两个事务并且在每个事务中插入一行数据：

目前为止一切正常。两条语句都在自增的字段插入了一个值。现在我们尝试在第一个事务中修改一行记录：

在其等待的过程中，在第二个事务中修改该行记录：

该查询立即失败并返回出现死锁的错误消息。与此同时，第二查询正确完成：你刚刚看到了InnoDB的死锁探测器是如何工作的。为了弄清楚究竟发生了什么，我们再次检查SHOW ENGINE INNODB STATUS命令的输出信息：

该输出包含了许多最近一次死锁的信息以及为什么会发生死锁。你需要注意的是WAITING FOR THIS LOCK TO BE GRANTED相关的部分（该部分表明事务在等待哪个锁）以及HOLDS THE LOCK(S)部分（该部分表明阻塞事务的锁的信息）。该知识在实际应用中更为重要，比如，查询是由Web应用的用户交互产生的，而你无法精确定位在特定的时间执行的是哪个查询。

为了应对潜在的死锁，你需要像第 1 章介绍的一样，在应用程序中添加错误处理功能。如果你得到表明是死锁的错误消息并且已经产生回滚，就需要重启事务。

2.3.3　隐式提交

有些语句在没有显式调用COMMIT语句的时候也会提交事务。这种情况称为隐式提交，并且如果你没意识到你正在提交事务，这很有可能导致一致性问题。很多语句都会产生隐式提交。这里不一一列举，因为它们会随版本的更替而不同。一般来说，DDL语句与事务相关的语句和管理语句都会产生隐式提交，而那些操作数据的语句则不会产生隐式提交。意料之外的隐式提交的一个典型特征就是你会在表中发现非预期的数据，尽管你认为插入数据的语句应该回滚。下面是一个示例：

CREATE TABLE语句产生了隐式提交。所以，即使你以为两条插入语句都进行了回滚，t1表也包含值为100的行数据。第二个值为200的插入会如预期一样回滚。这个示例运行的前提是设置了autocommit=0，这样才会默认使用多语句事务。当讨论提交的时候，无须再强调autocommit的默认值是1，也就是默认情况下，当没有显式调用BEGIN或START TRANSACTION命令时不会使用多语句事务。当autocommit的值是1的时候，每条语句都会立即提交，也就是说，在前面的示例中，两行记录实际上都会写入表中：