MySQL Backup Lock简介
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.
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
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
Fig 3.2 unlock table; create table;
解除锁定 unlock tables; 再次创建table t1 成功。
3.3.3 backup lock 说明
mysql 只需一个连接lock即可锁住其他DDL操作
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)
- 点赞
- 收藏
- 关注作者
评论（0）