mysql 数据库恢复锁定_MySQL数据恢复和复制对InnoDB锁机制的影响

最新推荐文章于 2024-08-23 20:00:57 发布

weixin_39785814

最新推荐文章于 2024-08-23 20:00:57 发布

阅读量94

点赞数

文章标签： mysql 数据库恢复锁定

本文链接：https://blog.csdn.net/weixin_39785814/article/details/113223986

版权

mysql> select * fromsource_tab;+------+------+--------+

| id | age | name |

+------+------+--------+

| 1 | 24 | yayun |

| 2 | 24 | atlas |

| 3 | 25 | david |

| 4 | 24 | dengyy |

+------+------+--------+

4 rows in set (0.00sec)

mysql> select * fromtarget_tab;

Emptyset (0.00sec)

mysql> descsource_tab;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql> desctarget_tab;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql>

CTAS操作给原表加锁的例子

session1操作

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * fromsource_tab;+------+------+--------+

| id | age | name |

+------+------+--------+

| 1 | 24 | yayun |

| 2 | 24 | atlas |

| 3 | 25 | david |

| 4 | 24 | dengyy |

+------+------+--------+

4 rows in set (0.00sec)

mysql> insert into target_tab select * from source_tab where name='yayun'; #该语句执行以后，session2中的update操作将会等待

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> commit;

Query OK,0 rows affected (0.04sec)

mysql>

session2操作

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * fromsource_tab;+------+------+--------+

| id | age | name |

+------+------+--------+

| 1 | 24 | yayun |

| 2 | 24 | atlas |

| 3 | 25 | david |

| 4 | 24 | dengyy |

+------+------+--------+

4 rows in set (0.00sec)

mysql> update source_tab set name='dengyayun' where name='yayun'; #一直等待，除非session1执行commit提交。

Query OK,1 row affected (49.24sec) #可以看见用了49秒，这就是在等待session1提交，当session1提交后，顺利更新

Rows matched:1 Changed: 1 Warnings: 0mysql> commit;

Query OK,0 rows affected (0.00sec)

mysql>

在上面示例中，只是简单的读source_tab表的数据，相当于执行一个普通的SELECT语句，用一致性读就可以了。Oracle正是这么做的，它通过MVCC技术实现的多版本并发控制实现一致性读，不需要给source_tab加任何锁。大家都知道InnoDB也实现了多版本并发控制(MVCC),对普通的SELECT一致性读，也不需要加任何锁；但是这里InnoDB却给source_tab表加了共享锁，并没有使用多版本一致性读技术。

MySQL为什么这么做呢？why？其原因还是为了保证恢复和复制的正确性。因为在不加锁的情况下，如果上述语句执行过程中，其他事务对原表(source_tab)做了更新操作，就可能导致数据恢复结果错误。为了演示错误的发生，再重复上面的例子，先将系统变量innodb_locks_unsafe_for_binlog的值设为"on",默认值是off。

innodb_locks_unsafe_for_binlog

设定InnoDB是否在搜索和索引扫描中使用间隙锁(gap locking)。InnoDB使用行级锁(row-level locking)，通常情况下，InnoDB在搜索或扫描索引的行锁机制中使用“下一键锁定(next-key locking)”算法来锁定某索引记录及其前部的间隙(gap)，以阻塞其它用户紧跟在该索引记录之前插入其它索引记录。站在这个角度来说，行级锁也叫索引记录锁(index-record lock)。

默认情况下，此变量的值为OFF，意为禁止使用非安全锁，也即启用间隙锁功能。将其设定为ON表示禁止锁定索引记录前的间隙，也即禁用间隙锁，InnoDB仅使用索引记录锁(index-record lock)进行索引搜索或扫描，不过，这并不禁止InnoDB在执行外键约束检查或重复键检查时使用间隙锁。

启用innodb_locks_unsafe_for_binlog的效果类似于将MySQL的事务隔离级别设定为READ-COMMITTED，但二者并不完全等同：innodb_locks_unsafe_for_binlog是全局级别的设定且只能在服务启动时设定，而事务隔离级别可全局设定并由会话级别继承，然而会话级别也以按需在运行时对其进行调整。类似READ-COMMITTED事务隔离级别，启用innodb_locks_unsafe_for_binlog也会带来“幻影问题(phantom problem)”，但除此之外，它还能带来如下特性：

(1)对UPDATE或DELETE语句来说，InnoDB仅锁定需要更新或删除的行，对不能够被WHERE条件匹配的行施加的锁会在条件检查后予以释放。这可以有效地降低死锁出现的概率；

(2)执行UPDATE语句时，如果某行已经被其它语句锁定，InnoDB会启动一个“半一致性(semi-consistent)”读操作从MySQL最近一次提交版本中获得此行，并以之判定其是否能够并当前UPDATE的WHERE条件所匹配。如果能够匹配，MySQL会再次对其进行锁定，而如果仍有其它锁存在，则需要先等待它们退出。

其无法动态修改，需要修改配置文件，演示如下：

CTAS操作不给原表加锁带来的安全问题

mysql> show variables like 'binlog_format';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00sec)

mysql> show variables like 'innodb_locks_unsafe%';+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_locks_unsafe_for_binlog | ON |

+--------------------------------+-------+

1 row in set (0.00sec)

mysql>

session1操作

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from source_tab where id=1;+------+------+-----------+

| id | age | name |

+------+------+-----------+

| 1 | 24 | dengyayun |

+------+------+-----------+

1 row in set (0.00sec)

mysql> insert into target_tab select * from source_tab where id=1;

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> commit; #插入操作后提交

Query OK,0 rows affected (0.01sec)

mysql> select * from source_tab where name='good yayun'; #此时查看数据，target_tab中可以插入source_tab更新前的结果，这复合应用逻辑+------+------+------------+

| id | age | name |

+------+------+------------+

| 1 | 24 | good yayun |

+------+------+------------+

1 row in set (0.00sec)

mysql> select * fromtarget_tab;+------+------+-----------+

| id | age | name |

+------+------+-----------+

| 1 | 24 | dengyayun |

+------+------+-----------+

1 row in set (0.00 sec)

session2操作

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * from source_tab where id=1;+------+------+-----------+

| id | age | name |

+------+------+-----------+

| 1 | 24 | dengyayun |

+------+------+-----------+

1 row in set (0.00sec)

mysql> update source_tab set name='good yayun' where id=1; # session1未提交，可以对session1中的select记录进行更新操作

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> commit; # 更新操作先提交

Query OK,0 rows affected (0.02sec)

mysql> select * from source_tab where name='good yayun';+------+------+------------+

| id | age | name |

+------+------+------------+

| 1 | 24 | good yayun |

+------+------+------------+

1 row in set (0.00sec)

mysql> select * fromtarget_tab;+------+------+-----------+

| id | age | name |

+------+------+-----------+

| 1 | 24 | dengyayun |

+------+------+-----------+

1 row in set (0.00sec)

mysql>

从上面的测试结果可以发现，设置系统变量innodb_locks_unsafe_for_binlog的值为"ON"后，innodb不再对原表(source_tab)加锁，结果也符合应用的逻辑，但是如果我们分析一下BINLOG内容，就可以发现问题所在

[root@MySQL-01 mysql]# mysqlbinlog mysql-bin.000120 | grep -A 20 'update source_tab'update source_tab set name='good yayun' where id=1

/*!*/;

# at468#140401 2:04:12 server id 1 end_log_pos 495 Xid = 74COMMIT/*!*/;

# at495#140401 2:04:23 server id 1 end_log_pos 563 Query thread_id=5 exec_time=0 error_code=0SET TIMESTAMP=1396289063/*!*/;

BEGIN/*!*/;

# at563#140401 2:02:42 server id 1 end_log_pos 684 Query thread_id=5 exec_time=0 error_code=0SET TIMESTAMP=1396288962/*!*/;

insert into target_tabselect * from source_tab where id=1

/*!*/;

# at684#140401 2:04:23 server id 1 end_log_pos 711 Xid = 73COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;

[root@MySQL-01 mysql]#

可以清楚的看到在BINLOG的记录中，更新操作的位置在INSERT......SELECT之前，如果使用这个BINLOG进行数据库恢复，恢复的结果则与实际的应用逻辑不符；如果进行复制，就会导致主从数据不一致！

通过上面的例子，相信童鞋们不难理解为什么MySQL在处理

"INSERT INTO target_tab SELECT * FROM source_tab WHERE...."

"CREATE TABLE new_tab....SELECT.....FROM source_tab WHERE...."

时要给原表(source_tab)加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条件，innodb还会给原表加上Next-Key Lock锁。

因此，INSERT....SELECT和CREATE TABLE....SELECT.....语句，可能会阻止对原表的并发更新。如果查询比较复杂，会照成严重的性能问题，生产环境需要谨慎使用。

总结如下：

如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以使用下面3种方法：

1.将innodb_locks_unsafe_for_binlog的值设置为"ON",强制MySQL使用多版本数据一致性读。但付出的代价是可能无法使用BINLOG正确的进行数据恢复或者主从复制。因此，此方法是不推荐使用的。

2.通过使用SELECT * FROM source_tab ..... INTO OUTFILE 和LOAD DATA INFILE.....语句组合来间接实现。采用这种放松MySQL不会给(源表)source_tab加锁。

3.使用基于行(ROW)的BINLOG格式和基于行的数据的复制。此方法是推荐使用的方法。

参考资料：

https://www.facebook.com/note.php?note_id=131719925932

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog

weixin_39785814

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫