ITPub博客

首页 > 数据库 > MySQL > mysql innodb的行锁(6) --不安全语句加锁

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

原创 MySQL 作者:xchui702 时间:2017-03-14 05:32:58 0 删除 编辑
一般情况下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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-16

  • 博文量
    38
  • 访问量
    118187