One thing I commonly hear when working with my clients is “I want
to change my DDL strategy in order to avoid locking in my
database! The last time I used the same old method I ended up in
a metadata lock situation!”
I agree that metadata locks can be painful, but unfortunately,
it’s completely unavoidable, and changing from one tool to
another won’t help with this. That said, it’s still worth it to
examine how metadata locks work and what the impact is for each
of the common tools and processes. In doing so we will see that
all these tools will require metadata locks, but knowing more
about how they work and how the use locking can help us determine
the right tool for your specific use case.
Any time you make a change to a table a metadata lock is needed
to ensure consistency between the table itself and MySQL’s data
dictionary. In order for MySQL to establish this lock it has to
wait for any query against the table in …
[Read more]