MySQL事务概念基础

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

标准事务隔离级别

• 读未提交（read-uncommitted），对应脏读

当一个事务被允许读取另外一个事务修改但未被提交的数据时，会发生脏读。

• 不可重复读（read-committed）

当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不可重复读。

• 可重复读（repeatable-read），对应幻读

在事务执行过程中，另一个事务将新纪录添加到正在读取的事务中，会发生幻读。

• 串行化（serializable）

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

Redo log 使用监控

• 执行SHOW engine innodb

LOG
Log sequence number 602763740 	// Redo日志缓冲区中的LSN
Log flushed up to 602763740 		// Redo日志文件中的LSN
Pages flushed up to 584668961 	// 写入磁盘中的数据页对应的LSN
Last checkpoint at 555157885 		// 最后一个CheckPoint对应的LSN
复制

• INNODB_METRICS

SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME IN ('log_lsn_current', 'log_lsn_last_checkpoint'); 
+-------------------------+-----------+
| NAME | COUNT | 
+-------------------------+-----------+
| log_lsn_last_checkpoint | 555157885 |
| log_lsn_current | 602763740 | 
+-------------------------+-----------+
复制

• sys schema（系统库）

SELECT *FROM sys.metrics WHERE Variable_name IN ('log_lsn_current','log_lsn_ last_checkpoint');
+-----------------------+----------------+-------------------------+---------+ 
| Variable_name | Variable_value | Type | Enabled | 
+-----------------------+----------------+-------------------------+---------+ 
| log_lsn_current | 602763740 | InnoDB Metrics - recovery | YES |
| log_lsn_last_checkpoint | 555157885 | InnoDB Metrics - recovery | YES |
+-----------------------+----------------+-------------------------+---------+

Used log = log_lsn_current – log_lsn_last_checkpoint = 602763740 - 555157885
= 47605855 (bytes)
Used % = (Used log / Total log) * 100
= (47605855 / (innodb_log_file_size * innodb_log_files_in_group)) * 100 = (47605855 / 100663296) * 100
= 47.29 %
复制

• 查看每分钟产生的Redo日志量，可以通过如下方式：（建议平均半个小时写满1个日志文件比较合适）

pager grep sequence
show engine innodb status\Gselect sleep(60);show engine innodb status\G
nopager

select round((number1-number2)/1024/1024) as MB;
复制

WAL日志先写

当执行提交事务之后，并不是直接修改到数据文件中的，而是先保证将相关的操作日志记录到Redo日志文件中，数据库后台会根据自身的机制将内存中的脏数据刷新到磁盘中。

之所以这么做，有2点原因：

• 写入数据时随机I/O比较慢，用户执行提交之后一定要等到数据文件同步写入完成才返回成功，用户的感受会比较差。
• 如果提交一个很大的事务，要进行大量的随机I/O同步写入，那么数据库就会有抖动。

综合以上两点考虑引入Redo日志，每次提交事务之后，都先将相关的操作日志写入Redo日志文件中，并且都追加到文件末尾，这是一个顺序I/O，这样将每次同步的随机I/O转换为顺序I/O，对脏数据的刷新在后台可以进行阶段性操作，并且可以做相关的I/O合并，使整体性能得到提升。

事务特性

原子性

在 MySQL 中，任何 Buffer Pool 中的页被刷到磁盘之前，都会先写入到日志文件中，这样做有两方面的保证:

1. 如果 Buffer Pool 中的这个页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写下去的数据不会丢失，所以必须要保证 Redo 先写。
2. 因为 Buffer Pool 的空间是有限的，要载入新页时，需要从 LRU 链表中淘汰一些页，而这些页必须要刷盘之后，才可以重新使用，那这时的刷盘，就需要保证对应的 LSN 的日志也要提前写到 ib_logfiles 中，如果没有写的话，恰巧这个事务又没有提交，数据库挂了，在数据库启动之后，这个事务就没法回滚了。所以如果不写日志的话，这些数据对应的回滚日志可能就不存在，导致未提交的事务回滚不了，从而不能保证原子性，所以原子性就是通过 WAL 来保证的。

持久性

如下图所示，一个“提交”动作触发的操作有：binlog 落地、发送 binlog、存储引擎提交、flush_logs， check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。