MySQL Backup Lock简介

举报
GaussDB数据库 发表于 2019/06/12 15:28:21 2019/06/12
【摘要】 2.1 oracle lock vs percona lock Backup lock 分为 oracle lock 和percona table lock, oracle lock是针对instance的,而percona是针对table的。percona代码两者都支持。 oracle LOCK INSTANCE FOR BACKUP acquires an ins...

2.1 oracle lock vs percona lock   

Backup lock 分为 oracle lock 和percona table lock, oracle lock是针对instance的,而percona是针对table的。percona代码两者都支持。

oracle

LOCK INSTANCE FOR BACKUP acquires an instance-level backup lock that permits DML during an online backup while preventing operations that could result in an inconsistent snapshot.

 

percona

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to non-transactional tables and DDL statements for all tables.

-cpp代码

01

/**


02

  Object   namespaces.

 

03

  Sic:   when adding a new member to this enum make sure to

04

  update   m_namespace_to_wait_state_name array in mdl.cc!


 

05



06

  Different   types of objects exist in different namespaces

 

07

   -   GLOBAL is used for the global read lock.

08

   -   TABLESPACE is for tablespaces.


 

09

   -   SCHEMA is for schemas (aka databases).

10

   -   TABLE is for tables and views.


 

11

   -   FUNCTION is for stored functions.


12

   -   PROCEDURE is for stored procedures.

 

13

   -   TRIGGER is for triggers.


14

   -   EVENT is for event scheduler events.

 

15

   -   COMMIT is for enabling the global read lock to block commits.

16

   -   USER_LEVEL_LOCK is for user-level locks.


 

17

   -   LOCKING_SERVICE is for the name plugin RW-lock service

18

   -   SRID is for spatial reference systems


 

19

   -   ACL_CACHE is for ACL caches


20

   -   COLUMN_STATISTICS is for column statistics, such as histograms

 

21

   -   BACKUP_LOCK is to block any operations that could cause


22

     inconsistent   backup. Such operations are most DDL statements,

 

23

     and   some administrative statements.


24

   -   RESOURCE_GROUPS is for resource groups.

 

25

   -   FOREIGN_KEY is for foreign key names.


26

  Note   that requests waiting for user-level locks get special

 

27

  treatment   - waiting is aborted if connection to client is lost.

28

*/


 

29

enum enum_mdl_namespace   {

30

  GLOBAL   = 0,


 

31

  TABLESPACE,

32

  SCHEMA,


 

33

  TABLE,


34

  FUNCTION,

 

35

  PROCEDURE,

36

  TRIGGER,


 

37

  EVENT,


38

  COMMIT,

 

39

  USER_LEVEL_LOCK,

40

  LOCKING_SERVICE,

 

41

  SRID,


42

  ACL_CACHE,

 

43

  COLUMN_STATISTICS,


44

  BACKUP_LOCK, /*   Oracle LOCK INSTANCE FOR BACKUP */

 

45

  RESOURCE_GROUPS,

46

  FOREIGN_KEY,


 

47

  BACKUP_TABLES, /*   Percona LOCK TABLES FOR BACKUP */

48

  /*   This should be the last ! */


 

49

  NAMESPACE_END

50

};


 

2.2 LOCK TABLES FOR BACKUP   

LOCK TABLES FOR BACKUP 使用MDL锁完成以下两个使命:
1. 阻塞(block)非事务表(MyISAM, CSV, MEMORY and ARCHIVE tables) 的更新操作
2. 阻塞(block)DDL statements for all tables: SELECT queries for all tables and INSERT/REPLACE/UPDATE/DELETE against InnoDB, Blackhole and Federated tables are not affected by LOCK TABLES FOR BACKUP.

FTWRL相比,LTFB 并不flush tables (存储引擎并不强制关闭tables,table cache也不清理tables) LTFB仅仅等待 conflicting statements to complete (i.e. DDL and updates to non-transactional tables),无需等待SELECTs, or UPDATEs to InnoDB tables to complete, for example.

3.1 lock table for backup 用法   
启动mysqld,连接 server,输入 LOCK TABLES FOR BACKUP,如果此时有相关DDL操作则直接返回

ERROR 1880 (HY000): Can't execute the query because you have a conflicting backup lock

 

UNLOCK TABLES releases the lock acquired by LOCK TABLES FOR BACKUP.

3.3 MySQL 8.0 lock table 测试   

3.3.1 lock tables

1.png

                                Fig 3.1 lock table; create table; update table

使用 LOCK TABLES FOR BACKUP 锁定table, 创建table t1 失败(DDL操作), 但是DML操作 select 与update 均成功。

3.3.2 unlock tables

2.png

                  Fig 3.2  unlock table; create table;     

解除锁定 unlock tables; 再次创建table t1 成功。

3.3.3 backup lock 说明

mysql 只需一个连接lock即可锁住其他DDL操作

3.png

           Fig 3.3 connect1 lock unlock; connect2 create t3(title); connect3 create t3(author);


connect 1 锁定 tables 以后,阻塞connect2 和connect3 创建table, 当connect1 解锁以后,可以看到其余两个connect创建成功, desc t3创建 成功(取决于哪个连接先拿到lock)

 

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。