mysql rc模式_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: 如果有什么理解错误的，或者模拟的不合理的地方，麻烦纠正指出来，谢谢。