MySQL总结（五）事务ACID的底层实现原理
隔离性由锁实现
redo log 实现 持久性
undo log 实现一致性，原子性
1. Why undo log?
数据库和内存之间进行IO的时候，事务正在进行，如果数据库宕机，那这时候数据就会不一致，就要回滚到以前的数据，undo log就是实现这个功能的。
undo log 记录的是数据原来的值，所以只需要把对数据库的逆操作记录就行了
如
-
insert 一条记录，我们就delete -
update 一条记录， 我们就把它以前的值记下来update -
delete 一条记录，我们就insert
undo能实现啥？
-
原子性 -
MVCC(多版本并发控制)后边会介绍
2. Buffer pool
当我修改数据库的记录时，会把这条记录先加载到缓存中，修改缓存的记录，所在的页为脏页（与磁盘的数据不一致），不会立刻写入磁盘，会等待一个时机写入磁盘。
读数据时也会先到缓存中找，没有的话会到磁盘中找。
3. Why Redo log？
上边说到，修改后的数据不会立刻写入磁盘，但内存级别是不安全的，万一停电了，数据就没了。事务要有持久性。
那可以直接把修改的数据更新到磁盘，不在缓冲池？
这样会增加大量的IO操作，而且还是随机IO
redo log file 是顺序写入的，是顺序IO
redo log file 记录的是 xx表空间xx页xx偏移量处修改为xx。
redo log 也不是直接写入磁盘，有一个redo log buffer 在一定时机下会写入磁盘
MySQL把对底层页面中的一次原子访问过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志可以作为一个不可分割的整体。
写入磁盘
一下情况会写入从redo log buffer 写入 redo log file
-
MySQL 正常关闭时；
-
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
-
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
-
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（
innodb_flush_log_at_trx_commit）innodb_flush_log_at_trx_commit= 0 时，每次提交事务时，redo log 不会写入磁盘innodb_flush_log_at_trx_commit= 1 时，每次提交事务时，redo log 写入磁盘innodb_flush_log_at_trx_commit= 2 时，每次提交事务时，从redo log buffer 写入redo log，但是不写入磁盘
锁
锁有表，页，行锁之分
读写锁
select column from table for select # 加共享锁
select column from table for update # 加排他锁
1. 表锁
-
S锁和X锁
-
S锁是读锁，也叫共享锁 -
X锁是写锁，也叫排他锁
加锁
LOCK TABLES t READ #InnoDB存储引擎会对表 t 加表级别的 `S锁 `。
LOCK TABLES t WRITE #InnoDB存储引擎会对表 t 加表级别的 `X锁` 。
兼容性
| 锁 | S | X |
|---|---|---|
| S | yes | no |
| X | no | no |
-
意向锁
InnoDB 支持
多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。-
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁并存。 -
意向锁是一种 不与行级锁冲突表级锁，这一点非常重要。 -
表明 某个事务正在某些行持有了锁或该事务准备去持有锁
-
意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 SELECT column FROM table ... LOCK IN SHARE MODE; -
意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 SELECT column FROM table ... FOR UPDATE;
即：意向锁是由存储引擎
自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前， InooDB 会先获取该数据行所在数据表的对应意向锁。作用：
如果有行锁了，另一个事务想加一个表锁。没有意向锁的话，就要看有没有页锁，有没有行锁。
有意向锁的话，就立刻知道有锁，不要在加了。
意向锁之间全部兼容
锁 IS IX IS YES YES IX YES YES 意向锁和S以及X锁的兼容性
锁 IS IX S YES NO X NO NO 结论
-
InnoDB 支持 多粒度锁，特定场景下，行级锁可以与表级锁共存。 -
意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥。 -
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。 -
意向锁在保证并发性的前提下，实现了 行锁和表锁共存且满足事务隔离性的要求。
-
-
自增锁
对有
auto_increment的字段，三种insert-
simple inserts预先知道要插入的行数
-
bulk inserts不知道插入的行数，一行一行的处理
-
mixed-mode inserts一部分有，一部分没有values(null， 'lang'，20)，(2，'laaang'，23)；
这时候是混合模式
-
-
元数据锁
当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删查改。读写锁之间、写锁之间都是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。
不需要显式使用，在访问一个表的时候会被自动加上。
2. 行锁
-
记录锁（Record locks）
事务A
mysql> select * from stu; +----+------+------+ | id | name | age | +----+------+------+ | 1 | lang | 1 | | 2 | he | 18 | | 3 | zhao | 18 | +----+------+------+ 3 rows in set (0.01 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> update stu set name = 'laaang' where id = 2; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0事务B
mysql> select * from stu; +----+------+------+ | id | name | age | +----+------+------+ | 1 | lang | 1 | | 2 | he | 18 | | 3 | zhao | 18 | +----+------+------+ 3 rows in set (0.01 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> update stu set name = 'Zhao' where id = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update stu set name = 'lang' where id = 2; # 阻塞记录锁是有S锁和X锁之分的，称之为
S型记录锁和X型记录锁。-
当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁； -
当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。
-
-
间隙锁（Gap Locks）
mysql> select * from stu; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | lang | 1 | | 2 | laaang | 18 | | 3 | zhao | 18 | | 5 | lang | 22 | +----+--------+------+ 4 rows in set (0.00 sec)在这个表中，如果给id = 5 的记录加上间隙锁，在（3，5）之家是加不上数据的。
加锁
select * from stu where id = 5 lock in share mode; select * from stu where id = 5 for update;这样加的是在一个区间（3，5）那再后边的区间不让insert咋办
-
Infimum记录，表示该页面中最小的记录。 -
Supremum记录，表示该页面中最大的记录。
在supermum的记录上加锁，就是（，+∞）不可以insert
-
-
临键锁（next-key locks）
next-key锁的本质就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。begin; select * from stu where id <=8 and id > 3 for update; -
插入意向锁（insert intention locks）
悲观锁（Pessimistic Locking）
悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。
乐观锁（Optimistic Locking）
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量。
-
版本号实现
加入version字段
# 当修改字段时,判断一下版本号，所以现需要查一下version select version from table UPDATE ... SET version=version+1 WHERE version=version;
-
乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。 -
悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。
死锁
两个事务都有锁，并且需要对方先释放锁；
MVCC
Multiversion Concurrency Control 多版本并发控制。MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。
-
快照读 -
当前读
ReadView
用到了undo
字段：
-
creator_trx_id：创建这个 Read View 的事务 ID。只有修改操作才会分配这个属性。
-
trx_ids：事务id列表 -
up_limit_id：列表最小的事务id -
low_limit_id：表示生成ReadView时系统中应该分配给下一个事务的 id 值，也就是以前有过的最大的id+1；如果列表有1， 2， 3那么此字段就是不一定时4，因为4事务可能是已经有了，但是提交过了，这个字段必须是列表没有出现过的，下一次将要进来的。
readview的操作流程
当前事务id用trx_id表示
-
trx_id=creator_trx_id是当前的事务，(当前事务访问他自己修改的数据)可以访问
-
trx_id<up_limit_id已经提交的事务，可以访问
-
trx_id>=low_limit_id当前readview创建的事务，不可以访问
-
trx_id属于up_limit_id和low_limit_id之间在 ：列表中，活跃事务，不可以访问。
不在：列表中，已经提交的事务，可以访问。
Example
-
READ COMMITTEDREAD COMMITTED每一次SELECT都会有新的raedview注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。
-
REPEATABLE READ只生成一个readview
总结
-
READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView -
REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复 使用这个ReadView就好了。
本文由 mdnice 多平台发布
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
