MySQL 事务的学习整理

事务是数据库区别文件系统的重要特征之一。在文件系统中，如果你正在写文件，但是操作系统突然崩溃了，这个时候文件有可能会被损坏的，当然也会有一些机制让文件恢复到某一个时间点，比如依靠原有的备份等。

 

1 引入事务的目的

数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换成另外一种状态。在数据库提交工作时，可以确保其要么所有修改都已经保存了，要么所有修改都不保存。

 

2 ACID简介

InnoDB存储引擎中的事务完全符合ACID的特性。ACID是如下：

原子性 (atomicity)

       原子性是指一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。只有使事务中所有的数据库操作执行都成功，才算整个事务成功。只要有一个sql语句执行失败，那么在这个事务中已经执行的sql语句都必须撤销，数据库状态应该退回到执行事务前的状态。

一致性(consistency)

    事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。在事务开始之前和结束之后，数据库的完整性约束没有被破坏。

隔离性 (isolation)

       一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰，这些通过锁来实现。

持久性(durability) 

       持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障（比如说宕机等）不应该对其有任何影响。

 

3 事务的实现

       隔离性 (isolation)通过锁来实现，其他3个原子性atomicity，一致性consistency，持久性durability通过数据库的redo和undo来完成。

 

3.1  redo

         在Innodb存储引擎中，事务日志通过redo日至文件和Innodb存储引擎的日志缓冲(InnoDBLog Buffer)来实现。当开始一个事务时候，会纪录该事务的一个LSN（Log Sequence Number日志序列号）；当事务执行时候，会往InnoDB存储引擎的日志缓冲里插入事务日志；当事务提交时，必须将InnoDB存储引擎的日志缓冲写入disk（默认的实现，即innodb_flush_log_at_trx_commit=1）。也就是在写数据之前，需要先写日志，这种方式称为预写日志方式WAL（Write-Ahead Logging）。

         InnoDB存储引擎通过预写日志的方式来保证事务的完整性。这意味着disk上存储的数据页和内存缓冲池中的页是不同步的，对于内存缓冲池中页的修改，先是写入redo日志文件，然后再写入磁盘，因此是一种异步的方式，可以通过show engine innodb status;来观察当前disk和log的差距：

Create table z (a int,primary key(a))engine=innodb;

 

DELIMITER $$

Use test $$

DROP PROCEDURE IF EXISTS`test`.`load_test`$$

CREATE PROCEDURE load_test(COUNT INT)

BEGIN

 DECLARE i INT UNSIGNED DEFAULT 0;

 START TRANSACTION;

 WHILE i < COUNT DO

         REPLACEINTO z SELECT i;  -- 用replace是因为需要重复调用，避免主键重复insert报错。

     SET i=i+1;

         ENDWHILE;

 COMMIT;

END $$

DELIMITER ;

 

3.2 undo

undo的记录正好与redo的相反，insert变成delete，update变成相反的update，redo放在redo file里面。而undo放在一个内部的一个特殊segment上面，存储与共享表空间内（ibdata1或者ibdata2中）。参考：http://blog.csdn.net/mchdba/article/details/8664943

undo不是物理恢复，是逻辑恢复，因为它是通过执行相反的dml语句来实现的。而且不会回收因为insert和upate而新增加的page页的，undo页的回收是通过masterthread线程来实现的。

 

 

4 事务型控制语句

在mysql命令行的默认下，事务都是自动提交的，sql语句提交后马上会执行commit操作。因此开启一个事务必须使用begin，start transaction，或者执行 set autocommit=0; 可以使用的事务控制语句

starttransction | begin : 显示的开启一个事务，参考http://blog.csdn.net/mchdba/article/details/8690935

    隐式提交的sql语句：（1）ddl语句，alter database…,alter event, alter procedure,alter table ,alterview,create table,drop table,rename table ,truncate table等；（2）修改mysql架构的语句，createuser,drop user,grant,rename user,revoke,set password。（3）管理语句，analyze table,cache index,check table,load index into cache,optimizetable,repair table等。

    [truncate table 语句是ddl，不能rollback的，这点和mssqlserver不同。]

5 对事务操作的统计

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

         计算TPS的方法（com_commit+com_rollback）/tim。这种计算方法必须有一个前提条件是：所有的事务必须是显示提交的，如果存在隐式的提交和会滚（默认autocommit=1，或者value为on）

1. mysql> show variables like 'autocommit';  
2. +---------------+-------+  
3. | Variable_name | Value |  
4. +---------------+-------+  
5. | autocommit    | ON   |  
6. +---------------+-------+  
7. 1 row in set (0.00 sec)  
8.    
9. mysql>  
10. mysql> show global status like'com_commit';  
11. +---------------+-------+  
12. | Variable_name | Value |  
13. +---------------+-------+  
14. | Com_commit    | 0    |  
15. +---------------+-------+  
16. 1 row in set (0.00 sec)  
17.    
18. mysql> use test;  
19. Database changed  
20. mysql> show tables;  
21. +----------------+  
22. | Tables_in_test |  
23. +----------------+  
24. | t              |  
25. | t1             |  
26. | t3             |  
27. | z              |  
28. +----------------+  
29. 4 rows in set (0.00 sec)  
30.    
31. mysql> insert into z select 1;  
32. Query OK, 1 row affected (0.00 sec)  
33. Records: 1 Duplicates: 0  Warnings: 0  
34.    
35. mysql> select * from z;  
36. +---+  
37. | a |  
38. +---+  
39. | 1 |  
40. +---+  
41. 1 row in set (0.00 sec)  
42.    
43. mysql> show global status like'com_commit';  
44. +---------------+-------+  
45. | Variable_name | Value |  
46. +---------------+-------+  
47. | Com_commit    | 0    |  
48. +---------------+-------+  
49. 1 row in set (0.00 sec)  
50.    
51. mysql>  

可以看出来Com_commit 始终为0 ，这就是隐式提交的时候，不计入com_commit和com_rollback中的。

 

         另外mysql还有2个参数 handler_commit和handler_rollback，这2个参数在mysql5.1种可以很好的统计innodb的显示和隐式的事务提交操作，而在innodb plugin中这2个参数的统计值有些怪异，不能很好的统计事务的次数。

1. mysql> show global status like'Handler_commit';  
2. +----------------+-------+  
3. | Variable_name  | Value |  
4. +----------------+-------+  
5. | Handler_commit | 17    |  
6. +----------------+-------+  
7. 1 row in set (0.00 sec)  
8.    
9. mysql> show global status like'Handler_rollback';  
10. +------------------+-------+  
11. | Variable_name    | Value |  
12. +------------------+-------+  
13. | Handler_rollback | 0     |  
14. +------------------+-------+  
15. 1 row in set (0.00 sec)  
16.    
17. mysql>  

因此如果都是显示控制事务的提交和会滚得话，可以用com_commit com和rollback这2个参数，否则情况就有些复杂。