玖叶教程网

前端编程开发入门

2024年04月25日

MySQL 事务特性以及工作原理与刷盘策略

一、事务的 ACID 特性

作用是什么？
影响了 DML 语句（insert update delete 一部分select）

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

二、隔离级别

transaction_isolation 隔离级别（参数）负责的是 MVCC 读一致性问题：

  • RU : 读未提交，可脏读，一般不一起出现
  • RC : 读已提交，可能出现幻读，可以防止脏读
  • RR : 可重复读，功能是防止"幻读"现象 ，利用的是 undo 的快照技术 + GAP（间隙锁）+ NextLock（下键锁）
  • SR : 可串行化，可以防止死锁，但是并发事务性能较差


补充：

在 RC 级别下，可以减轻 GAP + NextLock 锁的问题，但是会出现幻读现象。一般在为了读一致性会在正常 select 后添加 for update 语句。但是，请记住执行完一定要 commit。否则容易出现所等待比较严重。
RR 隔离级别解决了不可重复读问题 + 幻读的现象。不可重复读问题是由 undo 的快照技术来解决。幻读现象是由 MVCC + GAP + next-lock 解决
例如:

[world]>select *


三、事务的生命周期（事务控制语句）

事务的开始

begin; / start transaction;


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

事务的结束

commit --提交事务


完成一个事务，一旦事务提交成功 ，就说明具备 ACID 特性了。

rollback --回滚事务


将内存中，已执行过的操作，回滚回去

四、自动提交策略（autocommit）

db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;
db01 [(none)]>set global autocommit=0;

注意：自动提交是否打开，一般在有事务需求的 MySQL 中，将其关闭。不管有没有事务需求，我们一般也都建议设置为 0，可以很大程度上提高数据库性能。

set autocommit=0; 
set global autocommit=0;
vim /etc/my.cnf
autocommit=0

五、事务的隐式控制

异常终止

delete from  where xxx
kill 5850692 --导致上面删除语句终止，失效

用于隐式提交的 SQL 语句

begin
a
b
begin 自动提交上一个事务
set autocomit = 1

导致提交的非事务语句

  • 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;

七、InnoDB 事务的 ACID 如何保证

7.1 一些概念


redo log 重做日志 （前滚日志）：ib_logfile0~ ib_logfile1 50M，轮询使用（大小和个数都可以调整，使用方式二进制，轮询去写日志）
redo log buffer：redo 内存区域（加载 redo）


city.ibd, order.idb...：存储数据行和索引
buffer pool：数据缓冲区池，数据和索引的缓冲

LSN 日志序列号：像 git 或者 svn 里面的版本号。磁盘数据页，redo 文件，buffer pool，redo buffer。MySQL 每次数据库启动，都会比较磁盘数据页和 redolog 的 LSN，必须要求两者 LSN 一致数据库才能正常启动。

WAL（write ahead log）日志优先写的方式实现持久化。优先日志 redo buffer 数据（日志）先写到磁盘 ib_logfile0，然后 buffer poll 内存数据写入 ibd 文件。

脏页：内存脏页，内存中发生了修改,没来得及写入到磁盘，我们把内存页称之为脏页。例如，idb 修改 name="张三" lsn=100。buffer pool 内存中修改 name="lisi" lsn=101，此时还没来得及写入磁盘 idb 就是脏页。
CKPT（Checkpoint）：检查点，就是将脏页刷写到磁盘的动作。
TXID 事务号：InnoDB 会为每一个事务生成一个事务号伴随着整个事务。正常情况下，再不断电，不宕机情况下流程。

7.2 redo log 介绍

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

Redo 作用是什么？在事务 ACID 过程中，实现的是“D”持久化的作用（数据库宕机不丢失数据）。对于 AC 也有相应的作用。

Redo日志位置：redo的日志文件 iblogfile0 iblogfile1

Redo buffer：redo的buffer 数据页的变化信息 + 数据页当时的 LSN 号。LSN 日志序列号 磁盘数据页、内存数据页、redo buffer、redolog。

7.3 redo 的刷新策略

commit;


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

八、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 日志写线程会将 redobuffer 信息写入 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 的"前滚操作"。

九、undo 回滚日志


undo 是什么？

undo 顾名思义“回滚日志”。

undo 作用是什么？

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

十、相关概念

10.1 Innodb 几个重点参数

缓冲区池

select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size


一般建议最多是物理内存的 75-80%。

10.2 innodb_flush_log_at_trx_commit（双一标准之一）

作用：

主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点，取值分别为 0、1、2 三个。控制了redo buffer 刷写策略，是一个安全参数，是在 5.6 版本以上默认的参数。

select @@innodb_flush_log_at_trx_commit; #innodb_flush_log_at_trx_commit=1

参数说明：

  • 1：每次事物的提交都会引起日志文件写入、flush 磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync 到物理磁盘
  • 0：表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入文件系统缓存并且每秒 fsync 磁盘一次；
  • 2：每次事务提交引起写入文件系统缓存,但每秒钟完成一次 fsync 磁盘操作。

设置了 redo buffer 刷写策略，是一个安全参数，是在 5.6 版本以上默认的参数。
参数功能：

  • 1：每次事务提交，都会立即刷下 redo 到磁盘（redo buffer --每次事务-->os buffer（操作系统缓存） --每次事务--写入磁盘）
  • 0：表示当事务提交时，不立即做日志写入操作（redo buffer --每秒-->os buffer （操作系统缓存）--每秒--磁盘）
  • 2：每次事务提交引起写入文件系统缓存（redo buffer --每事务-->os buffer（操作系统缓存） --每秒--磁盘）

10.3 Innodb_flush_method=(O_DIRECT, fdatasync)


作用：控制了 redo buffer 和 data bufffer 刷写磁盘方式

  • 控制的是 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

10.4 关于 redo 设置

innodb_log_buffer_size= 128M #业务系统CPU压力有关
innodb_log_file_size=256 #一般是1-2倍
innodb_log_files_in_group = 3 #3-4组

redo 日志有关的参数：

innodb_log_buffer_size = 16777216 #内存大小
innodb_log_file_size = 50331648  #文件大小
innodb_log_files_in_group = 3

转自：孙龙 程序员，

链接：cnblogs.com/sunlong88/p/16640387.html

标签：mysqltruncatetable 

作者:gojiuye , 分类:技术精选 , 浏览:16 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言