建站
咨询
售后
建站咨询
mysqlinnodb的行锁(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;
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 (避免不安全的语句)
当前名称：mysqlinnodb的行锁(6)--不安全语句加锁
文章源于：http://lswzjz.com/article/jihhje.html
当然是否加锁还受到下面参数控制, 因为这个加锁不是隔离级别的原因，而是为了复制安全。
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
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=0use `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 (避免不安全的语句)
当前名称：mysqlinnodb的行锁(6)--不安全语句加锁
文章源于：http://lswzjz.com/article/jihhje.html
