Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you the difference between ONLINE DDL INPLACE and COPY two ways of MDL lock, I believe most people do not know how, so share this article for everyone's reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

MySQL online DDL is divided into INPLACE and COPY, which are specified by ALGORITM parameter in ALTER statement. In addition to different implementation methods, MDL locking in the whole process is also different.

For alter table test.t1 add column name varchar(10), algorithm=copy;, alter does this by copying to temporary. The MDL sequence throughout the process is as follows:

Prepare phase: Apply MDL_SHARED_UPGRADABLE lock first, then upgrade to MDL_SHARED_NO_WRITE lock

alter phase: MDL_SHARED_NO_WRITE lock

commit phase: upgrade to MDL_EXCLUSIVE lock

For the adder table test.t1 add column name varchar(10), algorithm=inplace;, if the adder uses inplace algorithm, the MDL locking sequence throughout the process is as follows:

Prepare phase: apply for MDL_SHARED_UPGRADABLE lock first, then upgrade to MDL_EXCLUSIVE lock, then downgrade to MDL_SHARED_UPGRADABLE lock

alter phase: MDL_SHARED_UPGRADABLE

commit phase: upgrade to MDL_EXCLUSIVE lock

MDL_SHARED_UPGRADABLE is compatible with SR locks acquired by select and SW locks acquired by dml

MDL_SHARED_NO_WRITE is compatible with SR locks acquired by select and conflicts with SW locks acquired by dml

MDL_EXCLUSIVE conflicts with SR lock acquired by select and SW lock acquired by dml

The above is "what is the difference between ONLINE DDL INPLACE and COPY two ways of MDL lock" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.