mysql:事务

一、定义

事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别与文件系统的重要特征之一。

二、事务ACID特性

• 原子性（atomicity）:简单来说，要么都做，要么都不做
• 一致性（consistency）:一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
• 隔离性（isolation）：每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见
• 持久性（durability）:事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。需要注意的是，只能从事务本身的角度来保证结果的永久性.

三、事务的分类

• 扁平事务（Flat Transactions）:在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。
• 带有保存点的扁平事务（Flat Transactions with Savepoints）:除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早保存点的一个状态。
• 链事务（Chained Transactions）：可视为保存点模式的一种变种。带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的（volatile），而非持久的（persistent）。这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。
• 嵌套事务（Nested Transactions）:是一个层次结构框架。由一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换。
• 分布式事务（Distributed Transactions）：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

四、事务的实现

锁实现事务的隔离性;
redo log实现事务的原子性和持久性;
undo log实现事务的一致性;

1. redo log

请参考下面博客:
redo log

2. undo log

请参考下面博客:
undo log

3.Binary Log Group Commit（BLGC）

(1) 作用

因为数据需要备份和恢复，所以需要保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致。BLGC能够达到上述效果，并且能够大大提高效率。

(2) BLGC流程

在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为leader，其他事务称为follower，leader控制着follower的行为。BLGC的步骤分为以下三个阶段：

• Flush阶段，将每个事务的二进制日志写入内存中。
• Sync阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的写入，这就是BLGC。
• Commit阶段，leader根据顺序调用存储引擎层事务的提交，InnoDB存储引擎本就支持group commit，因此修复了原先由于锁prepare_commit_mutex导致group commit失效的问题。

(3) BLGC流程图解

(4) BLGC设置

当有一组事务在进行Commit阶段时，其他新事物可以进行Flush阶段，从而使group commit不断生效。当然group commit的效果由队列中事务的数量决定，若每次队列中仅有一个事务，那么可能效果和之前差不多，甚至会更差。但当提交的事务越多时，group commit的效果越明显，数据库性能的提升也就越大。

//用来控制Flush阶段中等待的时间，即使之前的一组事务完成提交，当前一组的事务也不马上进入Sync阶段，而是至少需要等待一段时间。
mysql> show variables like 'binlog_max_flush_queue_time';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| binlog_max_flush_queue_time | 0     |
+-----------------------------+-------+

这样做的好处是group commit的事务数量更多，然而这也可能会导致事务的响应时间变慢。该参数的默认值为0，且推荐设置依然为0。除非用户的MySQL数据库系统中有着大量的连接（如100个连接），并且不断地在进行事务的写入或更新操作。

五、事务控制语句

1、事务控制语句相关命令

• START TRANSACTION|BEGIN：显式地开启一个事务。
• COMMIT：要想使用这个语句的最简形式，只需发出COMMIT。也可以更详细一些，写为COMMIT WORK，不过这二者几乎是等价的。COMMIT会提交事务，并使得已对数据库做的所有修改成为永久性的。
• ROLLBACK：要想使用这个语句的最简形式，只需发出ROLLBACK。同样地，也可以写为ROLLBACK WORK，但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
• SAVEPOINT identifier∶SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
• RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常。
• ROLLBACK TO [SAVEPOINT]identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。

2. 相关设置

//在MySQL命令行的默认设置下，事务都是自动提交（auto commit）的，即执行SQL语句后就会马上执行COMMIT操作
mysql> show variables like 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

//关闭自动提交
mysql> SET AUTOCOMMIT=0;

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

3. 简单例子

//带有保存点的扁平化事务
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 2 |  222 |
| 3 |   33 |
+---+------+

mysql> begin;
mysql> insert into t values(4,44);
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 2 |  222 |
| 3 |   33 |
| 4 |   44 |
+---+------+
//创建保存点
mysql> SAVEPOINT first; 

mysql> insert into t values(5,55);
//返回保存点
mysql> ROLLBACK SAVEPOINT first;
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 2 |  222 |
| 3 |   33 |
| 4 |   44 |
+---+------+

//返回事务刚开始的保存点
mysql> ROLLBACK ;
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 2 |  222 |
| 3 |   33 |
+---+------+

4. COMMIT(ROLLBACK ) VS COMMIT(ROLLBACK ) WORK

(1) 区别

• 相同点:COMMIT和COMMIT WORK语句基本是一致的，都是用来提交事务。
• 不同点:COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。
  如果是CHAIN方式，那么事务就变成了链事务。
  如果是RELEASE。在事务提交后会自动断开与服务器的连接，

(2) 相关设置

用户可以通过参数completion_type来进行控制，

• 该参数默认为0，表示没有任何操作。在这种设置下COMMIT和COMMIT WORK是完全等价的。
• 值为1时，COMMIT WORK等同于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务，
• 值为2时，COMMIT WORK等同于COMMIT AND RELEASE。在事务提交后会自动断开与服务器的连接，

//查看completion_type
mysql> select @@global.completion_type;
+--------------------------+
| @@global.completion_type |
+--------------------------+
| NO_CHAIN                 |
+--------------------------+
mysql>  select @@session.completion_type;
+---------------------------+
| @@session.completion_type |
+---------------------------+
| NO_CHAIN                  |

//设置completion_type
mysql> set @@session.completion_type=1;
mysql> show variables like 'completion_type';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| completion_type | CHAIN |
+-----------------+-------+
mysql> select @@session.completion_type;
+---------------------------+
| @@session.completion_type |
+---------------------------+
| CHAIN                     |
+---------------------------+

(3) 举例

//测试COMMIT WORK;
mysql> CREATE TABLE t(a INT,PRIMARY KEY(a)) ENGINE=INNODB;
mysql> SET @@completion_type=1;

mysql> BEGIN;
mysql> INSERT INTO t SELECT 1;
mysql> COMMIT WORK;

mysql> INSERT INTO t SELECT 2;
mysql> INSERT INTO t SELECT 2;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> ROLLBACK;
mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+

//紧接着测试ROLLBACK WORK
mysql> delete from t;
mysql> select * from t;
Empty set (0.00 sec)
mysql> insert into t values (1);
//别的事务看不到
mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+
//回滚并开启下一个链式事务
mysql> ROLLBACK WORK ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (1);
//提交事务
mysql> COMMIT;

//注意:另一个容易犯的错误是ROLLBACK TO SAVEPOINT，虽然有ROLLBACK，但其并不是真正地结束一个事务，因此即使执行了ROLLBACK TO SAVEPOINT，之后也需要显式地运行COMMIT或ROLLBACK命令。

//紧接着测试ROLLBACK TO SAVEPOINT
mysql> begin;
mysql> insert into t values (2);
mysql> SAVEPOINT t1;

mysql> insert into t values (3);
mysql> insert into t values (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
//回滚到t1保存点 别的事务看不到
mysql> ROLLBACK TO t1;

//提交t1保存点的事务，也可以执行ROLLBACK回滚
mysql> commit ;

六、对事务的操作统计

1. TPS

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

2. 统计方法

(1) com_commit和com_rollback

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

mysql> SHOW GLOBAL STATUS LIKE 'com_commit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 18    |
+---------------+-------+

mysql> SHOW GLOBAL STATUS LIKE 'com_rollback';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback  | 9     |
+---------------+-------+

(2) handler_commit和handler_rollback

handler_commit和handler_rollback用于事务的统计操作。但是我注意到这两个参数在MySQL 5.1中可以很好地用来统计InnoDB存储引擎显式和隐式的事务提交操作，但是在InnoDB Plugin中这两个参数的表现有些“怪异”，并不能很好地统计事务的次数。

mysql> SHOW GLOBAL STATUS LIKE 'handler_commit';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 431   |
+----------------+-------+
mysql> SHOW GLOBAL STATUS LIKE 'handler_rollback';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Handler_rollback | 36    |
+------------------+-------+

//隐式提交
mysql> insert into t values (5);
mysql> SHOW GLOBAL STATUS LIKE 'handler_commit';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_commit | 432   |
+----------------+-------+

七、事务的隔离级别

1. 事务隔离级别的定义

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

• READ UNCOMMITTED:称为浏览访问（browse access），仅仅针对事务而言的。
• READ COMMITTED：称为游标稳定（cursor stability）。
• REPEATABLE READ:是2.9999°的隔离，没有幻读的保护。
• SERIALIZABLE：称为隔离，或3°的隔离。

注意:InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。这与其他数据库系统（如Microsoft SQL Server数据库）是不同的。所以说，InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。

大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题，但是根据Jim Gray在《Transaction Processing》一书中指出，两者的开销几乎是一样的，甚至SERIALIZABLE可能更优!!!因此在InnoDB存储引擎中选择REPEATABLE READ的事务隔离级别并不会有任何性能的损失。同样地，即使使用READ COMMITTED的隔离级别，用户也不会得到性能的大幅度提升。

在SERIALIABLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

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

在MySQL 5.1版本之后，因为支持了ROW格式的二进制日志记录格式，建议使用ROW格式的二进制日志来避免同步数据产生不一致。

2. 相关设置

//设置当前会话或全局的事务隔离级别：
SET [GLOBAL|SESSION] TRANSACTION  ISOLATION  LEVEL
{
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
}

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

//查看当前会话的事务隔离级别
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

//查看全局的事务隔离级别
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

八、分布式事务

1. MySQL数据库分布式事务

(1) 前提条件

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

(2) XA事务

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

XA事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

• 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
• 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
• 应用程序：定义事务的边界，指定全局事务中的操作。

在MySQL数据库的分布式事务中，资源管理器就是MySQL数据库，事务管理器为连接MySQL服务器的客户端。图7-22显示了一个分布式事务的模型。

(3) 分布式事务使用两段式提交（two-phase commit）

• 在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。
• 在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。

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

(4) mysql内部XA事务

其在存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部XA事务。

MySQL数据库通过内部XA事务保证主从数据一致。
MySQL数据库在binlog与InnoDB存储引擎之间采用XA事务。当事务提交时，InnoDB存储引擎会先做一个PREPARE操作，将事务的xid写入，接着进行二进制日志的写入,接着进行redo log写入。如果在InnoDB存储引擎提交前，MySQL数据库宕机了，那么MySQL数据库在重启后会先检查准备的UXID事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

在图7-23中，如果执行完①、②后在步骤③之前MySQL数据库发生了宕机，则会发生主从不一致的情况。为了解决这个问题，MySQL数据库在binlog与InnoDB存储引擎之间采用XA事务。当事务提交时，InnoDB存储引擎会先做一个PREPARE操作，将事务的xid写入，接着进行二进制日志的写入，如图7-24所示。如果在InnoDB存储引擎提交前，MySQL数据库宕机了，那么MySQL数据库在重启后会先检查准备的UXID事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

九、不好的事务习惯

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，结果其实是完全一样的。

2. 使用自动提交

自动提交并不是一个好的习惯，因为这会使初级DBA容易犯错，另外还可能使一些开发人员产生错误的理解，如我们在上面提到的循环提交问题。MySQL数据库默认设置使用自动提交（autocommit）。

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

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

mysql> show variables like 'completion_type';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| completion_type | NO_CHAIN |
+-----------------+----------+

3. 使用自动回滚

对于开发人员来说，重要的不仅是知道发生了错误，而是发生了什么样的错误。因此msyql自动回滚存在这样的一个问题。

//创建存储过程
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。但是不知道到底发生了什么错误

4. 使用长事务

定义:就是执行时间较长的事务

由于事务ACID的特性，这个操作被封装在一个事务中完成。这就产生了一个问题，在执行过程中，当数据库或操作系统、硬件等发生问题时，重新开始事务的代价变得不可接受。数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。

正确处理:对于长事务的问题，有时可以通过转化为小批量(mini batch)的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。