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?
Best Answer
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:
v3_cam_date
against the SlaveSTOP SLAVE;
on the SlaveALTER TABLE ... DROP PARTITION
on the Master.v3_cam_date
against the MasterSTART SLAVE;
on the Slave (ReplicatesALTER 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
OPTION #2
ALTER TABLE
, move the Read DBVIP to the SlaveALTER TABLE
has been completed, move the Read DBVIP to the MasterOPTION #3
Option #1 seems to be the simplest way in the long run.