Metadata Locking and Deadlock Detection in MySQL 5.5

This document discusses metadata locking in MySQL 5.5. It begins with an introduction to metadata locking and defines problems related to ALTER statements violating ACID properties, breaking replication, and causing deadlocks. It then covers how metadata locks work, how deadlock detection is implemented, how storage engines can integrate with metadata locking, and the performance impact. Finally, it outlines the roadmap for future work related to cross-storage-engine deadlock detection, making DDL operations consistent across cluster nodes, and improving performance through sharding of metadata lock sets.

Uploaded by

Oleksiy Kovyrin

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

562 views14 pages

Metadata Locking and Deadlock Detection in MySQL 5.5

Uploaded by

Oleksiy Kovyrin

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 14

Metadata locking in

MySQL 5.5

Konstantin Osipov
Engineer @ Mail.Ru
kostja.osipov@gmail.com

These slides released under the Creative Commons
AttributionNoncommercialShare Alike License
Mastering a 25-foot saw
DDL vs. DML: an illustration
What if... a transactional catalog
Locking agents
Define the problem (2)

Four groups of bugs:

- ALTER, DROP violate ACID

- … or ... break replication
- deadlocks in LOCK TABLES, FLUSH,
SET GLOBAL read_only and ALTER
- DML doesn't scale or THANK YOU
LOCK_open
Define the problem (3)
In come metadata locks
Deadlock detection

How does a typical wait-for grpah look like?

How to plug in

Engines need to plug-in to avoid cross-storage-

engine deadlocks.

- it's safe to #include “mdl.h”, no extra deps

- subclass MDL_waitfor_subgraph to accept
- MDL_waitfor_graph_visitor
- let us know when you're waiting, by calilng
- MDL_context::will_wait_for()
Performance impact
The roadmap

- help us add cross-storage-engine deadlock

detection!
- thr_lock.c and handler::store_lock() need not be
mandatory any more
- consistent DDL across all NDB cluster
- shard MDL_set (performance++)
- instrumentation
Performance impact
That's it.