Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

Question

I have some tables that many users need to access to:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (734863) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (734959) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (735141) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (735210) ENGINE = InnoDB,
 PARTITION MERGER_2013227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION pcurrent_2013227 VALUES LESS THAN (735292) ENGINE = InnoDB) */

When an user want to drop a partition ALTER TABLE v3_cam_date DROP PARTITION pcurrent_2013227, it may cause many transactions to be in the Waiting for table metadata lock state:

     Id: 31560182
   User: alice
   Host: 192.168.3.40:36132
     db: db
Command: Query
   Time: 806
  State: Waiting for table metadata lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _campaigncomplete
        FROM `ox_campaigns` A 
        INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
        INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
        WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(    (SELECT `sys_date_cpc` FROM `000_sys_params_v4`) , INTERVAL 1 DAY) 
                AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',17) ) ,A.`uid` =  NAME_CONST('userid',5770)))

What is the efficient way to accomplish this without locking?

Answer 1

What you are asking for is impossible. Regardless of storage engine, DDL of any kind will lock a table. If you must remove a partition from a table that is active, you should:

1. Setup MySQL Replication (if you haven't done so already)
2. Perform all SELECTs involving v3_cam_date against the Slave
3. STOP SLAVE; on the Slave
4. Perform ALTER TABLE ... DROP PARTITION on the Master.
5. Perform all SELECTs involving v3_cam_date against the Master
6. START SLAVE; on the Slave (Replicates ALTER TABLE ... DROP PARTITION to the Slave)

This is probably your only recourse. The only other recourse is to simply wait out the ALTER TABLE ... DROP PARTITION.

This situation requires some intervention in the application. Within your application, you would have to create a Write DBVIP on the Master and use the Read DBVIP with one of the following three(3) options:

OPTION #1

• Keep the Read DBVIP on the Slave

OPTION #2

• Keep the Read DBVIP on the Master
• When you must do an ALTER TABLE, move the Read DBVIP to the Slave
• When ALTER TABLE has been completed, move the Read DBVIP to the Master

OPTION #3

• Setup Read DBVIP on a LoadBalancer
• Remove Master from LoadBalanced DBVIP on demand

Option #1 seems to be the simplest way in the long run.

Answer 2

To avoid this kind of problem insure to commit or rollback your transaction.

due to SELECT table is locked in shared mode.

Here is example

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from test order by id;
+----+------+
| id | a    |
+----+------+
|  1 | x  |
|  2 | y  |
+----+------+
2 rows in set (0.00 sec)

session2 > ALTER TABLE test add column c char(32) default 'xyz';

session3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State                           | Info                                                        |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
|  1 | sameer | localhost | test | Sleep   |  253|                                 | NULL                                                        |
|  2 | sameer | localhost | test | Query   |    3 | Waiting for table metadata lock | ALTER TABLE test add column c char(32) default 'xyz' |
|  3 | sameer | localhost | test | Query   |    0 | NULL                            | show processlist                                            |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)


session1 > rollback;
Query OK, 0 rows affected (0.00 sec)

session2 > ALTER TABLE test add column c char(32) default 'xyz';
Query OK, 2 rows affected (46.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

You can kill all sessions which are in sleep mode or exit session from where SELECT executed.

Answer 3

I've solved this issue by just restarting the MySQL service, and then try to drop the partition. After this the drop partition will take effect. This happens because the binlog apparently has something that hasn't finished with that table.

I hope this information could be helpful for you.