Bug #70790 ALTER TABLE REBUILD PARTITION SHOULD NOT PREVENT DML IN UNAFFECTED PARTITIONS

• Submitted: 31 Oct 2013 14:50
• Reporter: Arnaud Adant
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S4 (Feature request)
• Version: 5.6.14
• OS: Any
• CPU Architecture: Any
• Tags: partitioning online DDL

[31 Oct 2013 14:50] Arnaud Adant

Description:
In the 5.6 manual :

http://dev.mysql.com/doc/refman/5.6/en/online-ddl-partitioning.html

"
For REORGANIZE PARTITION, REBUILD PARTITION, or ADD PARTITION or COALESCE PARTITION for a table partitioned by LINEAR HASH or LIST, concurrent queries are allowed. Data from the affected partitions is copied while holding a shared lock. 
"

The shared lock is in fact a shared metadata lock on the table as a whoke, not on the affected partitions. The table is read only during the ALTER.

How to repeat:
drop table if exists t;
 CREATE TABLE t (
  c1 bigint NOT NULL,
  c2 bigint NOT NULL,
  c3 bigint NOT NULL,
  c4 timestamp NOT NULL,
  c5 smallint(6),
  c6 varchar(255),
  c7 int,
  PRIMARY KEY (c1, c7),
  KEY c4 (c4),
  KEY c5 (c5)
) ENGINE=InnoDB 
PARTITION BY RANGE COLUMNS(c7) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30),
    PARTITION p3 VALUES LESS THAN (40),
    PARTITION p4 VALUES LESS THAN (50),
    PARTITION p5 VALUES LESS THAN (60),
    PARTITION p6 VALUES LESS THAN (70),
    PARTITION p7 VALUES LESS THAN (80),
    PARTITION p8 VALUES LESS THAN (90),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

set autocommit = 1;
set global innodb_flush_log_at_trx_commit = 0;
truncate table t;
insert into t(c1,c2,c3,c4,c5,c6,c7)
values(uuid_short(),uuid_short(),uuid_short(),now(),1,
lpad(rand()*1000,100,'0'), rand()*100);
insert into t(c1,c2,c3,c4,c5,c6,c7)
values(uuid_short(),uuid_short(),uuid_short(),now(),1,  
lpad(rand()*1000,100,'0'),rand()*100);
replace into t(c1,c2,c3,c4,c5,c6, c7) select
uuid_short(),uuid_short(),uuid_short(),now(),1, lpad(rand()*1000,100,'0'), rand()*100
from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t
t12, t t13, t t14, t t15,t t16, t t17, t t18, t t19, t t20;

In a session S1 :

run 

alter table t rebuild partition p0;

In a session S2, insert in another partition p9 for example :

insert into t(c1,c2,c3,c4,c5,c6,c7)
values(uuid_short(),uuid_short(),uuid_short(),now(),1,
lpad(rand()*1000,100,'0'), 100);

In session S3, notice that the insert is "waiting for metadata lock".

Suggested fix:
For those operations that do not change the table structure :

- only lock the affected partitions in shared mode, that would allow for concurrent DML (inserts, updates, ...) in the unaffected partitions.

-  allow for lock = none during REBUILD, REORGANIZE, OPTIMIZE, change the ROW_FORMAT, ...