MySQL半一致性读导致语句级Binlog复制错误

共计 2455 个字符，预计需要花费 7 分钟才能阅读完成。

MySQL 事务的隔离级别为 read committed(或以下)，或者设置了 innodb_locks_unsafe_for_binlog 参数会启用半一致性读特性。

半一致性读参考: http://www.linuxidc.com/Linux/2017-02/140844.htm

MySQL 官方文档 refman-5.6-en.a4.pdf 1833 页 关于 innodb_locks_unsafe_for_binlog 参数

对于半一致性读，我感觉一个是违反两阶段锁，将不符合条件记录的行级锁提前释放。另一个是 Update 的行如果被锁定，则返回一个最近提交的版本。

官方文档原文如下：
Enabling innodb_locks_unsafe_for_binlog has additional effects:
 ? For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes.
 Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.
 This greatly reduces the probability of deadlocks, but they can still happen.

 ? For UPDATE statements, if a row is already locked, InnoDB performs a“semi-consistent”read,
 returning the latest committed version to MySQL so that MySQL can determine whether the row
 matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads
 the row again and this time InnoDB either locks it or waits for a lock on it.

半一致性读本身是为了增加并发，但是对于 STATEMENT 格式的 binlog 则是致命的错误。
实验环境如下:

初始化数据
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
 INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
 COMMIT;

 mysql> select * from t;
 +—+——+
 | a | b    |
 +—+——+
 | 1 |    2 |
 | 2 |    3 |
 | 3 |    2 |
 | 4 |    3 |
 | 5 |    2 |
 +—+——+
 5 rows in set (0.00 sec)

开启一个终端 A, 将 b = 3 的记录修改为 10
然后开启另外一个终端 B, 将 b = 2 的记录修改为 3 并且提交,
最后提交终端 A 的事务。

查看此时的结果
mysql> select * from t;
 +—+——+
 | a | b    |
 +—+——+
 | 1 |    3 |
 | 2 |  10 |
 | 3 |    3 |
 | 4 |  10 |
 | 5 |    3 |
 +—+——+
 5 rows in set (0.00 sec)

这个结果没有任何问题，但是查看 binlog 的内容
BEGIN
 /*!*/;
 # at 2802
 #140215  0:07:52 server id 1  end_log_pos 2906 CRC32 0x264b3682    Query    thread_id=2    exec_time=0    error_code=0
 SET TIMESTAMP=1392394072/*!*/;
update t set b=3 where b=2
 /*!*/;
 # at 2906
 #140215  0:07:54 server id 1  end_log_pos 2937 CRC32 0x59a3d24d    Xid = 63
COMMIT/*!*/;
 # at 2937
 #140215  0:06:02 server id 1  end_log_pos 3020 CRC32 0x56a9493b    Query    thread_id=3    exec_time=0    error_code=0
 SET TIMESTAMP=1392393962/*!*/;
 BEGIN
 /*!*/;
 # at 3020
 #140215  0:06:02 server id 1  end_log_pos 3125 CRC32 0x0d874b5d    Query    thread_id=3    exec_time=0    error_code=0
 SET TIMESTAMP=1392393962/*!*/;
update t set b=10 where b=3
 /*!*/;
 # at 3125
 #140215  0:08:01 server id 1  end_log_pos 3156 CRC32 0x8fe0dd5d    Xid = 61
COMMIT/*!*/;

如果使用 binlog 复制，则备库执行的语句如下:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
 INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
 COMMIT;

 update t set b=3 where b=2;
 commit;
 update t set b=10 where b=3;
 commit;

备库执行后的数据则是
mysql> select * from t;
 +—+——+
 | a | b    |
 +—+——+
 | 1 |  10 |
 | 2 |  10 |
 | 3 |  10 |
 | 4 |  10 |
 | 5 |  10 |
 +—+——+
 5 rows in set (0.00 sec)

为了避免这个问题，可以将 binlog_format 设置为 ROW。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-02/140846.htm