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