mysql长事务查询_智库 | 数据库长事务详解

原标题：智库 | 数据库长事务详解

在数据库的日常维护中，我们常常会听说数据库“长事务”这个词，那么何为长事务？长事务是如何产生的？长事务对数据库有什么影响？如何防止长事务的产生？本文就根据笔者的运维经验谈谈对数据库长事务的理解。

1．什么是事务

事务是关系型数据库中的一个逻辑工作单元，它由一个或多个SQL语句组成，这些语句要么全都执行，要么全都不执行，执行前和执行后必须有一致的状态，语句未执行完成之前对其它事务不可见，语句执行完成后所做的改变是永久的，即满足ACID特性。事务的ACID特性确保了数据库并发数据处理的正确性和灾难恢复时数据的完整性，这是它有别于文件系统的重要特征。

2．什么是长事务

长事务，顾名思义就是一个事务执行了很长时间仍未结束。那么一个事务执行多长时间算是长事务？这在不同的数据库产品中有不同的定义，对于informix数据库来说占用逻辑日志个数的百分比达到长事务高水位线就被定义为一个长事务，而在oracle中一个事务执行时间超过6秒钟就被认为是长事务。一般来说在一个联机事务处理系统中事务执行时间应该控制在一定范围内，如果一个事务执行时间太长，会长时间锁定某些资源，不利于并发处理，有时还会因为某些资源耗尽导致数据库挂起。

3．事务的控制

事务有开始和结束，以“begin”作为事务的开始，以“commit”或“rollback”结束，“commit”表明事务执行成功，对数据库所做的修改已经生效，“rollback”则说明事务执行失败，对数据库的所有操作均需要撤销，恢复到事务执行之前的状态。为了使事务能被撤销，数据库管理系统必须对所有操作及被修改前的数据进行记录，以便撤销时能够执行逆向操作，将数据恢复到以前的状态。在具体的实现中，不同的数据库有不同的方法，下面以informix和oracle为例进行分析。

Informix使用记录日志方式保存事务操作及数据，称为物理日志和逻辑日志。

物理日志是一块连续的磁盘空间，可以循环使用，用来存放上一次检查点之后第一次被修改的数据块的前映像，数据在下一次检查点完成时被逻辑清空，所使用存储空间被释放。当物理日志使用率达到75%时就会触发检查点，物理日志即被清空。因此，物理日志并不能用于事务回滚，只能用于实例恢复，当数据库非正常关闭时，使用物理日志可以将数据库快速恢复到最近一次检查点的状态。

逻辑日志由三个或多个日志文件组成，采用循环写入的方式，当一个逻辑日志写满时切换到下一个未被使用的日志继续写。逻辑日志不仅记录事务开始、提交、回滚、检查点、DDL、DML等操作，还同时记录被修改数据的前后映像，而且如果一个日志包含未提交的事务就不能被释放，故逻辑日志被应用在事务回滚和事务恢复中。

物理日志的释放只与检查点有关，检查点一旦完成，物理日志中内容就不再需要了，就可以释放，但是逻辑日志空间只有同时满足以下条件时才能被释放：不包含活动事务、不包含最后一次检查点、日志已被备份，以便保留足够的信息用于事务回滚和恢复。这样就带来一个问题，如果一个事务占据了全部逻辑日志，数据库就会进入阻塞状态，甚至连回滚操作也不能进行，因为回滚也需要写日志。为了防止数据库因日志资源耗尽发生阻塞，informix设置了长事务高水位线(LTXHWM)和独占的长事务高水位线(LTXEHWM)，当一个事务占用日志个数的百分比达到长事务高水位线就被标识为一个长事务，并自动触发回滚操作，当百分比达到独占的长事务高水位线时，其它会话均进入阻塞状态，只进行长事务回滚操作。

Informix的事务控制机制严重依赖于逻辑日志，在逻辑日志中既要写操作又要写数据，导致逻辑日志非常繁忙，容易引发故障。我们再来看看另外一种广泛使用的数据库oracle，似乎很少听到关于长事务的讨论，那它是如何来进行事务控制的呢？

oracle也是使用类似机制来实现事务控制，称为撤销段(undo segment，9i版本以前使用回滚段，需手工管理)和重做日志文件(redo log file)。

撤销段由系统自动管理，在undo表空间中分配，一般会分配一个系统段和多个非系统段，系统根据需要自动进行扩展和收缩，可以循环使用。撤销段中存储了事务信息及被更新数据的前值，当事务回滚时利用这些数据就可以将数据恢复到事务开始前的状态，这就要求在事务未提交之前撤销段中的数据不能被覆盖，有时为了满足查询一致性要求可能保留更长时间。

重做日志文件以组的形式存在，每个实例至少有两组，以便能在写满之后进行切换，每个组至少包含一个成员。重做日志文件中记录了对数据库所做的全部操作，通过重放日志可实现实例恢复。在实例恢复过程中，通过前滚重做日志，将最近一次检查点以来已提交的事务重做一次，如果遇到未提交的事务则进行入回滚过程，此时需要撤销段的参与。重做日志文件只要不包含最近一次检查点以后的数据(inactive状态)且归档完成(归档模式下)后就可以被重写，与事务是否提交无关。

与informix相比，oracle将用于回滚的数据与事务操作分离开来，缓解了重做日志文件写入的压力，也不用担心重做日志文件被覆盖的问题，使用专用的撤销段来管理回滚数据，就算undo表空间耗尽引发事务回滚，也不会导致数据库挂起，因为事务回滚时只写重做日志，不写撤销段。

4．长事务产生的几种原因

在informix中，长事务现象时有发生，主要有以下原因：

1) 逻辑日志参数设置不合理，日志个数太少或长事务高水位线太低；

2) 数据库并发很高，某些事务的粒度太粗，事务长时间不能提交，最终触及长事务高水位线；

3) 事务启动后，未使用commit或rollback来终止事务；

4) 大表连接插入目标表，连接条件不正确产生笛卡尔集，结果集超出预期，长时间不能处理完成；

5) 使用临时表装载大量数据，未指定withno log子句，也未设置TEMPTAB_NOLOG参数。

5．如何避免长事务

对于oracle来说，长事务可能会导致“ORA-30036”错误，即undo表空间已被用完，无法扩展撤销段，但只要将undo表空间设置为自动扩展并确保磁盘空间足够就不会产生此问题。而informix长事务带来的后果是很严重的，虽然在长事务发生时可以通过手工添加逻辑日志方式来挽救，但是及时性难以保证。不过长事务也是可以避免的，只要合理地设置数据库参数，合理的编写应用，长事务发生的概率会大大降低。具体来说可以从以下几点做起：

1) 根据数据库并发用户数量及更新数据量设置逻辑日志大小和个数，应保证在高并发情况下大部分事务能正常完成；

2) 适当调低长事务高水位线，确保在长事务发生时有足够的日志文件用于回滚；

3) 设置参数DYNAMIC_LOGS为2，当数据库检测到第n+1(n为当前正在写的日志)个日志不可用就会自动添加一个新的日志文件，需要注意的是日志不是在事务到达高水位线之后就增加，而是在无可用日志文件时才增加，因此若不希望事务回滚过程中数据库进入阻塞状态，还须手工添加日志；

4) 合理控制事务的颗粒度，将大事务分割为小事务进行处理，减少单个事务处理时间，提高逻辑日志资源利用率；

5) 事务操作应闭合，事务开始之后必须结束，尽量避免回滚操作，减少对系统资源的消耗；

6) 大表进行连接时，尽量保证连接条件唯一，避免产生笛卡尔集，目标表可使用裸表，避免产生事务日志；

7) 对于临时表的使用应谨慎，在创建临时表应养成写with no log字句的习惯，或将数据库参数TEMPTAB_NOLOG指定为1，这样就算创建临时表没有with no log语句，数据库也会默认使用临时表空间，不会记录事务日志；

8) 做好数据库事务、逻辑日志、表空间等指标监控，一旦发现相关指标超出阀值，立即采取措施进行处理。返回搜狐，查看更多

责任编辑：