MySQL MDL Types & Compatibility Guide

MySQL’s MDL (Metadata Locking) is a lock mechanism used for managing and controlling database objects. The MDL locks allow simultaneous reading and writing operations on the same object without causing conflicts. Below is a brief explanation of the MySQL MDL types and compatibility matrix.

1. Shared Lock: Multiple transactions can hold a shared lock simultaneously for read operations. Shared lock allows other transactions to also hold shared locks but prevents them from holding exclusive locks.
2. Compatibility: Shared locks are compatible with other shared locks, but are not compatible with exclusive locks.
3. Exclusive Lock: Only one transaction can hold an exclusive lock, which is used for write operations. Exclusive locks prevent other transactions from holding shared locks or exclusive locks at the same time.
4. Compatibility: Exclusive locks are not compatible with any other locks.
5. Write Lock: It allows holding both a write lock and a shared lock simultaneously, used to execute statements that modify data.
6. Compatibility: Write locks are compatible with read locks, but incompatible with other write locks.
7. Metadata lock is used to lock the metadata of database objects, such as table structures and indexes, preventing other transactions from making changes to the same metadata.
8. Compatibility: Metadata locks are incompatible with any other locks.

It is important to note that in MySQL, MDL locks are automatically managed, so there is no need to manually acquire or release locks. The system will automatically acquire and release locks based on the operations of the transaction. Furthermore, MDL locks are transaction-based and only valid within the scope of the transaction.