mysql 事务参数_mysql/mariadb知识点总结：事务控制语句，事务日志参数

这篇文章将会总结mysql中事务的事务控制语句，以及重做日志的相关参数。

在本博客中，"mysql"是一个系列文章，这些文章主要对mysql/mariadb的常用知识点进行了总结，每一篇博客总结的知识点有所不同，具体内容可参考mysql文章列表。

mysql文章列表直达链接：mysql知识点总结

上文中，主要对事务的概念进行了描述，这篇文章主要描述重做日志的先关参数以及事务控制语句的使用方法。

如果事务的相关概念还不了解，可以参考前文。

事务日志参数

话接上文，当使用innodb存储引擎时，我们可以通过如下语句，查看与日志相关的配置参数。

show global variables like '%innodb%log%';

innodb_log_file_size 表示每个redo log file的大小，单位为字节，上图中的设置表示每个重做日志文件的大小为5M

innodb_log_files_in_group 表示每个重做日志组中有几个redo log file

innodb_log_group_home_dir 表示重做日志组文件所在路径，此处的相对路径表示数据所在目录，默认情况下为/var/lib/mysql,此目录中的ib_logfile0与ib_logfile1即为日志组中的两个重做日志，可以看到，这两个日志文件的大小为5M，也对应了innodb_log_file_size的值

innodb_mirrored_log_groups 表示一共有几组日志组，上图中的1表示一共只有一组重做日志，换句话说，1表示只有1组镜像日志组，就是当前日志组本身，说白了，如果此值为1，表示没有冗余的日志组，如果想要有冗余的镜像日志组，此值至少要设置为2，此值容易被字面误解，需注意，如果重做日志所在的硬件设备并没有冗余能力，同时用户对数据安全性要求较高，那么往往需要将此值设置为大于等于2的值。

innodb_flush_log_at_trx_commit 表示当事务提交以后，是否立即将redo log从内存(log buffer)刷写到redo log file中。

如果此值设置为1(默认值)，表示事务提交时必须将redo log从log buffer中刷写到redologfile(磁盘)中，过程为：事务提交--log buffer--os buffer--log file，此值为1时完全满足ACID的要求。

如果此值设置为0，事务提交时并不会将redo log从log buffer刷写到redo log file，但是会在每秒钟自动刷写一次，也就是说每一秒钟都自动将内存中的redo log刷写到redo log file(磁盘)中，可以理解为，当事务提交时，redo log存在于log buffer中，每秒钟，log从log buffer中经过os buffer，刷写到log file中一次，当此值设置为0时，如果mysql数据库崩溃，最多会丢失1秒钟的redo log。

如果此值设置为2，表示在事务提交时，只会将redo log写入到文件系统内存(os buffer)中，但是不会立即写入到redo log file(磁盘)中，而是每秒钟从文件系统缓存中将数据刷写至redo log file(磁盘)中一次，可以理解为，当事务提交时，redo log存在于log buffer和os buffer中，每秒钟，log从os buffer中刷写到log file中一次，此值为2时，如果只是mysql数据库宕机，但是操作系统没有宕机，则数据不会丢失，如果此时操作系统宕机，重启数据库后，则会丢失未从文件系统内存刷写到redo log file中的那部分事务(约1秒钟的数据)，因为只有mysql宕机而操作系统没有宕机时，并不会丢失数据，所以可靠性 比此值设置为0时要高一些。

理论上来说，此值设置为1，安全性最高，性能最低，设置为0，性能最高，安全性最低，设置为2，性能较高，安全性较低，此值设置为1，能够满足ACID的特性，设置为0或2，将会失去ACID的特性。但是需要注意,很多操作系统或者硬盘设备会欺骗mysqld进程，让mysqld进程认为刷写操作已经完成，但是实际上并没有，在这种情况下，即使innodb_flush_log_at_trx_commit的值设置为1，也不能保证事务的可用性，具体预防方法可以查看官方文档，地址如下。

上述关于innodb_flush_log_at_trx_commit参数的总结参考了 "官网文档" 与 "MySQL技术内幕：InnoDB存储引擎"

如果我们想要根据自己的需要，设置重做日志的相关参数，只要修改上述变量即可。或者将上述设置写入配置文件中。

事务控制语句

在mysql中，默认情况下，我们每执行一条sql语句，mysql都会把这条sql当做一个单语句事务进行提交，而且默认是自动提交的，我们可以使用如下语句查看mysql是否开启了自动提交功能，如下两条语句分别查看全局与当前会话是否开启了自动提交功能。

show global variables like 'autocommit%';

show session variables like 'autocommit%';

如上图所示，默认情况下，autocommit是开启的，表示事务都是自动提交到，执行sql语句以后也会立即自动执行commit操作。

如果想要手动的控制提交操作，则需要显示的开启一个事务，或者禁用自动提交功能(set autocommit=0)，进行手动提交，上述两种方法都行，我们一个一个聊。

首先，我们来了解一些事务的控制语句，了解了这些语句，就自然会显示的控制事务了。

start transaction 或者 begin ：表示显示的开始一个事务，虽然begin和start transaction都表示显式的开启一个事务，但是在存储过程中，mysql会将begin识别为begin···end，所以，在存储过程中，只能使用start transaction来表示开始一个事务。

commit 或者 commit work ：表示提交事务，也就是说从begin到commit之间的所有sql语句对数据库所作出的修改将会被真正的执行，成为永久性的操作。

rollback 或者 rollback work ：表示回滚事务，回滚事务会撤销所有未提交的修改并结束当前事务，注意，使用rollback回滚事务以后，当前事务会结束，后面的操作不算在当前事务以内。

savepoint 标识符 ：表示创建一个事务的保存点，以便我们回滚到当前保存点，而不是回滚整个事务，就好比我们的游戏存档一样，如果你在当前位置设置了保存点，那么当你game over的时候，可以从这个保存点继续，而不是从游戏的开始处继续，一个事务中可以创建多个保存点。

rollback to savepoint 标识符 ：表示根据标识符回滚到指定的保存点，使用rollback to savepoint只会撤销对应保存点之后的操作，而且并不会结束当前事务，回滚到指定的保存点以后的操作仍然属于当前事务，与rollback不同。

release savepoint 标识符 ：表示删除一个保存点。

如果你还不理解上述事务控制语句，没有关系，我们来实际操作一下，你就会立马明白了，此处使用t1表做示例，t1表内容如下。

先从最简单的事务开始，示例如下

使用begin时事务开始，开始事务以后，我们插入了两条数据，然后执行了commit操作，提交事务以后，事务完成，所作出的修改被持久化。

上述示例就是最简单的事务开始，提交事务，事务中的sql语句被当做一个整体，要么全部执行成功，要么执行某一条语句失败，进行回滚。

那么，我们来看看回顾操作的示例，如下。

事务开始之前，t1表中有5条数据，事务开始以后，我们删除了t1id为4和5的两条记录，但是，我们并没有执行commit操作，所以这些删除操作并没有真正的持久化到数据库中，此时，我们执行了rollback操作，所以，所有未提交的操作都被撤销了，同时当前事务结束。

上述示例中已经使用到了回滚，但是，并没有使用到保存点，那么我们给出一个带有保存点的示例吧。

上图中事务执行的过程如下

事务开始前有5条数据

开始事务

删除了id号为3记录，创建了保存点，del3

插入了一条新的记录，创建了保存点，add6

删除了id号为4的记录，创建了保存点，del4

执行了回滚操作，指定回滚到保存点add6，回滚以后，id号为4的记录仍然存在，因为在add6保存点中，还未执行删除id号为4的记录的操作。

执行commit，提交事务，所有修改以提交时的为准，并且持久化。

通过事务控制语句，即可显示的手动的对事务进行控制，之前说过，我们也可以禁用autocommit功能，从而进行手动的提交操作，示例如下。

为了方便以后的操作，此处只将当前会话的autocommit功能关闭，使用如下语句关闭当前会话的autocommit功能。

set @@session.autocommit=0

当前会话的自动提交功能关闭以后，不进行手动commit操作，对应sql也是不会被真正提交的。

因为关闭了自动提交功能，所以，每个sql语句并不会自动被当做一个单语句事务，所以每个sql语句并不会被自动提交。如果想要将之前的修改持久化，需要手动执行commit操作。

好了，事务控制语句 和 重做日志的相关参数就总结到这里，希望这篇文章能对你有所帮助。