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?