十四、InnoDB的ACID事务

十四、InnoDB的ACID事务

一、什么是事务

事务（Transaction）是数据库区别于文件系统的重要特性之一，事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务中的操作，要么都做修改，要么都不做，这就是事务的基本目的。理论上说，事务有着极其严格的定义，它必须同时满足四个特性，即通常所说的事务的ACID特性。

二、事务的ACID特性

ACID模型是关系型数据库普遍支持的事务模型，用于保证数据的一致性，其中的ACID所代表的具体含义分别如下。

1）A：atomicity原子性。事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

2）C：consistency一致性。事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。也就是说，数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

3）I：isolation独立性。多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其他事务的运行效果。

4）D：durability持续性。在事务完成以后，该事务对数据库所做的更改便持久地保存在数据库之中，并不会被回滚。

举例来说：

比如银行的汇款1000元的操作，简单来说，可以拆分成A账户的余额-1000，B账户的余额+1000，还要分别在A和B的账户流水上记录余额变更日志，这四个操作必须放在一个事务中完成，否则丢失其中的任何一条记录对整个系统来说都是不完整的。 对于上述例子来说，原子性体现在要么四条操作每条都成功，这就意味着汇款成功；要么其中某一个操作失败，则整个事务中的四条操作都回滚，即汇款失败。一致性表示当汇款结束时，A账户和B账户里的余额变化和操作日志记录是可以对应起来的。独立性表示在汇款操作过程中，如果有C账户也在往B账户里汇款的话，那么两个事务之间相互不会影响，即“A->B”有四个独立操作，“C->B”也有四个独立操作。持久性表示当汇款成功时，A和B的余额就变更了，不管是数据库重启还是别的什么原因，该数据已经写入到磁盘中作为永久存储，不会再发生变化，除非有新的事务发生。 其中事务的隔离性是通过MySQL锁机制来实现的，原子性、一致性、持久性则是通过MySQL的redo和undo日志记录来完成的

三、显式事务启动|结束

1）以start transaction/begin开始事务。

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

2）以commit/rollback transaction结束事务。

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

3)自动提交策略（autocommit）

[(none)]>select @@autocommit;
[(none)]>set autocommit=0;
[(none)]>set global autocommit=0;
注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭,默认值为1及开启
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)命令行关闭自动提交
仅当前窗口生效
set autocommit=0;   
全局生效，配置后，需要退出当前会话窗口，重新进入
set global autocommit=0;
(2)修改配置文件，下一次mysql重启生效
vim /etc/my.cnf
autocommit=0     

四、隐式提交语句

主要是DDL、DCL会引发事务隐形提交，比如create、alter语句以及grant、revoke等语句。

用于隐式提交的 SQL 语句：
1）在开启新一个事务begin开始，前一个没有commit
begin 
sql语句 a
sql语句 b
begin  #默认自动在第二个begin前添加commit,用于前一个事务的完结

2）设置了自动提交策略
SET AUTOCOMMIT = 1

3）导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

五、事务开启的流程

1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';
2、开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;
#事务回退，将数据结果返回事务开启前的结果

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;
#事务完成，更新数据结果

六、ACID对InnoDB事务保障

1、一些概念

redo log ---> 重做日志 ib_logfile0~1   50M   , 轮询使用
redo log buffer ---> redo内存区域
ibd     ----> 存储 数据行和索引 
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号 
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

2、redo log

Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。

作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

redo日志位置?
redo的日志文件：iblogfile0 iblogfile1
 
redo buffer?
redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog

redo的刷新策略?
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

3、MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5. 执行commit时,LGWR日志写线程会将redo buffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102,MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"

4、undo 回滚日志

undo是什么？
undo,顾名思义“回滚日志”

作用是什么？
在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交(没有commit)的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

5、redo和undo掌握重点

redo怎么应用的:在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致.

undo怎么应用的:在CSR实现的是,将redo当中记录的未提交(没有commit)的时候进行回滚.

CSR(自动故障恢复)过程:
   我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5. 执行commit时,LGWR日志写线程会将redo buffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102,MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动

LSN :日志序列号
TXID:事务ID
CKPT(Checkpoint)

6、锁

“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁

测试锁的作用：

同时开启两个mysql窗口，一个先写入数据但不提交commit，另外一个编辑一样的数据会卡住

1）都关闭autocommit

[world]>set autocommit=0;
[world]>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

2)一个窗口先编辑一行数据，但不提交commit;另一个窗口也同样编辑相同行的数据

下面的窗口会一直卡住一段时间,让后超时

[world]>update city set Name='xxx' where id=100;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[world]>

3）commit第一窗口，第二个窗口的sql语句会执行

7、隔离级别

影响到数据的读取,默认的级别是 RR模式.
transaction_isolation   隔离级别(参数)
[world]>select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

负责的是,MVCC,读一致性问题
RU  : 读未提交,可脏读,一般不建议使用
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差

补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;

8、架构改造项目

项目背景:
2台  IBM X3650   32G  ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全)
MySQL 5.1.77   默认存储引擎 MyISAM  
数据量: 60G左右 ,每周全备,没有开二进制日志
架构方案:
    1. 升级数据库版本到5.7.20 
    2. 更新所有业务表的存储引擎为InnoDB
    3. 重新设计备份策略为热备份,每天全备,并备份日志
    4. 重新构建主从
结果:
    1.性能
    2.安全方面
    3.快速故障处理

七、InnoDB存储引擎核心特性-参数补充

1、存储引擎相关

查看
show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;

如何指定和修改存储引擎
(1) 通过参数设置默认引擎
(2) 建表的时候进行设置
(3) alter table t1 engine=innodb;

2、 表空间

共享表空间
 innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

独立表空间
show variables like 'innodb_file_per_table';

3、缓冲区池

查询
select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size 
一般建议最多是物理内存的 75-80%

4、innodb_flush_log_at_trx_commit (双一标准之一)

作用

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

查询

select @@innodb_flush_log_at_trx_commit;

参数说明

1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且每秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------

5、Innodb_flush_method=(O_DIRECT, fdatasync)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

作用

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

查看

show variables like '%innodb_flush%';

参数值说明

O_DIRECT  :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer

使用建议

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

7、redo日志有关的参数

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

8、扩展

RR模式(对索引进行删除时):
GAP:          间隙锁
next-lock:    下一键锁定

例子:
id（有索引）
1 2 3 4 5 6 
GAP：
在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁
next-lock：
对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。
总之：
GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。
IX IS X S是什么?

t_trx_commit=0
Innodb_flush_method=fsync

## 7、redo日志有关的参数

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

## 8、扩展

RR模式(对索引进行删除时):
GAP: 间隙锁
next-lock: 下一键锁定

例子:
id（有索引）
1 2 3 4 5 6
GAP：
在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁
next-lock：
对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。
总之：
GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。
IX IS X S是什么?