InnoDB事务

事务的ACID特性

  • Atomic(原子性):所有语句作为一个单元全部成功执行或全部取消.不能出现中间状态
  • Consistent(一致性):如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态
  • Isolated(隔离性):事务之间不相互影响
  • Durable(持久性):事务成功完成后,所做的所有更改都会准确地记录在数据库中.所做的更改不会丢失

事务的生命周期(标准的事务控制语句)

如何开启事务和结束事务

#开启事务
begin;
#提交事务
commit;
#回滚
rollback;

标准的事务语句

#DML
use world;
#包括insert delete update语句
update city set countrycode='CHN' where id=1;

自动提交机制(autocommit)

#查看自动提交参数0为关闭 1为开启
select @@autocommit;
#在命令行设置当前会话自动提交
set autocommit=0;
#在命令行设置全局自动提交
#断开窗口重连后生效,影响到所有新开的会话
set global autocommit=0;

修改配置参数

vim /etc/my.cnf

#关闭自动提交,重启MySQL生效
autocommit=0

隐式提交的情况

触发隐式提交的语句

  • begin;
  • create database ... ;

导致提交的非事务语句

DDL语句

  • ALTER
  • CREATE
  • DROP

DCL语句

  • GRANT
  • REVOKE
  • SET PASSWORD

锁定语句

  • LOCK TABLES
  • UNLOCK TABLES

导致隐式提交的语句

  • TRUNCATE TABLE
  • LOAD DATA INFILE
  • SELECT FOR UPDATE

事务的ACID如何保证

acid.png

  • data buffer pool : 缓冲区池,数据和索引的缓冲
  • redo buffer : redo内存区域
  • ibd : 存储数据行和索引
  • redo log : 重做日志
  • LSN : 日志序列号
  • ib_logfile0~1 : 默认50M,轮询使用

MySQL每次数据库启动,都会比较磁盘数据页和redo log的LSN,必须要求两者LSN一致数据库才能正常启动

  • 脏页 : 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页
  • CKPT : Checkpoint,检查点,就是将脏页刷写到磁盘的动作
  • TXID : 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
  • WAL(持久化) : write ahead log 日志优先写的方式实现持久化,日志是优先于数据写入磁盘的

redo重做日志的作用

主要功能保证持久性(D),原子性(A)和一致性(C)也有一定得作用

  1. 记录 了内存数据页的变化
  2. 提供 快速的事务的提交(commit)
  3. CSR 过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)

redo日志位置

  • redo的日志文件 : iblogfile0 iblogfile1
  • redo buffer : 数据页的变化信息+数据页当时的LSN号

redo的刷写策略

  • commit;命令会刷新当前事务的redo buffer到磁盘,还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘.MySQL在启动时,必须保证redo日志文件和数据文件LSN必须一致,如果不一致就会触发CSR,最终保证一致

事务故障流程详解

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

undo回滚日志的作用

在ACID特性中,主要保证原子性(A),同时对一致性(C)和隔离性(I)也有一定功效

  1. 记录 了数据修改之前的状态
  2. 提供 事务工作过过程中回滚操作(rollback)
  3. CSR 过程中实现未提交数据的回滚操作
  4. 实现一致性快照,配合隔离级别保证MVCC,读和写的操作不会互相阻塞

锁

实现了事务之间的隔离功能,InnoDB中实现的是行级锁(row-level lock),初次之外还有GAP(间隙锁),NextLock(下键锁),但需要索引支持

隔离级别

  • RU : 读未提交,会出现脏读,不可重复读,幻读
  • RC : 读已提交,可能出现幻读,不可重复读,可以防止脏读
  • RR : 可重复读,(MVCC,undo快照)功能是防止幻读现象,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁),可以用通过GAP+Next LOCK来防止幻读(索引)
  • SR : 可串行化,可以防止死锁,但是并发事务性能较差

在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.请记住执行完一定要commit否则容易出现所等待比较严重

可以在my.cnf中设置隔离级别

transaction_isolation=READ-UNCOMMITED
transaction_isolation=READ-COMMITED
transaction_isolation=REPEATABLE-READ

InnoDB核心参数的介绍

  • default_storage_engine=innodb : 存储引擎默认设置
  • innodb_file_per_table=1 : 表空间模式 0为共享表空间 1为独立表空间
  • innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend : 共享表空间文件个数和大小
  • innodb_flush_log_at_trx_commit=1 : 事务提交日志刷新,双一标准的其中一个

有关innodb_flush_log_at_trx_commit参数的文档

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.

翻译内容

要完全符合ACID，必须使用默认设置1。 日志在每次事务提交时写入并刷新到磁盘。

设置为0时，每秒写入一次日志并将其刷新到磁盘。 尚未刷新日志的事务可能会在崩溃中丢失。

设置为2时，在每次事务提交后写入日志，并每秒刷新一次到磁盘。 尚未刷新日志的事务可能会在崩溃中丢失。

  • Innodb_flush_method : 控制着innodb数据文件及redo log的打开,刷写模式有3个值fdatasync,O_DSYNC,O_DIRECT.fdatasync是默认值

fdatasync : 调用fsync()去刷数据文件与redo log的buffer

fsync.png

O_DSYNC : innodb会使用O_DSYNC方式打开和刷写redo log,使用fsync()刷写数据文件

O_DSYNC.png

O_DIRECT : innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

O_DIRECT.png

最高安全模式

innodb_flush_log_at_trx_commit=1

Innodb_flush_method=O_DIRECT

最高性能

innodb_flush_log_at_trx_commit=0

Innodb_flush_method=fsync

  • innodb_log_buffer_size=16777216 : redo日志设置相关设置
  • innodb_log_file_size=50331648 : redo日志设置相关设置
  • innodb_log_files_in_group=3 : redo日志设置相关设置
  • innodb_max_dirty_pages_pct=75 : 脏页刷写策略