日志相关问题

两阶段提交不同瞬间，MySQL异常重启，怎么保证数据完整？

图 1 两阶段提交示意图

不是update 语句执行流程吗，怎么调用 commit ？两个“commit”的概念混淆了：

1）上述“commit 语句”，MySQL 语法中，提交事务。跟 begin/start transaction 配对用。执行时，包含“commit 步骤（2）”。

2）图中“commit ”，事务提交小步骤（最后一步），执行完，事务提交。

没显式开启事务，update 语句自己就是一个事务，执行完成，用到“commit 步骤“。

A ：binlog 还没写，redo log 也还没提交，崩溃恢复，事务回滚。binlog 没写，不会传备库。

B， binlog 写完，redo log 没 commit ， crash，崩溃恢复判断规则

1.  redo log 事务完整，有commit 标识，提交；

2.  redo log 事务完整 prepare， 对应binlog 完整提交.否则回滚：

追问 1：怎么知道 binlog 是否完整?

有完整格式：

statement 格式的 binlog，最后有 COMMIT

row 格式的 binlog，最后有XID event

binlog-checksum 验证 binlog 正确性。磁盘原因，中间出错的情况。

追问 2：redo log 和 binlog 怎么关联?

共同数据字段XID。崩溃恢复顺序扫描 redo log：

只有 parepare、没有 commit 的 redo log，XID 去 binlog 找对应事务。

追问 3：prepare 阶段的 redo log 加上完整 binlog， 重启就能恢复，为什么这么设计?

保证主、备库数据一致性。 

B， binlog 已写，被从库（用 binlog 恢复的库）用，主库也要提交这个事务。

追问 4：如这，为什么还要两阶段提交呢？redo log写完，再写 binlog。崩溃恢复时，必须得两个日志都完整才可以。是不是一样的逻辑？

场景，事务持久性问题。每人都“ok”，一起提交。

redo log 提交完成了，事务不能回滚（如回滚，覆盖别的事务更新）。

如 redo log 直接提交，binlog 写入失败，InnoDB 回滚不了，数据和 binlog 日志不一致了。

追问 5：不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？

只保留 binlog， “更新到内存” -> “写 binlog”-> “提交事务”不可以。

历史原因，InnoDB 是 MySQL 插件，binlog 没有崩溃恢复能力，用 InnoDB 原有 redo log 。

实现上原因：只用 binlog 来实现崩溃恢复的流程，没有 redo log 

图 2 只用 binlog 支持崩溃恢复

binlog 不能恢复“数据页”。binlog2 写完，没commit 的时候，crash。

事务 2 回滚， binlog2 补回来；事务 1 来说，认为提交完了，不会再用 binlog1。

InnoDB 用 WAL，依赖日志恢复数据页。事务 1 可能丢失数据页。binlog 没记录数据页更新细节，补不回来

追问 6：只用 redo log，不要 binlog？

只从崩溃恢复角度可以。正式生产库，binlog 开。有着 redo log 无法替代的功能。

1）归档。redo log 循环写，历史日志没法保留。

2）高可用的基础，binlog 复制。

2）数据分析系统，靠消费 MySQL 的 binlog 更新数据。关掉下游没法输入。

追问 7：redo log 一般设置多大？

redo log 太小的话，很快写满，强行刷 redo log， WAL 能力发挥不出来。

redo log  4 个文件、每个 1GB 

追问 8：数据写入后落盘，redo log更新过来还是从buffer pool ？

redo log 没有记录数据页完整数据，不能更新磁盘数据页。

1.  不一致，称为脏页。内存中数据页写盘。与 redo log 毫无关系。

2.  崩溃恢复场景，InnoDB 判断数据页丢失更新，将它读到内存，让 redo log 更新内存。更新完成，内存页变成脏页，回到第一种情况状态。

追问 9：redo log buffer 是什么？是先修改内存，还是先写 redo log文件？

更新中，日志写多次：不能没 commit 时写到 redo log 文件里。

begin;

insert into t1  ...    //内存被修改，redo log buffer 写入日志

insert into t2  ...

commit;    //日志写到 redo log 文件（文件名是 ib_logfile+ 数字）

redo log buffer 块内存，先存 redo 日志。

业务设计问题

业A、B 两个用户，互相关注成好友。like 表，friend 表，like 表有 user_id、liker_id 两个字段，设置为复合唯一uk_user_id_liker_id。：

以 A 关注 B 为例：第一步，先查询对方有没有关注自己（B 有没有关注 A）

select * from like where user_id = B and liker_id = A;

有，则成为好友 insert into friend;

没有，单向关注关系insert into like;

A、B 同时关注对方，不会成好友。第 1 步双方没互关，用了排他锁也不行，记录不存在，行锁不生效。锁层面如何处理？

图 3 并发“喜欢”逻辑操作顺序

select 都空。session 1 插入一个单向关注关系”。session 2 也同样

解决办法：给“like”表增加relation_ship，取值 1、2、3。

1 ，user_id 关注liker_id;

2，liker_id 关注user_id;

3，互相关注。

然后，当 A 关注 B 的时候，逻辑改成如下所示的样子：

比较 A 和 B 的大小，A<B：

如果 A>B，则执行下面的逻辑

让“like”表里的数据保证 user_id < liker_id，反向关系已存在，出现行锁冲突。

insert … on duplicate 确保事务内部，强行占行锁，select 判断relation_ship 逻辑时确保行锁保护下读操作。

操作符 “|” 是按位或，连同 insert  ignore，保证重复调用时幂等性。

like 表relation_ship = 3，  friend 表里也有 A 和 B 记录。

之前说尽量不要使用唯一索引，这个例子创建两个。例子业务根本，“一定插入重复数据，数据库要有唯一性约束”。

在“业务开发保证不会插入重复记录”的情况下，着重要解决性能问题时，尽量用普通索引。

思考题

创建表 t，插入一行，修改。

mysql> CREATE  TABLE `t` (

`id` int(11) NOT  NULL primary key auto_increment,

`a` int(11)  DEFAULT NULL

) ENGINE=InnoDB;

insert into t  values(1,2);   表 t 唯一的一行数据 (1,2)。假设，我现在要执行：

mysql> update  t set a=2 where id=1;  结果：

MySQL 处理命令三种选择：你觉得实际哪种呢？为什么？

1.  更新都是先读后写的，MySQL 读出数据，发现 a 的值本来就是 2，不更新，直接返回

2.  MySQL 调用了 InnoDB 引擎提供的“修改为 (1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；

3.  InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作，该加锁的加锁，该更新的更新。

图 12 锁验证方式

B 的 update 被 blocked ，InnoDB 才能加锁，排除 1

图 13 可见性验证方式

A 第二个 select 是一致性读（快照读)，看不见 B 更新。返回 (1,3)表示看见新版本，session A update 时生成。排除 2

答案 3，InnoDB ，该加锁的加锁，该更新更新。

更新前判断一下，不用浪费 InnoDB 操作？其实 MySQL 是确认的。MySQL读的，确定 (id=1), 要写的 (a=3)，看不出来“不需要修改”。验证：

图 14 可见性验证方式 -- 对照

补充说明：

上面都是在binlog_format=statement 下进行。如果是binlog_format=row且 binlog_row_image=FULL ，需在 binlog 记录所有字段，读数据时读出。

“既然读了就会判断”， 此时“返回 (1,2)”。

binlog_row_image=NOBLOB, 会读除 blob 外所有字段，结果还是(1,2)

图 15 binlog_row_image=FULL 读字段逻辑  

新加update_time on update current_timestamp，发现会加锁，提交update_time不变，binlog没生成，加锁实际没更新？

 timestamp 设置自动更新，更新“别的字段”时，MySQL 会读入所有涉及字段，不需要修改。

@郭江伟 同学提到了两个点，都非常好，有去实际验证。结论是这样的：第一，hexdump 看出来没改应该是 WAL 机制生效了，要过一会儿，或者把库 shutdown 看看。第二，binlog 没写是 MySQL Server 层知道行的值没变，所以故意不写的，这个是在 row 格式下的策略。你可以把binlog_format 改成 statement 再验证下。

评论1

酒馆生意好,老板把孔乙己的欠账记录记小黑板上并记己点菜单。吹牛,忘了叫几两酒了。又给老板说,老板把酒改成二两。老板也不确定孔乙己叫没叫酒,就去查菜单,发现孔乙己确实点了酒,但是本来就二两,也就难得麻烦了,又要修改小黑板,又要改菜单。直接就给孔乙己说已经改好了。😄

小二连忙过来拦住，“老板，又赊账一碟茴香豆。”

老板大惊，“我怎不知？”

小二道，“老板你方才看板时没拿记账笔，记账笔没人用，自然可用”

于是把店规“变账须用记账笔。” 改为

“改帐均须动笔。纵为不变之帐，仍需覆写之”

评论2

创建测试数据：

mysql> create table t(id int primary key auto_increment,a int );

mysql> insert into t values(1,2);

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t set a=2 where id=1;

Query OK, 0 rows affected (0.00 sec)

查看系统锁情况：

show engine innodb status

---TRANSACTION 958998, ACTIVE 51 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 2, OS thread handle 139663691581184, query id 22 localhost root

mysql> show processlist;

+----+------+-----------+--------------------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+--------------------+---------+------+----------+------------------+

| 2 | root | localhost | sysbench | Sleep | 352 | | NULL |

| 3 | root | localhost | NULL | Sleep | 301 | | NULL |

+----+------+-----------+--------------------+---------+------+----------+------------------+

其中Thread id=2 为update会话，说明系统有锁

另一会话执行 update t set a=2 where id=1;

ERROR 1205 (HY000): Unknown error 1205 MySQL error code 1205 (ER_LOCK_WAIT_TIMEOUT): Lock wait timeout exceeded; try restarting transaction

提交第一个会话查看生成的binlog

### INSERT INTO `sysbench`.`t`

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=2 /* INT meta=0 nullable=1 is_null=0 */

# at 858

#181217 14:28:21 server id 9012 end_log_pos 889 CRC32 0xf96f7fcb Xid = 20

COMMIT/*!*/;

# at 889

#181217 14:42:14 server id 9012 end_log_pos 930 CRC32 0x3de034ba Rotate to bin.000089 pos: 4

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

发现没有update的binlog产生，也就是说该语句在server层没有实际执行

用hexdump对比update前后的数据行，事务id和回滚id也没变，说明innodb没有实际更新行。

鉴于该语句产生行锁，有事务信息，没有实际修改，可判断innodb在更新前后值一样时不会实际更新数据

评论3

1.事务执行过程中，binlog像redo log一样记录到binlog_cache里，单独的内存，redo log buffer

2.redo log buffer设置成全局参数，Binlog cache size也是global 的，5.5～5.7