mysql - 事务控制语句 & 重做日志的相关参数

 

事务日志参数：

查看日志参数：

 mysql> show variables like '%innodb%log%'; 

 

1. innodb_log_file_size 表示每个redo log file的大小，单位为字节，上图中的设置表示每个重做日志文件的大小48为M
2. innodb_log_files_in_group 表示每个重做日志组中有几个redo log file
3. innodb_log_group_home_dir 表示重做日志组文件所在路径，此处的相对路径表示数据所在目录，默认情况下为/var/lib/mysql,此目录中的ib_logfile0与ib_logfile1即为日志组中的两个重做日志
4. innodb_mirrored_log_groups 表示一共有几组日志组，上图中的1表示一共只有一组重做日志，换句话说，1表示只有1组镜像日志组，就是当前日志组本身，说白了，如果此值为1，表示没有冗余的日志组，如果想要有冗余的镜像日志组，此值至少要设置为2，此值容易被字面误解，需注意，如果重做日志所在的硬件设备并没有冗余能力，同时用户对数据安全性要求较高，那么往往需要将此值设置为大于等于2的值。
5. innodb_flush_log_at_trx_commit 表示当事务提交以后，是否立即将redo log从内存（log buffer）刷写到redo log file中
   1. 如果此值设置为1（默认值），表示事务提交时必须将redo log从log buffer中刷写到redologfile（磁盘）中，过程为：事务提交--log buffer--os buffer--log file，此值为1时完全满足ACID的要求。
   2. 如果此值设置为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。
   3. 如果此值设置为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时要高一些。
   4. 小结：

      理论上来说，

      1. 此值设置为1，安全性最高，性能最低，

      2. 设置为0，性能最高，安全性最低，

      3. 设置为2，性能较高，安全性较低，

      4. 此值设置为1，能够满足ACID的特性，

      5. 设置为0或2，将会失去ACID的特性。

      6. 但是需要注意,很多操作系统或者硬盘设备会欺骗mysqld进程，让mysqld进程认为刷写操作已经完成，但是实际上并没有，在这种情况下，即使innodb_flush_log_at_trx_commit的值设置为1，也不能保证事务的可用性，具体预防方法可以查看官方文档，地址如下。

         https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

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

 

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

事务控制语句：

1. 查看【是否自动提交事务】配置参数：

   mysql> select @@autocommit;
   +--------------+
   | @@autocommit |
   +--------------+
   |            1 |
   +--------------+
   1 row in set (0.00 sec)

   1：自动提交；2：手动提交。

2. 事务控制语句：
   1. begin（start transaction）：表示显示的开始一个事务，虽然begin和start transaction都表示显式的开启一个事务，但是在存储过程中，mysql会将begin识别为begin···end，所以，在存储过程中，只能使用start transaction来表示开始一个事务；
   2. commit 或者 commit work ：表示提交事务，也就是说从begin到commit之间的所有sql语句对数据库所作出的修改将会被真正的执行，成为永久性的操作；
   3. rollback 或者 rollback work ：表示回滚事务，回滚事务会撤销所有未提交的修改并结束当前事务，注意，使用rollback回滚事务以后，当前事务会结束，后面的操作不算在当前事务以内。

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

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

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

3. 设置当前会话的自动提交参数：

   mysql> set @@session.autocommit=0;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> select @@autocommit;
   +--------------+
   | @@autocommit |
   +--------------+
   |            0 |
   +--------------+
   1 row in set (0.00 sec)