Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
The ALTER and the SELECT are executed from different users. How can we switch the reading from Slave to the Master, flexibility? –  quanta Apr 18 '13 at 3:32

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.

share|improve this answer

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.

share|improve this answer
5  
I don't think this is what the person asking the question is about. quanta is asking for a solution that doesn't block (presumably so that the overall response time of his database stay good) - restarting the service will make it unavailable and fail all current transactions. –  Mat Mar 10 '13 at 7:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.