MySQL 事务篇
前情提要
数据库构成部分
- 客户端：jdbc / sqlyog
- 服务器 mysql server
-
连接器 – 管理连接、验证权限
-
分析器 – 语法分析、词法分析
-
优化器
-
执行器 – 执行具体的sql
-
- 存储引擎 (innodb、myisam) – 读写接口，可以理解为不同的文件数据组成格式
InnoDB 与 MyISAM 的区别
-
前者支持事务，后者不支持
- InnoDB 使用redo log 和 undo log来实现事务，是MySQL的默认引擎
- 我们常听到的两个日志redo log 和 undo log是
innodb独有的
-
前者支持聚簇索引，后者不支持聚簇索引，且可允许主键不存在
-
InnoDB 的文件格式有两个：一个是表结构 和 一个是包含数据和索引的文件
-
MyISAM 的文件格式有三个：一个是表结构、一个是数据文件、一个是索引文件
-
为什么InnoDB 是数据和索引放在一个文件，笔者认为由于InnoDB 支持聚簇索引，因聚簇索引的叶子节点存储是整行记录的特性，造就了数据和索引放在一个文件
-
为什么MyISAM可允许主键不存在，底层又是怎么实现索引的呢，笔者在网上找到了一篇解释MyISAM底层索引实现的博文
【图文结合】全网最全的MySQL索引讲解，万字长文由浅入深带你认识索引 - 知乎 (zhihu.com)
MyISAM的辅助索引(Secodary key)叶子节点存放的还是列值与行号的组合，叶子节点中保存的是指向真实数据的物理地址。主键索引可以当做是一个唯一、非空的索引,而辅助索引的key是可以重复的。
-
还有其他区别就不一一列举，如锁的最小粒度，前者为行锁，后者为表锁
事务特性
开始进入正题，前面已经说明InnoDB 支持事务，MyISAM 不支持，因而后面的篇幅都是围绕着InnoDB 的事务特性以及与之相关的内容进行展开。
ACID
-
原子性： 要么不做要么全做 ，通过undo log日志实现
undo log日志中记录了与sql语句相反的内容，如实际操作是删除，undo log相反添加该条记录
-
一致性：需要原子性、隔离性、持久性的保证来实现
-
隔离性：事务之间的操作相互独立，互不干扰，通过mvcc 和锁实现
-
持久性：事务提交后，对应的操作是永久保存在数据库文件中的，通过redo log日志实现
持久化的实现
为什么需要redo log
-
在进行数据更改的操作时，会先查看内存是否存在该条记录，如果不存在则从磁盘中读取，读取的单位是页（datapage)，默认是16kb
-
拿到数据后，先对内存中的数据进行修改
-
假设修改完毕之后，直接写入磁盘，这意味着一次io，如果仅仅修改了一条记录就刷新一个数据页就过于浪费了；但如果不刷新，此时修改后的数据还在内存，如果此时服务宕机就会造成数据的丢失
-
redo log就是那个保障的中间者：修改完后，不立即刷新，而是记录一条日志，等待
一个刷新的时机去写入磁盘每条记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成
-
所谓刷新的时机由一个参数控制：innodb_flush_log_at_trx_commit
- commit事务一提交就进行刷盘
- commit刷新近os的内核缓冲区，具体的刷盘时机不确定
- 后台线程，每1秒刷新一次到磁盘
redo log两阶段提交 – 前提是开启binlog
- prepare阶段，将数据修改到内存，写到redo log；
- commit阶段，写binlog并且将redo log的状态改成commit状态；
假设对ID = 2的数据进行修改
1、优化器找存储引擎取出ID=2这一行，如果ID=2这一行记录所在的数据页本身就在内存当中，那么就直接返回给执行器；否则需要从磁盘读取到内存当中，然后再返回给优化器；
2、优化器拿到行数据之后，会对内存中的数据页进行修改，同时将这个更新操作记录到Redo Log。此时Redo Log处于 perpare 状态，告知执行器已经完成，可随时提交事务；
3、接下来执行器会生成这个更新操作的binlog；
4、执行器调用存储引擎的提交事务的接口，将刚刚写入的Redo Log改成commit状态
mysql发生崩溃恢复的过程中，会根据redo log日志，结合 binlog 记录来做事务回滚：
- 如果redo log 和 binlog都存在，逻辑上一致，那么提交事务；
- 如果redo log存在而binlog不存在，逻辑上不一致，那么回滚事务；
binlog
- 是服务层的日志，记录逻辑语句，追加方式写，可以定期备份
与redo log日志的区别:
-
redo是innodb独有的，binlog是所有引擎都可以使用的
-
redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑
-
redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前的日志信息
隔离级别
- 未提交读 – 仍存在脏读 不可重复读 幻读问题
- 已提交读 – 仍存在不可重复读 幻读问题
- 可重复读 – 仍存在幻读问题 – innodb存储引擎默认隔离级别，使用next-key锁解决幻读问题
- 可串行化
MVCC 多版本并发控制
使隔离级别下执行一致性读操作有了保证，即为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。即含有不同版本，根据版本比较进行更新或放弃，在很多情况下避免了加锁操作，开销更低；只在已提交读和可重复读两个隔离级别下操作（不能解决写写时的更新丢失），用于快照读。
快照读 与 当前读
- 快照读：对数据的查询，读取到可能是最新版本也可能是历史版本
- select * from table ;
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert;
- update ;
- delete;
在内部，InnoDB向数据库中存储的每一行数据添加三个字段。
（1）DB_TRX_ID字段，6字节，事务id；表示创建或更新行的最后一个事务的事务标识符
（2）DB_ROLL_PTR字段，7字节，回滚指针（roll pointer）；回滚指针指向写入回滚段的撤消日志（Undo Log），如果行已更新，则撤消日志包含重建更新前该行内容所需的信息。
（3）DB_ROW_ID字段，6字节，隐藏主键，一个随着新行插入而单调增加的行ID，如果innodb对应的表没有主键且没有唯一字段，会以该字段为唯一标识，生成聚簇索引。
当插入的是一条新数据时，记录上对应的回滚段指针为NULL；
更新一条新的数据时
- 用排他锁锁定该行
- 把该行修改前的值拷贝到Undo Log中
- 修改当前行的值，填写事务编号，使回滚指针指向Undo Log中的修改前的行
- 记录Redo Log，包括Undo Log中的变化
回滚指针会把不同版本的记录串在一起。在InnoDB中存在purge线程，它会查询那些比现在最老的活动事务还早的Undo Log，并删除它们，从而保证Undo Log文件不至于无限增长。
回滚（rollback）需要根据当前回滚指针从Undo Log中找出事务修改前的版本，并恢复。
Read View 读视图
构成部分：
- creator_trx_id : 进行该查询的事务id
- trx_ids : 活跃事务id列表
- up_limit_id : 最小事务id
- low_limit_id ：预分配给接下来的事务id
根据可见性算法找到可见的数据版本： 对读取的DB_TRX_ID进行比较
-
首先比较
DB_TRX_ID< up_limit_id，如果小于，则说明该事务在此之前已经提交，当前事务能看到DB_TRX_ID所在的记录；如果大于等于进入下一个判断if (trx_id < view->up_limit_id) {
return(TRUE);}
-
接下来判断
DB_TRX_ID >= low_limit_id，如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见；如果小于，则进入下一步判断if (trx_id >= view->low_limit_id) {
return(FALSE);}
-
判断
DB_TRX_ID是否在活跃事务列表中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的
在 RC读已提交 和 RR可重复读 隔离级别下
- RC级别下，每次语句执行的过程，即每查询一次就生成一次Read View
- RR级别下，同一个事务中的第一次快照读才生成Read View
锁
- 按粒度分类
- 行锁
- 表锁
- 页锁
- 按是否加锁分类
- 乐观锁
- 悲观锁
- 按是否兼容分类
- 共享锁（读 S锁）
- 排他锁（写 X锁）
三种行锁：
-
记录锁 Record Lock
单个行记录上加锁，防止事务间修改或删除数据，Record Lock总是会去锁住索引记录，如果表建立的时候没有设置任何一个索引，InnoDB存储引擎会使用隐式的主键来进行锁定
-
间隙锁 Gap Lock
间隙锁，表示只锁住一段范围，不锁记录本身，通常表示两个索引记录之间，或者索引上的第一条记录之前，或者最后一条记录之后的锁
-
Next-Key Lock = Gap Lock + Record Lock
锁定一个范围及锁定记录本身。InnoDB使用该锁，在可重复读隔离级别下解决幻读问题，其中加锁都是给所有记录一条一条加锁，并没有一个直接的范围可以直接锁住，所以会生成多个区间
意向锁即为表级别的锁，两种意向锁： 仅Innodb存储引擎支持
- 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁
- 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁
由于InnoDB存储引擎支持行锁，因此意向锁不会阻塞除全表扫描以外的任何请求，意向锁是为了表示是否有请求锁定表中的某一行数据
如果没有意向锁，当已使用行锁对表中的某一行进行修改时，另一个请求要对全表进行修改，就需要对所有行是否被锁定进行扫描，这种情况下，效率是非常低的；
在引入意向锁之后，使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），如有其他请求需对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需通过等待意向互斥锁被释放
何时加锁 加什么锁
对于快照读不论何种级别何种情况均不加锁，因而此处对当前读及其对应的隔离级别进行讨论
对SQL语句 delete from table where id = 2
-
id是主键 + RC / RR级别
对id = 2的行记录加写锁 – X锁
-
id是唯一索引 + RC / RR级别
语句会选择走id列的唯一索引进行where条件的过滤，在找到id = 2的记录后，首先会对id = 2的记录加上X锁，同时会读取对应的主键值，回聚簇索引，然后将聚簇索引上对应的记录加X锁，防止有其他语句通过走其他索引对该记录进行操作
-
id是非唯一索引 + RC级别
语句会选择走id列的索引进行where条件的过滤，将所有满足查询条件的记录都加上X锁，同时会读取对应的主键值，回聚簇索引，然后将聚簇索引上对应的记录加X锁，防止有其他语句通过走其他索引对该记录进行操作
-
id是非唯一索引 + RR级别
与上一组合区别在于会加GAP间隙锁；
语句会选择走id列的索引进行where条件的过滤，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，记录间加GAP锁，聚簇索引的记录加X锁，重复进行；直至进行到第一条不满足条件的记录，不需要加记录X锁，但是仍旧需要加GAP锁 – 即不仅锁住记录本身还在满足条件的记录加上gap锁，防止幻读
-
id无索引 + RC级别
由于id无索引，会选择走全表扫描，即对所有记录都加上X锁；如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 MySQL Server 层进行过滤
-
id无索引 + RR级别
由于id无索引，会选择走全表扫描，即对所有记录都加上X锁且在记录间加上gap锁
3732
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
