事务是数据库区别文件系统的重要特征之一。在文件系统中,如果你正在写文件,但是操作系统突然崩溃了,这个时候文件有可能会被损坏的,当然也会有一些机制让文件恢复到某一个时间点,比如依靠原有的备份等。
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; 可以使用的事务控制语句
隐式提交的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>
可以看出来Com_commit 始终为0 ,这就是隐式提交的时候,不计入com_commit和com_rollback中的。
另外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>
因此如果都是显示控制事务的提交和会滚得话,可以用com_commit com和rollback这2个参数,否则情况就有些复杂。