mysql innodb的行锁(6) --不安全语句加锁

一般情况下select 使用mvcc的技术，是不加行锁的，但是对于insert ... select , create table .. select 等不安全语句，会自动对源表加共享锁
当然是否加锁还受到下面参数控制, 因为这个加锁不是隔离级别的原因，而是为了复制安全。

root@sakila 11:03:59>show variables like '%unsafe%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.00 sec)

会话1：
root@sakila 11:02:28>insert into target select * from tab_no_index;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

会话2:
root@sakila 11:03:02>update tab_no_index set name=name where name='1';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

*************************************************************************************
如果修改变量，则不会对上面的select 的源表加锁，但是在statement 的复制模式下，复制会出现问题
set global innodb_locks_unsafe_for_binlog=1;

mysqlbinlog mysql-bin.000039 | more 结果分析如下： 因为记录bin log是按提交顺序记录的，所以在执行insert ... select 语句前，已经执行了update了，而主库是先执行insert.. select ,再执行update. 所以两者的结果是不一样的，所以是不安全的。

#170312 23:18:14 server id 2552763370  end_log_pos 328 CRC32 0x6262bb7c         Query   thread_id=2     exec_time=0     error_code=0
use `sakila`/*!*/;
SET TIMESTAMP=1489331894/*!*/;
update tab_no_index set name='8' where name='1'
/*!*/;
# at 328
#170312 23:18:32 server id 2552763370  end_log_pos 359 CRC32 0xebeef64e         Xid = 20
COMMIT/*!*/;
# at 359
#170312 23:17:41 server id 2552763370  end_log_pos 442 CRC32 0xae75ad5e         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1489331861/*!*/;
BEGIN
/*!*/;
# at 442
#170312 23:17:41 server id 2552763370  end_log_pos 580 CRC32 0x3368f120         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1489331861/*!*/;
insert into target select * from tab_no_index where name='1'
/*!*/;
# at 580
#170312 23:18:48 server id 2552763370  end_log_pos 611 CRC32 0x1b39b7d7         Xid = 19
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

综述：
避免上锁又不影响复制的最优方式是：
innodb_locks_unsafe_for_binlog =1 (允许不安全的语句)
同时把
binlog_format=row  (避免不安全的语句)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/674865/viewspace-2135289/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/674865/viewspace-2135289/