MySQL原理分析MySQL事务深入分析事务事务是数据库管理系统(DBMS)执行过程中的一个有限的数据库操作序列(D

MySQL事务深入分析

事务

事务是数据库管理系统(DBMS)执行过程中的一个有限的数据库操作序列(DML)组合而成的逻辑执行单位。例：上厕所（脱裤子、排泄、穿裤子）----> 上完了（事务结束）。

事务的特点(ACID)

原子性(Atomicity)
- 事务中的所有操作都是一起执行的不可分割
- 通过undolog实现
一致性(Consistency)
- 数据库在事务执行前后状态保持一致
- 常用的手段从业务逻辑上保证数据一致性
隔离性(Isolation)
- 事务之间不会相互影响
- Mysql自身的锁机制和MVCC机制实现了隔离
永久性(Durability)
- 事务执行完成以后，事务里的所有操作都永久的保存在数据库中

在并发场景下事务可能产生一些问题

脏读：查询到其它事务还没有提交的数据，由于其它事务可能提交失败或者回滚，导致当前事务读取数据不正确
不可重复读：当前事务多次读取时，由于其它事务提交了修改数据导致当前事务多次读取的数据不一致。
幻读：当前事务多次查询多条数据时，由于其它事务提交了数据，导致查询到的多条数据结果集不一致。

事务隔离级别

读未提交 (READ UNCOMMITED)
- 脏读、幻读、不可重复读均有可能发生
读已提交 (READ COMMITED)
- 幻读、不可重复读可能发生
可重复读 (REPEATABLE READ) --- MySQL默认隔离级别
- 对于InnoDB 均不可能发生，MyISAM 可能产生幻读
串行化 (SERIALIZABLE)
- 均不可能产生

MySQL 数据一致性解决方案

LBCC(Lock Based Concurrency Control) 基于锁（间隙锁、记录锁、临键锁）的并发控制。
MVCC(Multi Version Concurrency Control) 读取数据（基于快照读方式）时不对数据加锁，提高了数据库的并发处理能力

MySQL锁机制深入分析

锁的类型
- 粒度划分
  - 表锁
  - 行锁
  - 表锁的粒度大于行锁，加锁效率也比行锁快，冲突概率比行锁要大，但并发性能要比行锁小
- 类型划分
  - 共享锁 Shared Locks（行锁）
    - 读锁，不同事务共享同一条数据，都有读的权限
  - 排他锁 Exclusive Locks（行锁）
    - 写锁，排斥其它的锁，当有事务获取了某一行数据的写锁，除该事务外，其它事务不可读也不可改。
    - 一般mysql会默认对 insert update delete 的语句增加排他锁
    - 程序员可以通过 FOR UPDATE 给一行数据手动加排他锁
  - 意向锁 Intention Shared | Exclusive Lock（表锁，又分为意向共享和意向排他）
    - 由数据引擎维护，无法手动操作。
    - 数据行加共享锁前必须取得该表的意向共享锁
    - 数据行加排他锁前必须取得该表的意向排他锁
    - 简单总结就是加锁前先要通过表级意向排他锁，得知数据没有其它锁的时候才能对数据行加锁
锁的本质
- 本质上对表和数据的锁时锁住表的索引
- 如表定义了主键，则InnoDB以主键作为聚簇索引
- 如表未定义主键，则InnoDB会选择一个不包含null的值的唯一索引作为主键索引
- 如表无唯一索引，则InnoDB会选择内置的 RowID(6byte)
- 表中没有索引时，锁住一行数据会导致锁全表
- 表中有唯一索时，会找到唯一索引的主键索引通过回表查询找到数据加锁
锁的算法
- 记录锁
  - 锁定具体某行记录，通过（唯一/主键索引）精确匹配
- 间隙锁
  - 锁定范围，当查询记录不存在时使用间隙锁以防止插入语句insert,间隙锁之间互不冲突。
  - 间隙锁将表数据按范围区分，间隙范围不可分割
  - 例如，假定表中有数据5和数据11，查询记录为8的记录不存在时，间隙锁会锁住5到11这个范围不允许插入，直到查询完成间隙锁释放
- 临键锁
  - 包含记录和区间，与间隙锁的区别是包含了范围内具体的记录行

死锁
- 锁的阻塞和释放
  - show variables like "innodb_lock_wait_timeout" 查看mysql锁等待超时时间
- 死锁的发生和检测
  - mysql有死锁检测机制，当满足条件时会报错
  - 死锁发生的条件：互斥、不可剥夺、循环等待
- 死锁的避免
  - 顺序访问
  - 数据排序
  - 避免没有where条件的操作
  - 事务分解
  - 优先使用等值查询，避免范围查询

MySQL的日志原理和MVCC

通过数据更新分析日志原理

日志数据的产生
- 数据库在更新时，会产生binlog 、 redolog 、 undolog
  - binlog：是server层产生的逻辑日志，用于进行数据复制。
    - 进行数据复制和数据传送，例如主从备份
    - 完整记录了数据库每次数据操作
    - 记录在专门的文件中
  - redolog：是InnoDB产生的物理日志，保证持久化
    - InnoDB产生的物理日志，记录数据页的变化
    - InnoDB '日志优先于数据'，记录redolog 视为数据已经更新
    - 内存中的数据更新后写redo.log,数据被写入硬盘后删除
    - redo.log存储在4个1GB文件中，并且循环写入
  - redolog的工作
    - write pos 是当前日志写入点
    - checkpoint是擦除点，数据被更新到硬盘时擦除
    - 当write pos 追上checkpoint时，事务无法提交，需等待checkpoint推进
    - 只要redolog不丢数据就不会丢失
  - redolog工作的时机
    - InnoDB的redo log 写满了
    - 对应的系统内存不足
    - MySQL认为系统空闲的时候
    - MySQL正常关闭的情况下
  - undolog：是InnoDB产生的逻辑日志，保证隔离性、原子性。快照就是undolog处理的
    - InnoDB里面每个事务有一个唯一的事务ID，transactionId
    - InnoDB产生的逻辑日志用于事务回滚和旧版本的展示（快照）
      - 快照读取时，读取最新事务ID的上一个事务ID的数据
    - 对任何数据（包括缓存）的更新，都先写入undolog
    - undolog位于表空间的rollback segment中

数据更新流程

Client对MySQL Server 发送 update 请求
MySQL Server查询数据，InnoDB操作数据页从磁盘读入内存，从页中取出数据返回给MySQL
MySQL Server 修改行数据，InnoDB接收修改，写如undo log,更新内存中的数据页，redolog写入内容。
Client 提交事务，binlog写入内存，InnoDB提交事务

redolog刷盘

innodb_flush_log_at_trx_commit 参数控制redo log 刷盘。取值如下

0：异步每秒刷盘
1：每个事务刷盘 [默认值] 建议使用，保障数据安全
N：N个事务刷盘

binlog刷盘

sync_binlog 参数控制binlog 刷盘。取值如下

0：自动控制刷盘
1：每个事务刷盘 [默认值] 建议使用，保障数据安全
N：N个事务刷盘

持久化分析

redolog刷盘前系统崩溃：数据丢失
redolog刷盘后系统崩溃：重启时会对redolog进行重放、重写内存中数据页、重写binlog

redolog在 binlog之前的原因

redo log时系统关键节点，起决断性作用
binlog一旦写入无法撤回，因为可能已被同步至备份库

行记录的版本控制

由于undolog的存在，可以从最新版本推算之前的版本

快照读（一致性非锁定读）

不锁定数据时，读取数据的指定历史版本
版本根据事务具体需求确定
- 读已提交：根据每次SELECT时，其他事务的提交情况
- 可重复读：根据事务开始时，其他事务的提交情况

当前读 读取数据的当前版本，并加锁若当前版本已被加锁且不兼容，则阻塞等待 X锁：UPDATE、DELETE 、 SELECT FOR 、 UPDATE S锁：SELECT IN SHARE MODE

MySQL原理分析