MySQL数据恢复和复制对InnoDB锁机制的影响
MySQL通过BINLOG记录执行成功的INSERT,UPDATE,DELETE等DML语句。并由此实现数据库的恢复(point-in-time)和复制(其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQLl数据库，多称为slave，进行实时同步)。MySQL 5.5.x以后的版本支持3种日志格式。通过binlog_format参数设置。该参数影响了记录二进制日志的格式，十分重要。
1.STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。
2.ROW格式记录的不再是简单的SQL语句，而是记录表的每行记录更改的情况。
3.在MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录。但是在一些特殊情况下会使用ROW格式，可能的情况如下：
(1)表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。
(2)使用了UUID(),USER(),CURRENT_USER(),FOUND_ROWS(),ROW_COUNT()等不确定函数。
(3) 使用了INSERT DELAY语句。
(4)使用了用户自定义函数(UDF).
(5)使用了临时表(temporary table) 。
对于基于语句的日志格式(STATEMENT)的恢复和复制而言，由于MySQL的BINLONG是按照事务（transaction）提交（committed）的先后顺序记录的，因此要正确恢复或者复制数据，就必须满足：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读（Phantom Problem）。这已经超过了ISO/ANSI SQL92"可重复读（Repeatable Read）"隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到Next-Key Lock锁的原因，比如用在范围条件更新记录时，无论是在Read Committed或者是Repeatable Read隔离级别下，InnoDB都要使用Next-key Lock锁。既然说到Next-key Lock锁机制，我这里简单说一下，演示各种效果就让童鞋们自己去测试了^_^
Record lock：对单个索引项加锁
Gap lock：间隙锁，对索引项之间的"间隙",第一条记录前的"间隙"或最后一条记录后的"间隙"加锁，不包括索引项本身
Next-key lock：Gap lock+Next-key lock 锁定索引项范围。对记录及其前面的间隙加锁
mysql> select * from source_tab; +------+------+--------+ | id | age | name | +------+------+--------+ | 1 | 24 | yayun | | 2 | 24 | atlas | | 3 | 25 | david | | 4 | 24 | dengyy | +------+------+--------+ 4 rows in set (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> desc source_tab; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc target_tab; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
CTAS操作给原表加锁的例子
session1操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from source_tab; +------+------+--------+ | id | age | name | +------+------+--------+ | 1 | 24 | yayun | | 2 | 24 | atlas | | 3 | 25 | david | | 4 | 24 | dengyy | +------+------+--------+ 4 rows in set (0.00 sec) mysql> insert into target_tab select * from source_tab where name='yayun'; #该语句执行以后，session2中的update操作将会等待 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.04 sec) mysql>
session2操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from source_tab; +------+------+--------+ | id | age | name | +------+------+--------+ | 1 | 24 | yayun | | 2 | 24 | atlas | | 3 | 25 | david | | 4 | 24 | dengyy | +------+------+--------+ 4 rows in set (0.00 sec) mysql> update source_tab set name='dengyayun' where name='yayun'; #一直等待，除非session1执行commit提交。 Query OK, 1 row affected (49.24 sec) #可以看见用了49秒，这就是在等待session1提交，当session1提交后，顺利更新 Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql>
在上面示例中，只是简单的读source_tab表的数据，相当于执行一个普通的SELECT语句，用一致性读就可以了。Oracle正是这么做的，它通过MVCC技术实现的多版本并发控制实现一致性读，不需要给source_tab加任何锁。大家都知道InnoDB也实现了多版本并发控制(MVCC),对普通的SELECT一致性读，也不需要加任何锁；但是这里InnoDB却给source_tab表加了共享锁，并没有使用多版本一致性读技术。
MySQL为什么这么做呢？why？其原因还是为了保证恢复和复制的正确性。因为在不加锁的情况下，如果上述语句执行过程中，其他事务对原表（source_tab）做了更新操作，就可能导致数据恢复结果错误。为了演示错误的发生，再重复上面的例子，先将系统变量innodb_locks_unsafe_for_binlog的值设为"on",默认值是off。
innodb_locks_unsafe_for_binlog
其无法动态修改，需要修改配置文件，演示如下：
CTAS操作不给原表加锁带来的安全问题
mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_locks_unsafe%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_locks_unsafe_for_binlog | ON | +--------------------------------+-------+ 1 row in set (0.00 sec) mysql>
session1操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from source_tab where id=1; +------+------+-----------+ | id | age | name | +------+------+-----------+ | 1 | 24 | dengyayun | +------+------+-----------+ 1 row in set (0.00 sec) mysql> insert into target_tab select * from source_tab where id=1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; #插入操作后提交 Query OK, 0 rows affected (0.01 sec) mysql> select * from source_tab where name='good yayun'; #此时查看数据，target_tab中可以插入source_tab更新前的结果，这复合应用逻辑 +------+------+------------+ | id | age | name | +------+------+------------+ | 1 | 24 | good yayun | +------+------+------------+ 1 row in set (0.00 sec) mysql> select * from target_tab; +------+------+-----------+ | id | age | name | +------+------+-----------+ | 1 | 24 | dengyayun | +------+------+-----------+ 1 row in set (0.00 sec)
session2操作
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from source_tab where id=1; +------+------+-----------+ | id | age | name | +------+------+-----------+ | 1 | 24 | dengyayun | +------+------+-----------+ 1 row in set (0.00 sec) mysql> update source_tab set name='good yayun' where id=1; # session1未提交，可以对session1中的select记录进行更新操作 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # 更新操作先提交 Query OK, 0 rows affected (0.02 sec) mysql> select * from source_tab where name='good yayun'; +------+------+------------+ | id | age | name | +------+------+------------+ | 1 | 24 | good yayun | +------+------+------------+ 1 row in set (0.00 sec) mysql> select * from target_tab; +------+------+-----------+ | id | age | name | +------+------+-----------+ | 1 | 24 | dengyayun | +------+------+-----------+ 1 row in set (0.00 sec) mysql>
从上面的测试结果可以发现，设置系统变量innodb_locks_unsafe_for_binlog的值为"ON"后，innodb不再对原表（source_tab）加锁，结果也符合应用的逻辑，但是如果我们分析一下BINLOG内容，就可以发现问题所在
[root@MySQL-01 mysql]# mysqlbinlog mysql-bin.000120 | grep -A 20 'update source_tab' update source_tab set name='good yayun' where id=1 /*!*/; # at 468 #140401 2:04:12 server id 1 end_log_pos 495 Xid = 74 COMMIT/*!*/; # at 495 #140401 2:04:23 server id 1 end_log_pos 563 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1396289063/*!*/; BEGIN /*!*/; # at 563 #140401 2:02:42 server id 1 end_log_pos 684 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1396288962/*!*/; insert into target_tab select * from source_tab where id=1 /*!*/; # at 684 #140401 2:04:23 server id 1 end_log_pos 711 Xid = 73 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; [root@MySQL-01 mysql]#
可以清楚的看到在BINLOG的记录中，更新操作的位置在INSERT......SELECT之前，如果使用这个BINLOG进行数据库恢复，恢复的结果则与实际的应用逻辑不符；如果进行复制，就会导致主从数据不一致！
通过上面的例子，相信童鞋们不难理解为什么MySQL在处理
"INSERT INTO target_tab SELECT * FROM source_tab WHERE...."
"CREATE TABLE new_tab....SELECT.....FROM source_tab WHERE...."
时要给原表（source_tab）加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条件，innodb还会给原表加上Next-Key Lock锁。
因此，INSERT....SELECT和CREATE TABLE....SELECT.....语句，可能会阻止对原表的并发更新。如果查询比较复杂，会照成严重的性能问题，生产环境需要谨慎使用。
总结如下：
如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以使用下面3种方法：
1.将innodb_locks_unsafe_for_binlog的值设置为"ON",强制MySQL使用多版本数据一致性读。但付出的代价是可能无法使用BINLOG正确的进行数据恢复或者主从复制。因此，此方法是不推荐使用的。
2.通过使用SELECT * FROM source_tab ..... INTO OUTFILE 和LOAD DATA INFILE.....语句组合来间接实现。采用这种放松MySQL不会给（源表）source_tab加锁。
3.使用基于行（ROW）的BINLOG格式和基于行的数据的复制。此方法是推荐使用的方法。
参考资料：
https://www.facebook.com/note.php?note_id=131719925932
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
