通过binlog文件对增删除改等更新数据的sql语句

1. 恢复和复制的需要,对innodb锁机制的影响

  mysql
通过binlog文件对增删除改等更新数据的sql语句,实现数据库的恢复和主从复制。mysql的恢复机制(复制其实就是在slave
mysql不断做基于binglog的恢复)特点有如下:
  (1) mysql 的恢复是sql语句级的,也就是重新执行binlog中的sql语句,
oracle数据库则是基于数据库文件块的。
  (2) mysql
的binlog是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的。这也与oracle不同,oracle是按照系统更新号(SCN)来恢复数据的。

2.  insert into 和create table对于原表也会加共享锁
 
 下面演示原表加锁的例子:

会话1

会话2

SET autocommit=0;

SELECT * FROM city WHERE CityCode=’003′

city_id      country_id        cityname CityCode

103  2       杭州         003

SET autocommit=0;

SELECT * FROM city WHERE CityCode=’003′

city_id      country_id        cityname CityCode

103  2       杭州         003

INSERT INTO  cityNew

SELECT  * FROM city WHERE CityCode=’003′

共 1 行受到影响

 

 

UPDATE city SET CityCode=’004′ WHERE CityCode=’003′

等待超时

Lock wait timeout exceeded; try restarting transaction

Commit;

 

 

Commit;

  上面的例子中,只是简单的读取city表,相当于一个普通的select
语句,在这里innodb给city表加了共享锁,并有使用多版本数据一致性技术。原因还是为了保证恢复和复制的正确性,因为不加锁,上述语句的执行过程中,其他事务对city表做了更新操作,可能导致数据恢复结果错误。如需要演示这种可以将系统变量
innodb_locks_unsafe_for_binlog的值设置为”NO”不加共享锁(set
innodb_locks_unsafe_for_binlog=’on’) 默认是”OFF”
。如果设置上面的值为ON,
可能会使Binlog中记录的sql执行顺序不一致,使用恢复的结果与实际的应用逻辑不符,如果进行复制,就会导致主从数据库不一致。
  如果不想设置为ON,又不希望对源表的并发更新产生影响,可以使用 into
outfile 将city表导入到一个txt文件,再使用load data infile
导入到新表。使用这种间接方式不会对源city表加锁。

 

相关文章