MySql中的表级锁-DML

文章目录

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然MDL锁是系统默认会加的，但却是你不能忽略的一个机制。比如下面这个例子，我经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。

你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。我们来看一下下面的操作序列，假设表t是一个小表。

备注：这里的实验环境是MySQL 5.6。

Session A

mysql> begin;
mysql> select * from T limit 1;
+-----+---+------+
| id  | k | name |
+-----+---+------+
| 100 | 1 | NULL |
+-----+---+------+
1 row in set (0.00 sec)

Session B – 返回正常，查询需要读锁，读锁之间不互斥，所以即使Session A的读锁还没释放也没关系

mysql> select * from T limit 1;
+-----+---+------+
| id  | k | name |
+-----+---+------+
| 100 | 1 | NULL |
+-----+---+------+
1 row in set (0.00 sec)

Session C 卡住 – 因为有读的DML锁，修改表需要有DML写锁，读写冲突，所以需要等Session A 的读锁释放，Seesion C才能执行。

mysql> alter table T add f int;

Session D 卡住 – 因为Session C有写锁，读写冲突，要等Seesion C执行完才能执行完成。

mysql> select * from T;

查看Proecesslist

mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                      |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------+
|  7 | root | localhost | testdb | Query   |    0 | init                            | show processlist          |
|  8 | root | localhost | testdb | Sleep   |   69 |                                 | NULL                      |
|  9 | root | localhost | testdb | Query   |   33 | Waiting for table metadata lock | alter table T add f int   |
| 10 | root | localhost | testdb | Query   |   21 | Waiting for table metadata lock | SELECT * FROM `T` LIMIT 0 |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------+
4 rows in set, 1 warning (0.00 sec)

我们可以看到session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。

之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。

如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。

你现在应该知道了，事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。