一、定义
事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作,要么都做修改,要么都不做,这就是事务的目的,也是事务模型区别与文件系统的重要特征之一。
二、事务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)的事务来进行处理。当事务发生错误时,只需要回滚一部分数据,然后接着上次已完成的事务继续进行。