总体来说:在 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: 如果有什么理解错误的,或者模拟的不合理的地方,麻烦纠正指出来,谢谢。