基础材料:
centos7.5 mysql 5.7.24
前几篇博客中介绍了表级锁(表锁、元数据锁)的相关内容,在<mysql锁系列之MDL元数据锁之一>简单说明了一下元数据锁的优先级为写锁大于读锁,同为写锁要排队。对此我们进行如下测试,按session顺序逐个执行语句:
session108 | session109 | session110 | session111 | session112 | session113 | session114 | session115 | session116 |
begin; | lock tables testok write; | lock tables testok read; | alter table testok add p varchar(10); | begin; | lock tables testok write; | lock tables testok read; | alter table testok add pp varchar(10); | begin; |
select * from testok; | | | | select * from testok; | | | | select * from testok; |
按照之前的理论推算(以下锁均指元数据锁):
1、目前的情况应该是session108持有该表的读锁,其余8个session卡住,等待该读锁释放。实际情况符合预期。
2、当session108执行commit后,释放读锁,session109获取到该表的写锁,剩余7个session等待该写锁释放。实际情况为session111先执行并提交就没它什么事了,然后session109执行,用写锁锁住该表,然后其余6个session等待该写锁。
3、此时我们在session109上执行unlock tables;显示释放写锁,根据理论预测的结果为session113获得写锁。实际情况符合预期。(此处你可能对session115有疑问,先继续往下走)
实际情况为session110\112\114\116同时执行,并持有该表的读锁,session115等待该读锁。
5、为session110\112\114\116释放读锁,session115执行并提交。
可见理论预测与结果相差甚远,为什么会出现这种情况,下面具体分析一下:
首先在步骤1时,观察metadata_locks表的情况:
session108持有表读锁(SHARED_READ),这个没什么问题
session109因为要加写锁,期间加了全局意向排他锁、数据库意向排他锁(GRANTED),这个没什么问题。注意第4行,在testok上要加的锁为(SHARED_NO_READ_WRITE)(PENDING),虽然前缀为SHARED,但它实际上是写锁。此时要特别注意一下,它pending的原因是在等两个锁,一个是session108的SHARED_READ读锁,另一个是session111的SHARED_UPGRADABLE读锁,这也就解释了为什么同为写锁pending,排他它后面session111反而比它先执行。
session110申请读锁,PENDING了,按照前篇说的,由于session109要加写锁,它被阻塞,这个没什么问题。
session111因为要加写锁,期间加了全局意向排他锁、数据库意向排他锁(GRANTED)在第8行还对该表加了SHARED_UPGRADABLE读锁,用于online DDL的处理(GRANTED),而在申请表的EXCLUSIVE排他锁时,PENDING了,这个也没什么问题。(这里需要额外说一下SHARED_UPGRADABLE,排队规则的一个特例,它并不受前面的写锁阻塞依然可以获得锁。本身虽然是读锁,但同为SHARED_UPGRADABLE会互斥,也就是同一时间一张表上只能存在一个SHARED_UPGRADABLE读锁,这也是为什么同一时间只有一个DDL操作能进行),On-line DDL执行过程还有几种情况,将在《mysql锁系列之MDL元数据锁之三》进行说明。
session112申请读锁,PENDING了。
session113因为要加写锁,期间加了全局意向排他锁、数据库意向排他锁(GRANTED),申请(SHARED_NO_READ_WRITE),PENDING。
session114申请读锁,PENDING了。
session115加了全局意向排他锁、数据库意向排他锁(GRANTED),申请SHARED_UPGRADABLE读锁时,被session111的SHARED_UPGRADABLE读锁互斥
session116申请读锁,PENDING了。
了解了目前所有元数据锁的情况,我们逐步验证上面的实际情况:
1、在session108执行commit;,由于session111一直持有SHARED_UPGRADABLE读锁,所以session111先拿到写锁执行并提交(过程中虽然会降级读锁,但其他写锁session仍然无法获得锁),然后session109执行,用{写锁}锁住该表,然后其余6个session等待该写锁。此时metadata_locks如下:
可以看到目前是session109持有SHARED_NO_READ_WRITE写锁,session115没有先执行的原因是他目前在申请读锁,优先级低。
2、 在session109上执行unlock tables;显示释放写锁,此时metadata_locks如下:
此时session113持有SHARED_NO_READ_WRITE写锁。
3、在session113上执行,unlock tables;显示释放写锁,此时metadata_locks如下:
其余申请读锁的session拿到锁,而session115这个时刻也在申请读锁,所以同时拿到SHARED_UPGRADABLE读锁,然后再申请写锁,此时被其余seesion阻塞。
4、所有读锁session上释放锁后,session115成功执行,此时所有锁消失。
至此解释了元数据锁的加锁顺序,可以更好的理解元数据锁加锁过程中出现的一些问题。
注意事项:
SHARED_UPGRADABLE虽然是读锁,但其不会被前面的写锁阻塞,同为SHARED_UPGRADABLE会互斥,同一张表同一时间DDL操作不能并行,On-line DDL解决的是DDL与DML之间的并行问题,而不是DDL之间的并行。由于ONLINE DDL 存在三阶段过程,还要分几种情况,下一篇继续说明。