mysql中恢复和复制的需要,对InnoDB锁机制的影响

mysql中恢复和复制的需要,对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT\UPDATE\DELETE等更新数据的SQL语句,并由此实现MYSQL数据库的恢复和主从复制,MYSQL的恢复机制有以下特点:

  • 一是MYSQL的恢复是SQL语句级的,也就是重新执行BINLOG中的SQL语名。这与ORACLE不同,ORACLE是基于数据库文件块的。
  • 二是MYSQL的BINLOG是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的。这点也与ORACLE不同,ORACLE是按照系统更新号(System Change Number,SCN)来恢复数据的,每个事务开始时,ORACLE都会分配一个全局唯一的SCN,SCN的顺序与事务开始的时间顺序是一致的。

从上面两条可知,MYSQL的恢复机制实际上是要求事务要串行化

另外,对于

insert into target_tab
select ... from source_tab where ...



create table new_tab
select ... from source_tab where ...

这种SQL语句,只是简单地读source_tab表的数据,相当于执行了一个普通的select语句,用一致性读就可以了。ORACLE正是这么做的,它通过MVCC技术实现的多版本数据来实现一至性读,不需要给source_tab加任何锁。但mysql的InnoDB却对source_tab加了共享锁,并没有使用MVCC一致性读。这是为了保证恢复和复制的正确性。

假设这样一种情况,session1和session2都开启了一个事务:

  1.  innodb_locks_unsafe_for_binlog变量设置为1,由于不能动态设置,要在配置文件中修改。这个选项默认值是0,意为“开启gap lock”,还可以设置为1,意为“禁用gap lock”。
  2. session1中使用insert into target_tab… select…source_tab这种方式插入数据。
  3. session2中对source_tab数据进行更新,然后提交。
  4. session1提交

观查结果,是符合应用逻辑的,不过在分析BINLOG的内容时会发现更新操作的位置在insert…select之前,如果使用这个BINLOG进行数据库恢复,恢复的结果与实际的应用逻辑不符合;如果进行复制,就会导致主从数据库不一致!(该案例并没有在进行测试,因为在设置innodb_locks_unsafe_for_binlog时系统提示变量为只读,通过查在线手册,该变量不能动态设置。)

这种查询比较复杂的话,会造成严重的性能问题,我们在应用中尽量避免使用。实际上mysql将这种SQL叫不确定的SQL,不推荐使用。

如果应用中一定要用这种SQL来实现业务逻辑,又不希望对源表的并发更新产生影响,可以采用以下两咱措施:

  • 将innodb_locks_unsafe_for_binlog的值设置为1,强制MYSQL使用MVCC。但它的代价是可能无法使用binlog正确地恢复或复制数据,因此不推荐使用这种方式。
  • 使用
    select * from source_tab ... into outfileload data infile...
    语句组合来间接实现,采用这种方式MYSQL不会给source_tab加锁。

发表评论