MySQL 事务的学习整理
事务是数据库区别文件系统的重要特征之一。在文件系统中，如果你正在写文件，但是操作系统突然崩溃了，这个时候文件有可能会被损坏的，当然也会有一些机制让文件恢复到某一个时间点，比如依靠原有的备份等。
1 引入事务的目的
数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换成另外一种状态。在数据库提交工作时，可以确保其要么所有修改都已经保存了，要么所有修改都不保存。
2 ACID简介
InnoDB存储引擎中的事务完全符合ACID的特性。ACID是如下：
原子性是指只有使事务中所有的数据库操作执行都成功，才算整个事务成功。只要有一个
数据库在事务开始之前和结束之后，数据库的完整性约束没有被破坏。
数据库
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
，因为它是通过执行相反的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）
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> mysql> show global status like'com_commit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_commit | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | t1 | | t3 | | z | +----------------+ 4 rows in set (0.00 sec) mysql> insert into z select 1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from z; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> show global status like'com_commit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_commit | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
另外mysql还有2个参数 handler_commit和handler_rollback，这2个参数在mysql5.1种可以很好的统计innodb的显示和隐式的事务提交操作，而在innodb plugin中这2个参数的统计值有些怪异，不能很好的统计事务的次数。
mysql> show global status like'Handler_commit'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Handler_commit | 17 | +----------------+-------+ 1 row in set (0.00 sec) mysql> show global status like'Handler_rollback'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Handler_rollback | 0 | +------------------+-------+ 1 row in set (0.00 sec) mysql>