你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-02-20] 来源:Linux社区  作者:壹頁書 [字体: ]

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

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款