环境：
主、从库的binlog_format = statement
隔离级别： RC
| 主库会话1 | 主库会话2 | 主库会话3 | 从库会话 |
|
use test; create table f1(char(2)) engine=InnoDB; |
|||
|
begin; insert into t1 select 1; insert into t1 select 2; insert into t1 select 3; insert into t1 select 4; insert into t1 select 5; |
|||
|
begin; insert into t1 select 'a'; insert into t1 select 'b'; insert into t1 select 'c'; insert into t1 select 'd'; insert into t1 select 'e'; insert into t1 select 'f'; commit; |
|||
|
select * from t1;
+------+ | f1 | +------+ | a | | b | | c | | d | | e | | f | +------+ |
select * from t1; +------+ | f1 | +------+ | a | | b | | c | | d | | e | | f | +------+ |
||
| commit | |||
|
select * from t1;
+------+ | f1 | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | a | | b | | c | | d | | e | | f | +------+ |
select * from t1; +------+ | f1 | +------+ | a | | b | | c | | d | | e | | f | | 1 | | 2 | | 3 | | 4 | | 5 | +------+ |
可以看到上图中，最后一行，主从库的数据排列顺序变化了。如果这时候我们在主库再执行一个类似 update t1 set f1='A' limit 4; 这种操作，则从库的数据彻底乱了。
下面是执行update时候 主库提示的warnings告警。
master [localhost] {root} (test) > master [localhost] {root} (test) > show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
1 row in set (0.00 sec)