MySQL表锁

测试数据

DROP TABLE IF EXISTS test.accounts;
CREATE TABLE IF NOT EXISTS test.accounts(
    id INT(11) unsigned NOT NULL AUTO_INCREMENT,
    account VARCHAR(32) NOT NULL DEFAULT "" COMMENT "账号",
    balance INT(11) NOT NULL DEFAULT 0 COMMENT "余额",
    PRIMARY KEY(id),
    KEY(account)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '账户';

INSERT INTO test.accounts(account, balance) VALUES (
FLOOR(RAND()*(1000000-100000)+100000), 
FLOOR(RAND()*(10000-1000)+1000)
);

SELECT * FROM test.accounts;

表级表锁中的普通锁和元数据锁有何不同呢？

普通表锁

普通表锁也是分为读锁和写锁，数据库提供LOCK TABLES命令用于为当前线程加锁，使用UNLOCK TABLES进行释放锁。

若当前线程获得表的读锁，当前线程和其他线程只能从表中读取数据，都无法写入数据。若当前线程获得表的写锁，那么只有拥有写锁的当前线程可以从表中读取和写入。

语句	描述	当前线程	其他线程
LOCK TABLES tblname READ	为表添加读锁	可读，写阻塞	可读，写等待
LOCK TABLES tblname WRITE	为表添加写锁	可读，可写	读等待，写等待
UNLOCK TABLES	释放表锁	-	-

写阻塞：当前线程写阻塞会出现错误信息

1099 - Table 'accounts' was locked with a READ lock and can't be updated

写等待：当先线程写等待无错误信息，命令行光标一直处于闪烁中，说明当前线程没有得到写锁定，一直处于等待中。

例如：为表添加读锁，表只能读取无法写入。

-	SessionA	SessionB	SessionC
1	LOCK TABLES test.accounts READ;	-	-
2	SELECT * FROM test.accounts;	SELECT * FROM test.accounts;	-
3	INSERT INTO test.accounts(account, balance) VALUES(1, 1); 写阻塞	INSERT INTO test.accounts(account, balance) VALUES(1, 1); 写等待	SHOW PROCESSLIST;
4	UNLOCK TABLES;	-	-

当SessionB插入数据时会出现写等待，此时在新的会话中查看进程列表，会出现“Waiting for table level lock”的状态信息。

mysql> SHOW PROCESSLIST;

+----+------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host            | db   | Command | Time | State                        | Info                                                                                                 |
+----+------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 11 | root | localhost:56919 | test | Sleep   |   25 |                              | NULL                                                                                                 |
| 12 | root | localhost:51626 | test | Sleep   | 1149 |                              | NULL                                                                                                 |
| 21 | root | localhost:53810 | test | Query   |    4 | Waiting for table level lock | INSERT INTO test.accounts(account, balance) VALUE(FLOOR((100000-10000)*RAND()+10000), FLOOR((10000-1 |
| 23 | root | localhost:56784 | test | Query   |    0 | NULL                         | SHOW PROCESSLIST                                                                                     |
+----+------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
4 rows in set

添加普通表锁后会对当前加锁线程后续的数据库操作产生影响，在没有出现行锁之前，都是通过表锁进行并发控制，由于表锁影响面太大限制太严格，不便于控制和使用。

元数据锁 MDL

为了在并发环境下维护表元数据一致性，在表上有活动事务时，不可以对元数据执行写入操作。从MySQL5.5开始引入MDL( Metadata Lock)元数据锁，来保护表的元数据信息，用于解决或保证DDL操作（结构变更）和DML操作（增删改）之间的一致性。

对于引入的MDL主要是为了解决两个问题：事务隔离问题、数据复制问题

MDL不需要主动加锁，每当访问一个数据表时会自动被加上，作用是防止在进行表操作时进行表的变更。当对一个表进行增删改查时将自动填加MDL读锁，当对一个表进行结构变更时将自动添加MDL写锁。每次进行DQL操作(SELECT查询)或DML的时候，对表添加的都是MDL的读锁。而进行DDL操作时，对表添加的则是MDL写锁。

操作	锁	描述
DQL	MDL的S锁	查询操作添加MDL读锁
DML	MDL的S锁	增删改操作添加MDL读锁
DDL	MDL的X锁	表结构变更操作添加MDL写锁

MDL读写锁之间的互斥关系

MDL互斥	S锁	X锁
S锁	N	Y
X锁	Y	Y

例如：会话A在两次查询期间，会话B对表结构做了修改（删除表）。

-	SessionA	SessionB	SessionC	备注
1	BEGIN;			SessionA 开启事务
2	SELECT * FROM test.accounts;			SessionA DQL S锁
3		DROP TABLE test.accounts（等待）		SessionB DDL X锁
4	SELECT * FROM test.accounts		SHOW PROCESSLIST;	SessionA DQL S锁
5	ROLLBACK;	删除表

会话B删除表时出现等待，此时它的进程状态是Waiting for table metadata lock。

mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------+
| Id | User | Host            | db   | Command | Time | State                           | Info                     |
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------+
| 11 | root | localhost:56919 | test | Sleep   |   10 |                                 | NULL                     |
| 12 | root | localhost:51626 | test | Sleep   | 2152 |                                 | NULL                     |
| 21 | root | localhost:53810 | test | Query   |   14 | Waiting for table metadata lock | DROP TABLE test.accounts |
| 23 | root | localhost:56784 | test | Query   |    0 | NULL                            | SHOW PROCESSLIST         |
+----+------+-----------------+------+---------+------+---------------------------------+--------------------------+
4 rows in set

会话B为什么会处于等待状态呢？

会话A开启事务并开启查询，此时获得MDL锁，锁模式为SHARED_READ共享读模式。
会话B执行DDL变更结构，此时获得MDL锁，锁模式为EXECLUSIVE排它锁模式。
会话A的MDL读锁与会话B的MDL写锁互斥，所以会话B进入等待。

例如：多个人同时操作数据库时，某人给表添加字段时整个表挂了，接下来的操作都将失败或不返回。

#	SessionA	SessionB	SessionC	SessionD
1	BEGIN;
2	SELECT * FROM t LIMIT 1;
3		SELECT * FROM t LIMIT 1;
4			ALTER TABLE t ADD f INT;`block`
5				SELECT * FROM t LIMIT 1;`block`

SessionC的操作为什么会被阻塞呢？

SessionA开启事务并支持查询，SessionB执行查询，SessionC执行修改表操作，此时SessionA的事务并未结束，MDL会随着事务的开启而加锁，事务的结束而释放锁。因此，SessionA此时保持住了MDL的读锁。SessionC想要获取MDL的写的时候，由于读写互斥，SessionC就会阻塞（block）了。

如何安全的对一个表进行添加字段操作呢？

在information_schema库的innodb_tx表中查找到当前正在执行的事务，若是长事务则可以考虑kill掉。
若是频繁访问的短事务比较多，可使用ALTER TABLE table_name WAIT n ADD colname这种类型的操作，若拿不到MDL写锁，一段时间会释放阻塞，不长期影响数据库。

既然InnoDB存储引擎已经有了意向锁，为什么还需要元数据锁呢？

由于MySQL是Server-Engine架构，所有MDL锁是在Server中实现的。另外，MDL还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁。这些都是InnoDB存储引擎不能直接实现的锁。与InnoDB锁实现一样，MDL也是类似对树的各个对象从上向下进行加锁，但MDL对象的层次更多。

MDL对象层次

MDL按对象或范围划分

属性	含义	分类
GLOBAL	全局锁	范围
COMMIT	提交保护锁	范围
SCHEMA	库锁	对象
TABLE	表锁	对象
FUNCTION	函数锁	对象
PROCEDURE	存储过程锁	对象
TRIGGER	触发器锁	对象
EVENT	事件锁	对象

MDL类型

MDL_INTENTION_EXCLUSIVE
意向排它元数据锁，只是在范围锁上使用，持有后才能升级到排他锁，与其他的IX锁兼容，与范围S和X锁不兼容。
MDL_SHARED
共享元数据锁，用于对元数据感兴趣且不访问数据的情况。
MDL_SHARED_HIGH_PRIO
高优先级共享元数据锁，高优先级别会忽略为了排他锁而生成的堆积请求，直接被授予。
MDL_SHARED_READ
共享读锁是在需要读取表中的数据时获取的共享元数据锁
MDL_SHARED_WRITE
在需要修改表中的数据时发生的共享元数据锁
MDL_SHARED_UPGRADABLE
当需要修改数据时一个可升级的共享元数据锁，可升级到DL_SHARED_NO_WRITE和MDL_EXCLUSIVE
MDL_SHARED_NO_WRITE
一个可以升级的元数据锁，阻止所有的数据更新，允许读取。
MDL_SHARED_NO_READ_WRITE
一个可以升级的共享元数据锁，其他的连接可以访问表的元数据，不是数据。阻止所有读取修改表数据的请求，持有这个锁的连接可以读取表的元数据，修改读取表的数据。
MDL_EXCLUSIVE
排他元数据锁，持有该锁的连接可以修改表的元数据和数据，当持有这个锁的时候其他元数据所类型都不被授予。

全局锁

全局锁是对整个数据库实例加锁，MySQL提供了一个加锁语句：FTWRL

mysql> FLUSH TABLES WITH READ LOCK

FTWRL能使整个实例上只读，所有的写和更新都会被阻塞。全局锁典型应用场景是做全局的数据备份时使用。

全局备份时 InnoDB 存储引擎完全可通过MVCC创建一致性视图来保证不受备份中其他操作的影响
对数据库的全局锁，不仅会阻塞DML增删改查，同样对数据库表的的DDL增删改字段也会阻塞。
可通过设置全局只读来进行只读性的设置

全局只读

mysql> SET GLOBAL READONLY = true;

设置全局只读与FTWRL的区别在于有些数据库会把SET GLOBAL READONLY设置成备库的只读限制而不是用来做备份，影响面比较大。在异常处理机制上，FTWRL机制在客户端异常断开时数据库会主动释放全局的锁并恢复正常。而SET GLOBAL READONLY在客户端异常断开时不会恢复原状，数据库会一直处于只读状态，影响很大。