一般情況下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 (避免不安全的語句)

mysql innodb的行鎖(6) --不安全語句加鎖

相關文章