|
Transactions on a partitioned table creates a metadata lock on the entire table that prevents non-accessed partitions to be dropped while there are active transactions on the table on any partition. This in turn, if a drop partition is issued, any other transactions on this table to wait, independent on the transaction. The meta_data_lock plugin reports this:
+-----------+-------------------------+-----------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+-----------------+----------------------+--------------+------------+
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT | Global read lock | | |
|
| 6 | MDL_SHARED_WRITE | MDL_TRANSACTION | Table metadata lock | mydb | t2 |
|
| 5 | MDL_SHARED_UPGRADABLE | MDL_TRANSACTION | Table metadata lock | mydb | t2 |
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_TRANSACTION | Schema metadata lock | mydb | |
|
+-----------+-------------------------+-----------------+----------------------+--------------+------------+
|
This to an extent cause the most important aspect of partitioning, managing old data by dropping partitions, to be much less useful.
|