7.3事务控制语句

在MySQL命令行的默认设置下， 事务都是自动提交(autocommit) 的， 即执行SQL语句后就会马上执行COMMIT操作。

因此要显式地开启一个事务需使用命令BEGIN、START TRANSACTION， 或者执行命令SET AUTOCOMMIT=0， 禁用当前会话的自动提交。

每个数据库厂商自动提交的设置都不相同， 每个DBA或开发人员需要非常明白这一点， 这对之后的SQL编程会有非凡的意义， 因此用户不能以之前的经验来判断MySQL数据库的运行方式。

在具体介绍其含义之前， 先来看看用户可以使用哪些事务控制语句。

  • START TRANSACTION BEGIN：显式地开启一个事务。

  • COMMIT：要想使用这个语句的最简形式， 只需发出COMMIT。也可以更详细一些， 写为COMMITWORK，不过这二者几乎是等价的。COMMIT会提交事务，并使得已对数据库做的所有修改成为永久性的。

  • ROLLBACK：要想使用这个语句的最简形式， 只需发出ROLLBACK。同样地，也可以写为ROLLBACKWORK， 但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

  • SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。

  • RELEASE SAVEPOINT identifier：删除一个事务的保存点， 当没有一个保存点执行这句语句时，会抛出一个异常。

  • ROLLBACK TO[SAVEPOINT] identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。例如可以发出两条UPDATE语句， 后面跟一个SAVEPOINT， 然后又是两条DELETE语句。如果执行DELETE语句期间出现了某种异常情况， 并且捕获到这个异常，同时发出了ROLLBACK TO SAVEPOINT命令， 事务就会回滚到指定的SAVEPOINT ， 撤销DELETE完成的所有工作， 而UPDATE语句完成的工作不受影响。

  • SET TRANSACTION：这个语句用来设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

START TRANSACTION、BEGIN语句都可以在MySQL命令行下显式地开启一个事务。

但是在存储过程中，MySQL数据库的分析器会自动将BEGIN识别为BEGIN…END， 因此在存储过程中只能使用STARTTRANSACTION语句来开启一个事务。

COMMIT和COMMIT WORK语句基本是一致的，都是用来提交事务。

不同之处在于COMMITWORK用来控制事务结束后的行为是CHAIN还是RELEASE的。

如果是CHAIN方式， 那么事务就变成了链事务。

用户可以通过参数completion_type来进行控制， 该参数默认为0， 表示没有任何操作。在这种设置下COMMIT和COMMITWORK是完全等价的。

当参数completion_type的值为1时， COMMIT WORK等同于COMMIT AND CHAIN， 表示马上自动开启一个相同隔离级别的事务。

在这个示例中我们设置completion_type为1， 第一次通过COMMIT WORK来插人1这个记录。

之后插入记录2时我们并没有用BEGIN (或者START TRANSACTION )来显式地开启一个事务，之后再插人一条重复的记录2就会抛出异常。接着执行ROLLBACK操作， 最后发现只有1这一个记录， 2并没有被插入。

因为completion_type为1时， COMMIT WORK会自动开启一个链事务， 第二条INSERT INTO t SELECT 2语句是在同一个事务内的，因此回滚后2这条记录并没有被插人表t中。

参数completion_type为2时， COMMIT WORK等同于COMMIT AND RELEASE。

7.4隐式提交的SQL语句

以下这些SQL语句会产生一个隐式的提交操作， 即执行完这些语句后， 会有一个隐式的COMMIT操作。

  • DDL语句：ALTERDATABASE…UPGRADEDATADIRECTORYNAME，ALTER EVENT ，ALTER PROCEDURE ，ALTERTABLE，ALTERVIEW，CREATE DATABASE ，CREATEEVENT， CREATE INDEX ， CREATEPROCEDURE ，CREATETABLE， CREATE TRIGGER ，CREATEVIEW，DROP DATABASE ，DROPEVENT，DROPINDEX， DROP PROCEDURE ，DROP TABLE ，DROP TRIGGER ，DROPVIEW，RENAMETABLE，TRUNCATETABLE。

  • 用来隐式地修改MySQL架构的操作：CREATEUSER、DROPUSER、GRANT、RENAMEUSER、REVOKE、SETPASSWORD。

  • 管理语句：ANALYZETABLE、CACHEINDEX、CHECKTABLE、LOADINDEXINTOCACHE、OPTIMIZETABLE、REPAIRTABLE。

注意我发现MicrosoftSQLServer的数据库管理员或开发人员往往忽视对于DDL语句的隐式提交操作，因为在MicrosoftSQLServer数据库中， 即使是DDL也是可以回滚的。

这和InnoDB存储引擎、Oracle这些数据库完全不同。

另外需要注意的是，TRUNCATE TABLE语句是DDL，因此虽然和对整张表执行 DELETE 结果是一样的，但是是不能回滚的。

7.5 对于事务操作的统计

由于InnoDB存储引擎是支持事务的， 因此InnoDB存储引擎的应用需要在考虑每秒请求数(QuestionPerSecond，QPS)的同时， 应该关注每秒事务处理的能力(TransactionPerSecond，TPS)。

计算TPS的方法是 (com_commit + com_rollback ) /time。

但是利用这种方法进行计算的前提是：所有的事务必须都是显式提交的，如果存在隐式地提交和回滚(默认autocommit=1)， 不会计算到com_commit和com rollback变量中。

7.6 事务的隔离级别

令人惊讶的是，大部分数据库系统都没有提供真正的隔离性，最初或许是因为系统实现者并没有真正理解这些问题。

如今这些问题已经弄清楚了，但是数据库实现者在正确性和性能之间做了妥协。

ISO和ANIS SQL标准制定了四种事务隔离级别的标准， 但是很少有数据库厂商遵循这些标准。

比如Oracle数据库就不支持READUNCOMMITTED和REPEATABLE READ的事务隔离级别。

SQL标准定义的四个隔离级别为：

  • READ UNCOMMITTED

  • READ COMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

READ UNCOMMITTED

READ UNCOMMITTED称为浏览访问(browseaccess)， 仅仅针对事务而言的。

READ COMMITTED称为游标稳定(cursorstability)。REPEATABLEREAD是2.9999°的隔离， 没有幻读的保护。SERIALIZABLE称为隔离， 或3°的隔离。SQL和SQL 2标准的默认事务隔离级别是SERIALIZABLE。

InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ ， 但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLEREAD事务隔离级别下， 使用Next-KeyLock锁的算法， 因此避免幻读的产生。

这与其他数据库系统(如Microsoft SQLServer数据库) 是不同的。所以说，InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求， 即达到SQL标准的SERIALIZABLE隔离级别。

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READCOMMITTED。

据了解，大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题， 但是根据Jim Gray在《Transaction Processing》一书中指出， 两者的开销几乎是一样的， 甚至SERIALIZABLE可能更优!!!

因此在InnoDB存储引擎中选择REPEATABLEREAD的事务隔离级别并不会有任何性能的损失。

同样地， 即使使用READCOMMITTED的隔离级别，用户也不会得到性能的大幅度提升。

在InnoDB存储引擎中， 可以使用以下命令来设置当前会话或全局的事务隔离级别：

SET[GLOBAL I SESSION] TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE

如果想在MySQL数据库启动时就设置事务的默认隔离级别， 那就需要修改MySQL的配置文件， 在[mysqld] 中添加如下行：

[mysqld]
transaction-isolation=  READ-COMMITTED

在SERIALIABLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCKINSHAREMODE， 即为每个读取操作加一个共享锁。

因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。这时，事务隔离级别SERIALIZABLE符合数据库理论上的要求， 即事务是well-formed的， 并且是two-phrased的。有兴趣的读者可进一步研究。

因为InnoDB存储引擎在REPEATABLE READ隔离级别下就可以达到3°的隔离，因此一般不在本地事务中使用SERIALIABLE的隔离级别。SERIALIABLE的事务隔离级别主要用于InnoDB存储引擎的分布式事务。

在READCOMMITTED的事务隔离级别下， 除了唯一性的约束检查及外键约束的检查需要gaplock，InnoDB存储引擎不会使用gaplock的锁算法。但是使用这个事务隔离级别需要注意一些问题。

首先， 在MySQL 5.1中，READCOMMITTED事务隔离级别默认只能工作在replication (复制) 二进制日志为ROW的格式下。如果二进制日志工作在默认的STATEMENT下， 则会出现如下的错误：

7.7 分布式事务

7.7.1 MySQL数据库分布式事务

InnoDB存储引擎提供了对XA事务的支持， 并通过XA事务来支持分布式事务的实现。

分布式事务指的是允许多个独立的事务资源(transactional resources) 参与到一个全局的事务中。

事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交， 要么都回滚，这对于事务原有的ACID要求又有了提高。

另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

XA事务允许不同数据库之间的分布式事务， 如一台服务器是MySQL数据库的， 另一台是Oracle数据库的， 又可能还有一台服务器是SQLServer数据库的， 只要参与在全局事务中的每个节点都支持XA事务。

分布式事务可能在银行系统的转账中比较常见，如用户David需要从上海转10000元到北京的用户Mariah的银行卡中：

Banke shanghai：
UPDATE account SET money -money-10000 WHERE user='David'；
#Banke Beijing
UPDATE account SET money=money + 10000 WHERE user='Mariah'；

在这种情况下，一定需要使用分布式事务来保证数据的安全。

如果发生的操作不能全部提交或回滚， 那么任何一个结点出现问题都会导致严重的结果。

要么星David的账户被扣款， 但是Mariah没收到， 又或者是David的账户没有扣款， Mariah却收到钱了。

XA事务由一个或多个资源管理器( Resource Managers) 、一个事务管理器( TransactionManager) 以及一个应用程序( Application Program) 组成。

  • 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。口事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。

  • 应用程序：定义事务的边界，指定全局事务中的操作。

在MySQL数据库的分布式事务中， 资源管理器就是MySQL数据库， 事务管理器为连接MySQL服务器的客户端。

图7-22显示了一个分布式事务的模型。

输入图片说明

分布式事务使用两段式提交(two-phasecommit) 的方式。

在第一阶段，所有参与全局事务的节点都开始准备(PREPARE)， 告诉事务管理器它们准备好提交了。

在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。

如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。

可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后， 再进行COMMIT或是ROLLBACK操作。

7.7.2 内部XA事务

之前讨论的分布式事务是外部事务， 即资源管理器是MySQL数据库本身。在MySQL数据库中还存在另外一种分布式事务， 其在存储引擎与插件之间， 又或者在存储引擎与存储引擎之间，称之为内部XA事务。

最为常见的内部 XA 事务存在于binlog与InnoDB存储引擎之间。

由于复制的需要，因此目前绝大多数的数据库都开启了binlog功能。

在事务提交时， 先写二进制日志， 再写InnoDB存储引擎的重做日志。对上述两个操作的要求也是原子的， 即二进制日志和重做日志必须同时写人。

若二进制日志先写了， 而在写入InnoDB存储引擎时发生了宕机， 那么slave可能会接收到master传过去的二进制日志并执行， 最终导致了主从不一致的情况。

如图7-23所示。

在图7-23中， 如果执行完①、②后在步骤③之前MySQL数据库发生了宕机， 则会发生主从不一致的情况。

为了解决这个问题， MySQL数据库在binlog与InnoDB存储引擎之间采用XA事务。

当事务提交时， InnoDB存储引擎会先做一个PREPARE操作， 将事务的xid写人， 接着进行二进制日志的写人， 如图7-24所示。

输入图片说明

如果在InnoDB存储引擎提交前， MySQL数据库宕机了， 那么MySQL数据库在重启后会先检查准备的UX ID事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

小结

参考资料

《mysql 技术内幕》