mysql rc模式时binlog_format=row的解释【转】

总体来说：在 tx_isolation= READ-COMMITTED 、binlog_format =statement 的情况下，mysql 没有gap 锁，这样binlog 记录的数据修改的顺序可能会导致 复制环境的 slave 数据和master 数据不一致。

模拟步骤

数据初始化

 CREATE TABLE `gapt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
mysql> select * from gapt;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
| 10 | 10   |
| 11 | 11   |
| 15 | 15   |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
|  6 | 6    |
|  8 | 8    |
|  9 | 9    |
+----+------+  

session1 和 session2 模拟

@session 1:
mysql> show variables like '%tx_isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update gapt set id=name+20 where name between 5 and 11;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

@session2
mysql> show variables like '%tx_isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into gapt select 24,5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> system date
Fri Jun 19 10:55:38 CST 2015

@session1
mysql> select * from gapt where name between 5 and 11;         
+----+------+
| id | name |
+----+------+
| 30 | 10   |
| 31 | 11   |
| 24 | 5    |
| 25 | 5    |
| 26 | 6    |
| 28 | 8    |
| 29 | 9    |
+----+------+
7 rows in set (0.00 sec)

#你会发现 6行变成7行了，
mysql> system date;
Fri Jun 19 10:55:50 CST 2015
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

重点： 两个 session 执行完 commit，并且按照 binlog_format = statement 日志模式的话，那么最终的binlog日志记录应该是按照事务 commit 的顺序记录的。

#binlog 实际存储的执行顺序
@session2 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into gapt select 24,5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update gapt set id=name+20 where name between 5 and 11;     
Query OK, 6 rows affected (0.00 sec)
mysql> commit;

如果是这样记录的话你会发现这个 @session1 的 update 语句会把 session2 刚刚插入的 name='5',id=24 数据也会更新掉了！！这也就导致了（如果有复制）从库从库的数据就会和复制的主库不一样了！！

问题来了，我是如何模拟的呢？我是设置 tx_isolation=READ-COMMITTED binlog_format= MIXED 的时候，上面的操作可以正确执行，这是因为 MySQL 识别到了这个可能存在不一致的复制情况，就把上面的操作转换成了 binlog_format=row 的二进制日志。

#150619 10:55:00 server id 4306  end_log_pos 283        Table_map: `repl`.`gapt` mapped to number 52
#150619 10:55:00 server id 4306  end_log_pos 319        Write_rows: table id 52 flags: STMT_END_F
BINLOG '
hISDVRPSEAAALgAAABsBAAAAADQAAAAAAAEABHJlcGwABGdhcHQAAgMPAmAAAg==
hISDVRfSEAAAJAAAAD8BAAAAADQAAAAAAAEAAv/8GAAAAAE1
'/*!*/;
# at 319
#150619 10:55:09 server id 4306  end_log_pos 346        Xid = 447            #session2 的 commit
COMMIT/*!*/;
# at 346
#150619 10:57:04 server id 4306  end_log_pos 414        Query   thread_id=16    exec_time=0     error_code=0
SET TIMESTAMP=1434682624/*!*/;
BEGIN
/*!*/;
# at 414
# at 460
#150619 10:54:14 server id 4306  end_log_pos 460        Table_map: `repl`.`gapt` mapped to number 52
#150619 10:54:14 server id 4306  end_log_pos 578        Update_rows: table id 52 flags: STMT_END_F
BINLOG '
VoSDVRPSEAAALgAAAMwBAAAAADQAAAAAAAEABHJlcGwABGdhcHQAAgMPAmAAAg==
VoSDVRjSEAAAdgAAAEICAAAAADQAAAAAAAEAAv///AUAAAABNfwZAAAAATX8BgAAAAE2/BoAAAAB
NvwIAAAAATj8HAAAAAE4/AkAAAABOfwdAAAAATn8CgAAAAIxMPweAAAAAjEw/AsAAAACMTH8HwAA
AAIxMQ==
'/*!*/;
# at 578
#150619 10:57:04 server id 4306  end_log_pos 605        Xid = 443       #session1 的 commit
COMMIT/*!*/;

binlog_format 为什么转化为 row 就可以呢，因为 row 格式的日志记录的是底层数据真正的变化，这样就不会导致复制环境的主从数据不一致了。

ps: 如果有什么理解错误的，或者模拟的不合理的地方，麻烦纠正指出来，谢谢。