数据库事务、锁、死锁、redo log、binlog、undo log
本文转发： https://www.cnblogs.com/xuwc/p/14006112.html
INNODB是如何实现事务的？
1、什么是事务
通俗来说就是一组SQL语句，而且这组SQL要么同时都执行成功要么同时都不成功。
事务的特性：
| 特征 | 说明 |
|---|---|
| 原子性（A） | 一个事务中的所有操作，要么全都成功，要么全都不成功，不会结束在中间某个环节； |
| 一致性（C） | 事务开始之前和结束之后，数据库的完整性没有被破坏； |
| 隔离性（I） | 要求每个读写事务的操作对象与其他事务的操作对象能相互隔离； |
| 持久性（D） | 事务一旦提交，其结果就会持久化，就算发生宕机也能恢复数据； |
2、InnoDB存储引擎对ACID的实现方式
利用回滚日志（undo log） 和 重做日志（redo log） 两种表实现事务，并实现 MVCC (多版本并发控制)；
在执行事务的每条SQL时，会先将数据原值写入undo log 中， 然后执行SQL对数据进行修改，最后将修改后的值写入redo log中。
redo log 重做日志包括两部分：1 是内存中的重做日志缓冲 ；2 是重做日志文件。在事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务commit操作完成才算完成。
当一个事务中的所有SQL都执行成功后，会将redo log 缓存中的数据刷入磁盘，然后提交。
如果发生回滚，会根据undo log 恢复数据。
| 特征 | INNODB实现方式 |
|---|---|
| 原子性（A） | 回滚日志（undo log）：用于记录数据修改前的状态； |
| 一致性（C） | 重做日志（redo log）：用于记录市局修改后的状态； |
| 隔离性（I） | 锁：用于资源隔离，分为共享锁和排它锁； |
| 持久性（D） | 重做日志（redo log） + 回滚日志（undo log）； |
3、MVCC 多版本并发控制
查询需要对资源加共享锁（S），数据修改需要对资源加排他锁（X）
| 排他锁 | 共享锁 | |
|---|---|---|
| 排他锁 | 不兼容 | 不兼容 |
| 共享锁 | 不兼容 | 兼容 |
利用undo log使读写不阻塞，实现了可重复读。当一个事务正在对一条数据进行修改时，该资源会被加上排它锁。在事务未提交时对加锁资源进行读操作时，读操作无法读到被锁资源，通过一些特殊的标志符去读undo log 中的数据（过程很复杂），这样读到的都是事务执行之前的数据。
MySQL InnoDB如何保证事务特性
如果有人问你“数据库事务有哪些特性”？你可能会很快回答出原子性、一致性、隔离性、持久性即ACID特性。那么你知道InnoDB如何保证这些事务特性的吗？如果知道的话这篇文章就可以直接跳过不看啦(#.#)
先说结论：
- redo log重做日志用来保证事务的持久性
- undo log回滚日志保证事务的原子性
- undo log+redo log保证事务的一致性
- 锁（共享、排他）用来保证事务的隔离性
重做日志 redo log
重做日志 redo log 分为两部分：一部分是内存中的重做日志缓冲（redo log buffer），是易丢失的；二部分是重做日志文件（redo log file），是持久的。InnoDB通过Force Log at Commit机制来实现持久性，当commit时，必须先将事务的所有日志写到重做日志文件进行持久化，待commit操作完成才算完成。
InnoDB在下面情况下会将重做日志缓冲的内容写入重做日志文件：
- master thread 每一秒将重做日志缓冲刷新到重做日志文件;
- 每个事务提交时
- 当重做日志缓冲池剩余空间小于1/2时
为了确保每次日志都写入重做日志文件，在每次将日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync（刷盘）操作。但这也不是绝对的。用户可以通过修改innodb_flush_log_at_trx_commoit参数来控制重做日志刷新到磁盘的策略，这个可以作为大量事务提交时的优化点。
-
1参数默认值，表示事务提交时必须调用一次fsync操作。
-
0表示事务提交时，重做日志缓存并不立即写入重做日志文件，而是随着Master Thread的间隔进行fsync操作。
-
2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。
fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。所以如果有人问你如何优化Mysql数据库的时候别忘了有硬件这一条，让他们提升硬盘配置，换SSD固态硬盘
重做日志都是以512字节进行存储的，称之为重做日志块，与磁盘扇区大小一致，这意味着重做日志的写入可以保证原子性，不需要doublewrite技术。它有以下3个特性： -
重做日志是在InnoDB层产生的
-
重做日志是物理格式日志，记录的是对每个页的修改
-
重做日志在事务进行中不断被写入，而且是顺序写入
回滚日志 undo log
为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log），然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
undo log实现多版本并发控制（MVCC）来辅助保证事务的隔离性。
回滚日志不同于重做日志，它是逻辑日志，对数据库的修改都逻辑的取消了。当事务回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎都会完成一个DELETE；对于每个UPDATE，InnoDB存储引擎都会执行一个相反的UPDATE。
事务提交后并不能马上删除undo log，这是因为可能还有其他事务需要通过undo log 来得到行记录之前的版本。故事务提交时将undo log 放入一个链表中，是否可以删除undo log 根据操作不同分以下2种情况：
- Insert undo log： insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求),故该undo log可以在事务提交后直接删除。不需要进行 purge操作。
- update undo log：记录的是对 delete和 update操作产生的 undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表,等待 purge线程进行最后的删除。
锁
事务的隔离性的实现原理就是锁，因而隔离性也可以称为并发控制、锁等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能互相分离。再者，比如操作缓冲池中的LRU列表，删除，添加、移动LRU列表中的元素，为了保证一致性那么就要锁的介入。
锁的类型
InnoDB主要有2种锁：行级锁，意向锁
行级锁：
- 共享锁（读锁 S），允许事务读一行数据。事务拿到某一行记录的共享S锁，才可以读取这一行，并阻止别的事务对其添加X锁。共享锁的目的是提高读读并发。
- 排它锁（写锁 X），允许事务删除一行数据或者更新一行数据。事务拿到某一行记录的排它X锁，才可以修改或者删除这一行。排他锁的目的是为了保证数据的一致性。
行级锁中，除了S和S兼容，其他都不兼容。
意向锁：
- 意向共享锁（读锁 IS ），事务想要获取一张表的几行数据的共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（写锁 IX），事务想要获取一张表中几行数据的排它锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
解释一下意向锁：- The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.
- 意向锁的主要用途是为了表达某个事务正在锁定一行或者将要锁定一行数据。e.g:事务A要对一行记录r进行上X锁，那么InnoDB会先申请表的IX锁，再锁定记录r的X锁。在事务A完成之前，事务B想要来个全表操作，此时直接在表级别的IX就告诉事务B需要等待而不需要在表上判断每一行是否有锁。意向排它锁存在的价值在于节约InnoDB对于锁的定位和处理性能。另外注意了，除了全表扫描以外意向锁都不会阻塞。
锁的算法
InnoDB有三种行锁的算法：
- Record Lock：单个行记录上的锁
- Gap Lock：间隙锁，锁定一个范围，而非记录本身
- Next-Key Lock：结合Gap Lock和Record Lock，锁定一个范围，并且锁定记录本身。主要解决的问题是REPEATABLE READ隔离级别下的幻读。可以参考文章了解事务隔离级别的相关知识点。
这里主要讲一下Next-Key Lock，利用Next-key Lock锁定的不是单个值而是一个范围，他的目的就是为了阻止多个事务将记录插入到同一范围内从而导致幻读。
注意了，如果走唯一索引，那么Next-Key Lock会降级为Record Lock，即仅锁住索引本身，而不是范围。也就是说Next-Key Lock前置条件为事务隔离级别为RR且查询的索引走的非唯一索引、主键索引。
下面我们用个例子详细说一下。
首先建立一张表：
CREATE TABLE T (id int ,f_id int,PRIMARY KEY (id), KEY(f_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into T SELECT 1,1;
insert into T SELECT 3,1;
insert into T SELECT 5,3;
insert into T SELECT 7,6;
insert into T SELECT 10,8;
事务A执行如下语句：
SELECT * FROM T WHERE f_id = 3 FOR UPDATE
这时SQL语句走非唯一索引，因此使用Next-Key Locking加锁，并且有2个索引，其需要分别进行锁定。
对于聚集索引，其仅对id等于5的索引加上Record Lock。而对于辅助索引，其加上Next-Key Lock，锁定了范围（1,3），特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上Gap Lock，即范围（3.6）的锁。
所以如果在新session中执行如下语句都会报错[Err] 1205 - Lock wait timeout exceeded; try restarting transaction：
select * from T where id = 5 lock in share MODE -- 不能执行，因为事务A已经给id=5的值加上了X锁，执行会被阻塞
INSERT INTO T SELECT 4,2 -- 不能执行，辅助索引的值为2，在（1,3）的范围内，执行阻塞
INSERT INTO T SELECT 6,5 -- 不能执行，gap锁会锁住（3,6）的范围，执行阻塞
此时想象一下，事务A锁定了f_id =5 的记录， 正常会有个gap lock，锁住（5,6），那么如果没有（5,6）的gap锁，那么用户可以插入索引 f_id 为5的记录，这样事务A再次查询就会返回一个不同的记录，也就导致了幻读的产生。
同理，如果我们事务A执行的是select * from T where f_id = 10 FOR UPDATE,在表里查不到数据，但是基于Next-Key Lock会锁住（8，+∞），我们执行INSERT INTO T SELECT 6,11是无法插入成功的，这就从根本上解决了幻读问题。
innodb下的记录锁，间隙锁，next-key锁
之前我们介绍了排他锁，其实innodb下的记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁。
-
行锁
记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。 -
生活中的间隙锁
编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。
这里的小红，小明，小花，小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。 -
Mysql中的间隙锁
下表中（见图一），id为主键，number字段上有非唯一索引的二级索引，有什么方式可以让该表不能再插入number=5的记录？
图一
根据上面生活中的例子，我们自然而然可以想到，只要控制几个点，number=5之前不能插入记录，number=5现有的记录之间不能再插入新的记录，number=5之后不能插入新的记录，那么新的number=5的记录将不能被插入进来。
那么，mysql是如何控制number=5之前，之中，之后不能有新的记录插入呢（防止幻读）？
答案是用间隙锁，在RR级别下，mysql通过间隙锁可以实现锁定number=5之前的间隙，number=5记录之间的间隙，number=5之后的间隙，从而使的新的记录无法被插入进来。
间隙是怎么划分的？
***注\***：为了方面理解，我们规定（id=A,number=B）代表一条字段id=A,字段number=B的记录，（C，D）代表一个区间，代表C-D这个区间范围。
图一中，根据number列，我们可以分为几个区间：（无穷小，2），（2，4），（4，5），（5，5），（5,11），（11，无穷大）。
只要这些区间对应的两个临界记录中间可以插入记录，就认为区间对应的记录之间有间隙。
例如：区间（2，4）分别对应的临界记录是（id=1,number=2），（id=3，number=4），这两条记录中间可以插入（id=2,number=3）等记录，那么就认为（id=1,number=2）与（id=3，number=4）之间存在间隙。
很多人会问，那记录（id=6，number=5）与（id=8，number=5）之间有间隙吗？
答案是有的，（id=6，number=5）与（id=8，number=5）之间可以插入记录（id=7，number=5），因此（id=6,number=5）与（id=8,number=5）之间有间隙的，
****间隙锁锁定的区域****
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。
图一中，where number=5的话，那么间隙锁的区间范围为（4,11）；
****间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：****
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）
****innodb自动使用间隙锁的条件：****
（1）必须在RR级别下
（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）
****接下来，通过实际操作观察下间隙锁的作用范围****
图三 表结构
**** 案例一：****
````
session 1:
start transaction ;
select * from news where number=4 for update ;
session 2:
start transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（执行成功）
insert into news value(9,5);#（执行成功）
insert into news value(11,5);#（执行成功）
````
检索条件number=4,向左取得最靠近的值2作为左区间，向右取得最靠近的5作为右区间，因此，session 1的间隙锁的范围（2，4），（4，5），如下图所示：
间隙锁锁定的区间为（2，4），（4，5），即记录（id=1,number=2）和记录（id=3,number=4）之间间隙会被锁定，记录（id=3,number=4）和记录（id=6,number=5）之间间隙被锁定。
因此记录（id=2,number=4），（id=2,number=2），（id=4,number=4），（id=4,number=5）正好处在（id=3,number=4）和（id=6,number=5）之间，所以插入不了，需要等待锁的释放，而记录(id=7,number=5)，（id=9,number=5），（id=11,number=5）不在上述锁定的范围内，因此都会插入成功。
案例二：
````
session 1:
start transaction ;
select * from news where number=13 for update ;
session 2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞)
update news set id=11 where number=11;#(执行成功)
````
检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大），如下图所示：
此表中没有number=13的记录的，innodb依然会为该记录左右两侧加间隙锁，间隙锁的范围（11，无穷大）。
有人会问，为啥update news set id=14 where number=11会阻塞，但是update news set id=11 where number=11却执行成功呢？
间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，此图间隙锁锁定的区域是（11，无穷大），也就是记录（id=13,number=11）之后不能再插入记录，update news set id=14 where number=11这条语句如果执行的话，将会被插入到（id=13,number=11）的后面，也就是在区间（11，无穷大）之间，由于该区间被间隙锁锁定，所以只能阻塞等待，而update news set id=11 where number=11执行后是会被插入到（id=13,number=11）的记录前面，也就不在（11，无穷大）的范围内，所以无需等待，执行成功。
案例三：
````
session 1:
start transaction ;
select * from news where number=5 for update;
session 2:
start transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)
insert into news value(9,12);#(执行成功)
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#（执行成功）
update news set id=4 where number=4 ;#（阻塞）
````
检索条件number=5,向左取得最靠近的值4作为左区间，向右取得11为右区间，因此，session 1的间隙锁的范围（4，5），（5，11），如下图所示：
有人会问，为啥insert into news value(9,12)会执行成功？间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，（id=9,number=12）很明显在记录（13,11）的后面，因此不再锁定的间隙范围内。
为啥update news set number=5 where id=1会阻塞？
number=5的记录的前面，后面包括中间都被封锁了，你这个update news set number=5 where id=1根本没法执行，因为innodb已经把你可以存放的位置都锁定了，因为只能等待。
同理，update news set id=11 where number=11由于记录（id=10,number=5）与记录（id=13,number=11）中间的间隙被封锁了，你这句sql也没法执行，必须等待，因为存放的位置被封锁了。
案例四：
session 1:
start transaction;
select * from news where number>4 for update;
session 2:
start transaction;
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(执行成功)
insert into news value(null,13);#(阻塞)
检索条件number>4,向左取得最靠近的值4作为左区间，向右取无穷大，因此，session 1的间隙锁的范围（4，无穷大），如下图所示：
session2中之所以有些阻塞，有些执行成功，其实就是因为插入的区域被锁定，从而阻塞。
next-key锁
next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是：select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁。
MySQL InnoDB中的行锁 Next-Key Lock消除幻读
InnoDB中有三种行锁技术：
1. Record Lock：单个行记录上的锁，我们通常讲的行锁，它的实质是通过对索引的加锁实现；只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。在事务隔离级别为读已提交下，仅采用Record Lock。
2. Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
3. Next-Key Lock：Record Lock+Gap Lock，锁定一个范围，并且锁定记录本身
1. Next-Key Lock
Next-Key Lock是结合Record Lock与Gap Lock的一种锁定方法，它锁定了包括记录本身的一个范围。
| id | name |
|---|---|
| 10 | a |
| 20 | b |
| 50 | c |
如果索引为 10，20，50，那么：
Record Lock：select * from tab where id = 10 for update; //对id=10单行进行加锁
Gap Lock锁范围：（- ∞ \infty∞，10）（10，20）（20，50）（50，+∞ \infty∞）
Next-Key Lock锁范围：（- ∞ \infty∞，10] (10，20] (20，50] (50，+∞ \infty∞）
| 事务A | 事务B |
|---|---|
| set autocommit=0; | |
| select * from tab where id>10 for update; //查询结果为20,50 | |
| select * from tab where id=10 for update;//执行等待,Next-Key Lock锁机制暴露 | |
| commit; | |
| 继续执行，查询结果为10 | |
| select * from tab where id=10 for update; //查询结果为10 | |
| select * from tab where id=10 for update; //等待执行 | |
| commit; | |
| 继续执行，查询结果为10 | |
| select * from tab where id=10 for update; //查询结果为10，锁降级为Record Lock | |
| select * from tab where id>20 for update; //立即执行，查询结果为50 |
应该从上面的例子中看出了一些问题。
- Next-Key Lock的加锁方式
- 当查询的索引含有唯一属性时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。上表中的第三个事例中可看出.
2. 为什么会存在Next-Key Lock
InnoDB能在可重复读的事务隔离级别下消除幻读
一般的数据库避免幻读需要在串行化的事务隔离级别下，而InnoDB在可重复读的事务隔离级别下消除幻读；这样能够有效提高数据库的并发度。
3. 幻读
幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。
| 事务A | 事务B |
|---|---|
| SET SESSION tx_isolation=‘READ-COMMITTED’; | |
| begin; select * from tab where id>10 for update; //查询结果为20,50 | |
| begin; insert into tab values(30,c); commit; | |
| select * from tab where id>10 for update; //查询结果为20,30,50;出现幻读 |
4. Next-Key Lock避免幻读
关键点在于对查询范围进行加锁，在另一个事务执行插入操作时是不被运行的，从而避免了幻读。
具体的例子可以参考第2、3节，不再举例。
参考：
MYSQL数据库间隙锁（mysql是如何解决幻读的）
概念
MySQL InnoDB支持三种行锁定方式：
行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：
select * from user where user_id > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101（这些记录并不存在）的“间隙”加锁。
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要
快照读和当前读
快照读历史数据－mvcc
innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc（MVCC只在读提交可重复读两种隔离级别下工作）。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。（http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html这个帖子里面就有一个实例）
当前读最新数据－next-key lock
如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了next－key lock，也就是结合gap锁与行锁，达到最终目的。
实现：
\1. 快照读(snapshot read)
简单的select操作(不包括 select ... lock in share mode, select ... for update)
2.当前读(current read)
select ... lock in share mode、select ... for update
insert、update、delete
在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。
测试
- 建表innodb_lock：
DROP TABLE IF EXISTS `innodb_lock`;
CREATE TABLE `innodb_lock` (
`a` int(10) NOT NULL,
`b` varchar(255) NOT NULL DEFAULT '',
KEY `index_a` (`a`),
KEY `index_b` (`b`)
) ENGINE=InnoDB;
- 插入数据，注意这里边没有a为2的数据：
INSERT INTO `innodb_lock` VALUES ('1', 'b2');
INSERT INTO `innodb_lock` VALUES ('3', '3');
INSERT INTO `innodb_lock` VALUES ('4', '4000');
INSERT INTO `innodb_lock` VALUES ('5', '5000');
INSERT INTO `innodb_lock` VALUES ('6', '6000');
INSERT INTO `innodb_lock` VALUES ('7', '7000');
INSERT INTO `innodb_lock` VALUES ('8', '8000');
INSERT INTO `innodb_lock` VALUES ('9', '9000');
（1）开启两个客户端，修改事务隔离级别为可重复读
（2）开启事务，在左侧客户端批量修改a为1~6范围内的数据。在右侧客户端插入a为2的数据。右侧操作被阻塞。说明有间隙锁。
（3）重复（2），事务隔离级别依然是repeatable read，只不过变成在右侧客户端插入a为10的数据，成功。
（4）事务隔离级别设置为read committed，重复步骤（2），发现右侧客户端的操作成功。说明该隔离级别无间隙锁。
（5）还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！左侧客户端给不存在的记录加锁，右侧客户端的增加操作阻塞。
但是，如果a是唯一索引，不会升级全表锁。先添加唯一索引：
（6）重复步骤（5），发现右侧客户端不会被阻塞，数据插入成功
小结：
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。
间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。
要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。
本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读：
t Session A Session B
|
| START TRANSACTION; START TRANSACTION;
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a | b |
| +------+-------+
| | 1 | a |
| +------+-------+
| INSERT INTO innodb_lock
| VALUES (2, 'b')
| SELECT * FROM innodb_lock;
| +------+-------+
| | a | b |
| +------+-------+
| | 1 | a |
| +------+-------+
| COMMIT
|
| SELECT * FROM innodb_lock
| +------+-------+
| | a | b
| +------+-------+
| | 1 | a |
| +------+-------+
|
| UPDATE innodb_lock SET b='z';
| Rows matched: 2 Changed: 2 Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a | b |
| +------+-------+
| | 1 | z |
| | 2 | z |
| +------+-------+
|
数据库锁表及阻塞的原因和解决办法
问题说明
当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁 相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。
关于共享锁和排他锁总结:
1mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型
2排他锁不能和其他锁共存
3共享锁可以和其他锁共存(由于排他锁的特性,共享锁只能和共享锁共存)
死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决方法
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时， 尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
死锁的第二种情况
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同 一条记录进行多次操作，很容易就出现这种死锁的情况。
解决方法
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决 方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据 的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了 长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。
死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
redo log与binlog与undo log
综述
binlog二进制日志是server层的，主要是左主从复制，时间点恢复使用
redo log重做日志是InnoDB存储引擎层的，用来保证事务安全
undo log回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
主备数据一致性
为了保证master和slave的数据一致性，则binlog和redo log保持一致
否则当binlog写完未fsync，主库crash了，备库却执行了，数据会不一致
binlog是mysql内部实现二阶段提交的协调者
为每个事务分配一个XID
一阶段：
事务状态为prepare，redo log和undo log已经记录了对应的日志
二阶段：
binlog完成write和fsync后，成功，事务一定提交了，否则回滚- 发送commit，清除undo信息，刷redo，设置事务状态为completed
故障恢复是如何做的
当出现crash等问题，通过扫描binlog中所有的xid，告知innodb，innodb回滚其它事务
需要保证binlog写入和redo log事务提交顺序一致性
如果不一致，会导致数据不一致
BLGC(Binary Log Group Commit) 解决串行prepare_commit_mutex的问题
引入队列解决
区别
redo log在事务没有提交前，每一个修改操作都会记录变更后的数据，保存的是物理日志->数据
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性
redo log只是先写入Innodb_log_buffer，定时fsync到磁盘
binlog只会在日志提交后，一次性记录执行过的事务中的sql语句以及其反向sql(作为回滚用)，保存的是逻辑日志->执行的sql语句
undo log事务开始之前，将当前版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性，保存的是逻辑日志->数据前一个版本
- 基于
redo log直接恢复数据的效率 高于 基于binglog sql语句恢复 - binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。
- binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
作者：哓晓的故事
链接：https://www.jianshu.com/p/090087c22820
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
必须了解的mysql三大日志-binlog、redo log和undo log
日志是mysql数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志(binlog)和事务日志(包括redo log和undo log)，本文接下来会详细介绍这三种日志。
binlog
binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。
逻辑日志：可以简单理解为记录的就是sql语句。
物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。
binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。
binlog使用场景
在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。
- 主从复制：在
Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。 - 数据恢复：通过使用
mysqlbinlog工具来恢复数据。
binlog刷盘时机
对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：
- 0：不去强制要求，由系统自行判断何时写入磁盘；
- 1：每次
commit的时候都要将binlog写入磁盘； - N：每N个事务，才会将
binlog写入磁盘。
从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。
binlog日志格式
binlog日志有三种格式，分别为STATMENT、ROW和MIXED。
在
MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。
STATMENT
基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；
缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。ROW
基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨MIXED
基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog
redo log
为什么需要redo log
我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：
- 因为
Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！ - 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
redo log基本概念
redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。
在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：
mysql支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下：
| 参数值 | 含义 |
|---|---|
| 0（延迟写） | 事务提交时不会将redo log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到redo log file中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。 |
| 1（实时写，实时刷） | 事务每次提交都会将redo log buffer中的日志写入os buffer并调用fsync()刷到redo log file中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。 |
| 2（实时写，延迟刷） | 每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到redo log file。 |
redo log记录形式
前面说过，redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：
同时我们很容易得知，在innodb中，既有redo log需要刷盘，还有数据页也需要刷盘，redo log存在的意义主要就是降低对数据页刷盘的要求。在上图中，write pos表示redo log当前记录的LSN(逻辑序列号)位置，check point表示数据页更改记录刷盘后对应redo log所处的LSN(逻辑序列号)位置。write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。
启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。
重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。
还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。
redo log与binlog区别
| redo log | binlog | |
|---|---|---|
| 文件大小 | redo log的大小是固定的。 |
binlog可通过配置参数max_binlog_size设置每个binlog文件的大小。 |
| 实现方式 | redo log是InnoDB引擎层实现的，并不是所有引擎都有。 |
binlog是Server层实现的，所有引擎都可以使用 binlog日志 |
| 记录方式 | redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。 | binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上 |
| 适用场景 | redo log适用于崩溃恢复(crash-safe) |
binlog适用于主从复制和数据恢复 |
由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。
undo log
数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log也是MVCC(多版本并发控制)实现的关键，这部分内容在面试中的老大难-mysql事务和锁，一次性讲清楚！中有介绍，不再赘述。
参考
MySQL原子性与持久性的保证(undo log, redo log与binlog)
MySQL的ACID特性
- 原子性（Atomicity）：原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做对于银行转账来所就是要么都做，要么都不做。
- 一致性（Consistency）：一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。
- 隔离性（Isolation）：隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（Durability）：持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
下面我们着重介绍MySQL原子性与持久性
MySQL原子性的保证–undo log（更具体的在MVCC中）
undo log的基本概念
undo log有两个作用，提供回滚和多个行版本控制(MVCC)。
在数据修改的时候，不仅记录了redo log，还记录了对应的undo，如果因为某些原因事务失败而回滚，可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样，他是逻辑日志。可以认为当delete一条记录是，undo log中记录一条对应的insert记录，反之亦然，当update一条记录时，他记录一条对应相反的update记录。
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚，有时候应用带行版本控制的时候，也是用过undo log来实现：当读取带某一行的其他事务锁定时，它可以从undo log中分析出改行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
undo log采用段的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。
另外，undo log也会产生redo log，因为undo log也要实现持久性的保护。
undo log的存储方式
innoDB存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。
MySQL 5.5之后可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量innodb_undo_logs自定义多少个rollback segment，默认值为128。
undo log默认存放在共享表空间中。即保存数据的ibdata1中，如果开启了innodb_file_per_table,将放在每个表的.ibd文件中。
在MySQL5.6中，undo的存放位置还可以通过变量innodb_undo_directory来自定义存放目录，默认值为"."表示datadir。
默认rollback segment全部写在一个文件中，但可以通过设置变量innodb_undo_tablespaces平均分配到多个文件中。
MySQL持久性的保证–redo Log与binlog
InnoDB重要的日志模块：redo log
在《孔乙己》这篇文章中酒店掌柜有一个分班，专门用来记录客人的赊账记录，如果赊账的人不多，那么它可以把顾客和账目写在板上，但是如果赊账的人多了，分班总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。如果有人要赊账或者还账的话，掌柜一般有两种做法：
- 直接把账本翻出来，把这次赊的帐加上去或者扣除掉；
- 现在粉板上记下这次的帐，等打烊以后再把账本翻出核算。
第一种做法对应MySQL中的做法就是每一次更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后在更新，整个过程IO成本，查找成本都很高
第二种粉板与账本配合的整个过程，就是MySQL里面经常说到的WAL技术，WAL全称为Write-Ahead Logging，他的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写道redo log里面，并更新内存，这个时候更新就算完成了。同时InnoDB引擎会在适当的时候，将这个操作记录在磁盘里面，而这个更新往往是在系统比较空闲的时候做的，这就像打烊以后掌柜做的事。
如果今天赊账的不多，掌柜可以等打烊以后在整理，但是如果某天赊账的特别多，粉板写满了，又怎么办呢，这个时候掌柜只好放下手中的活，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记录新账腾出空间。
与此类似，InnoDB的redo log是固定大小的，比如可以配置一组4个文件，每个文件的大小为1GB，那么这块粉板总共就可以记录4GB的操作，从头开始写，写到末尾就又回到开头循环写。
write pos是当前记录的位置，一边写一边后移，写到3号文件末尾后就会到0号文件开头，checkpoint就是当前要擦除的位置，也是往后推移并且循环的，查出记录前要把记录更新到数据文件。
write pos和checkpoint之间的数据是粉板上还空则的部分，可以用来记录新的操作，如果write pos追上checkpoint，表示粉板满了，这时候不能在执行新的更新，得停下来先删除掉一些记录。
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力成为crash-safe。
重要的日志模块：binlog
前面我们讲过，MySQL整体来看，其实就有两块，一块是Server层，负责MySQL功能层面上的事情，还有一块是引擎层，负责存储相关的具体事宜，上面我们聊到的粉板redo log是InnoDB特有的日志，而Server层也有自己的日志，成为binlog（归档日志）。
那么我想问，为什么又两份日志呢。
因为最开始MySQL没有InnoDB引擎，MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档，InnoDB如果只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统，redo log来实现crash-safe能力。
这两种日志有以下三点不同：
- redo log是InnoDB特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
- redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如 “给ID=2这一行的c字段加1”.
- redo log是循环写的，空间固定会用完；binlog是可以追加写入的，在文件写道一定大小后会切换到下一个，不会覆盖以前的日志。
我们来看看InnoDB引擎在执行下列MySQL语句的流程。
update T set c=c+1 where ID=2;
- 1
- 执行器先找到引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行，如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的binlog，并把binlog写入磁盘。
- 执行器调用引擎提交事务接口，引擎把刚刚写入的redo log改成提交状态，更新完成。
我们看到，最后三部被拆成了两个步骤：prepare和commit，这就是两阶段提交。
两阶段提交
两阶段提交是为了让两份日志之间的逻辑一致，binlog会记录所有的逻辑操作，并且采用追加写的形式。同时系统会定期做整库备份。这里的定期取决于系统的重要性，可以是一天一备，也可以是一周一备。
如果需要恢复到指定的某一秒，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那么我们可以这么做：
- 首先，找到最近的一次全量备份，如果你运气好，该备份那个就是昨天晚上的一个备份，从这个备份恢复到数据库。
- 从备份的时间点开始，将备份的binlog依次取出来，重放如中午误删表之前的哪个时刻。
为什么我们需要两阶段提交？
由于redolog和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log，再写binlog，或者采用反过来的顺序，来看下会有什么问题。
仍然用前面的update语句来做例子，假设当前ID=2的行，字段c的值是0，在假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，那么分为两种情况。
- 先写redo log后写binlog。假设在redo log写完，binlog没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1.由于binlog没有写完就crash了，这时候binlog里面就没有记录这个语句，因此之后备份日志的时候，存起来的binlog里面就没有这条语句。如果需要使用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次的更新，回复出来的这一行c的值就是0，与原库不同。
- 先写binlog后写redo log。如果binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行的c的值是0.但是binlog里面已经记录了把c从0修改为1这个日志，所以之后再用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行的值就是1，与原库的值不同。
在两阶段提交的不同时刻MySQL出现异常，重启后会出现什么情况
我们假设在redo log处于prepare阶段，写binlog之前为时刻A，在写binlog之后，redo log处于commit阶段之前为时刻B
如果在时刻A发生crash，由于binlog还没有写，redo log也还没有提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库中，binlog和redo log都没有写入，相当于没有执行这条命令。所以MySQL重启后和将来使用binlog进行数据恢复的数据库的状态是一样的。
如果在时刻B发生crash，这时候binlog写完，redo log还没有commit，那么在崩溃恢复时MySQL会做以下判断规则：
- 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；
- 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：
a. 如果是，则提交事务。
b. 否则，回顾事务。
MySQL怎么知道binlog是完整的？
一个事务的binlog是有完整格式的：
- statement格式的binlog，最后会有COMMIT；
- row格式的binlog，最后会有一个XID event。
另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容和正确性，对于binlog日志由于磁盘的原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现，所以，MySQL还是有办法验证事务binlog的完整性的。
redo log和binlog是怎么关联起来的？
他们都有一个共同的数据字段，叫XID。奔溃恢复的时候，会被顺序扫描redo log：
- 如果碰到既有prepare，又有commit的redo log，就直接提交。
- 如果碰到只有prepare，没有commit的redo log，就拿着XID去binlog找对应的事情。
处于prepare阶段的redolog加上完整的binlog，重启就能恢复，MySQL为什么要这么设计？
因为binlog一旦写入完成之后，那么这个binlog是完整的，如果这个时候MySQL发生崩溃，在重新启动之后，该binlog会被从库使用，所以主库也要提交这个事务，采用这个策略，主库和备库的数据就保证了一致性。
如果这样，为什么还要两阶段提交？为什么要先redolog写完，在写binlog，崩溃恢复的时候，必须两个日志完整才可以，是不是一样的逻辑？
对于InnoDB引擎来说，如果redo log提交完成了，事务就不能回滚，如果允许回滚，就有可能覆盖掉别的事务。而如果redo log直接提交，然后binlog写入的时候失败，InnoDB有回滚不了，数据和binlog日志有不一致了，两阶段提交就是为了给所有人一个机会，等待所有人OK。
不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃恢复，又能支持归档，不就可以了？
binlog没有能力恢复"数据页"
在图中所标注的位置，也就是binlog2写完了，但是整个事务还没有commit的时候，MySQL发生了crash。重启后，引擎内部事务2会回滚，然后应用binlog2可以补回来，但是，对于事务1，来说，系统认为已经提交完成，不会再应用一次binlog1。
InnoDB引擎使用的是WAL技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖与日志来恢复数据页。
再图中这个位置如果发生崩溃，事务1也是可能丢失了的，而且是数据页级的丢失，此时，binlog里面并没有记录数据页的更新细节，是补不回来的。
能不能只用redo log，不用binlog？
如果只从崩溃恢复的角度来说，是可以的，但是因为binlog拥有归档和赋值功能，如果关掉binlog，很多系统都无法使用，总的来说还是生态不行。
redo log一般设置多大？
如果redo log太小，会导致文件很快就被写满，然后不得不强行刷redo log，很容易就会使MySQL抖，这样WAL机制的能力就发挥不出来了，如果磁盘足够大，那么可以设置为4个1GB。
正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的？
redo log并没有记录数据页的完整数据，所以他并没有能力自己去更新磁盘数据页，也就不存在"数据最终落盘，是由redo log更新过去" 的情况。
- 如果正常运行的实例，数据页被修改后，跟磁盘的数据页不一致，成为脏页，最终数据落盘，就是把内存中的数据页写盘，这个过程，甚至与redo log毫无关系。
- 再崩溃恢复场景中，InnoDB如果判断一个数据页可能再崩溃恢复的时候丢失了更新，就会把他读到内存中，然后让redo log更新内存内容，更新之后，内存页变为脏页，就回到了第一种情况的状态。
redo log buffer 是什么？ 实现修改内存，还是先写redo log文件？
再一个事务更新过程中，日志是要写多次的。例如
begin;
insert into t1 ...
insert into t2 ...
commit;
这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都先保存起来，但又不能再还没commit的时候就直接写道redo log文件里。
所以redo log buffer 就是一块内存，用来保存redo日志的，也就十所，再执行第一个insert的时候，数据的内存被修改了，redo log buffer 也写入了日志。
但是，真正把日志写道redo log文件，实在执行commit语句的时候做的，单独执行一个更新语句的时候，InnoDB会自己启动一个事务，再语句执行完成的时候提交。过程跟上面一样，只不过是压缩到了一个语句里面完成。
undo与redo如何记录事务
假设有A，B两个数据，值分别为1，2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下：
- 事务开始。
- 记录A=1到undo log。
- 修改A=3。
- 记录A=3到redo log。
- 记录b=2到undo log。
- 修改B=4。
- 记录B=4到redo log。
- 将redo log写入磁盘。
- 事务提交。
上述记录中，2，4，5，7，8均为新增操作，但是2，4，5，7为缓冲到buffer区，只有8增加了IO操作，为了保证redo log能够有比较好的IO性能，InnoDB的redo log的设计有以下几个特点：
- 尽量保持redo log存储在一段连续的空间上。因此在系统第一次启动时就会将日志文件的空间完全分配。以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。
- 批量写入日志。日志并不是直接写入文件，而是先写入redo log buffer.当需要将日志刷新到磁盘时 (如事务提交),将许多日志一起写入磁盘.
- 并发的事务共享Redo Log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起，以减少日志占用的空间。
- 因为3的原因，当一个事务将redo log写入磁盘时，也会将其他未提交的事务的日志写入磁盘。
- redo log上只进行顺序追加的操作，当一个事务需要回滚时，它的redo log记录也不会从redo log中删除掉。
redo log的恢复
由于未提交的事务和回滚了的事务也会记录redo log，因此在进行恢复时，这些事务要进行特殊的处理，有两种不同的恢复策略：
- 进行回复时，只重做已经提交了的事务。
- 进行回复时，重做所有事务包括未提交的事务和回滚了的事务，然后通过undo log回滚的哪些未提交的事务。
MySQL数据库InnoDB存储引擎使用了B策略，InnoDB存储引擎中的恢复机制有几个特点：
- 在重做redo log时，并不关心事务性，恢复时，没有begin，也没有commit，rollback的行为。也不关心每个日志是哪个事务的。尽管事务ID等事务相关的内容会记入Redo Log，这些内容只是被当作要操作的数据的一部分。
- 使用第二个策略就必须将undo log持久化，而且必须要在写redo log之前将对应的undo log写入磁盘，undo和redo的这种关联，使得持久化变得复杂起来，为了降低复杂度，InnoDB将undo看作数据，因此记录undo log的操作也会记录到redo logzhong，这样，undo log就可以像数据一样缓存起来，而不用在redo log之前写入磁盘。包含undo log的redo log，看起来是这样的：
记录1: <trx1, Undo log insert <undo_insert …>>
记录2: <trx1, insert …>
记录3: <trx2, Undo log insert <undo_update …>>
记录4: <trx2, update …>
记录5: <trx3, Undo log insert <undo_delete …>>
记录6: <trx3, delete …>
- 既然Redo没有事务性，那岂不是会重新执行被回滚了的事务？确实是这样。同时Innodb也会将事务回滚时的操作也记录到redo log中。回滚操作本质上也是对数据进行修改，因此回滚时对数据的操作也会记录到Redo Log中。
记录1: <trx1, Undo log insert <undo_insert …>>
记录2: <trx1, insert A…>
记录3: <trx1, Undo log insert <undo_update …>>
记录4: <trx1, update B…>
记录5: <trx1, Undo log insert <undo_delete …>>
记录6: <trx1, delete C…>
记录7: <trx1, insert C>
记录8: <trx1, update B to old value>
记录9: <trx1, delete A>
