Description:
According to the documentation, see http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html:
* SELECT statements now lock only those partitions that actually need to be read.
* ALTER TABLE ... EXCHANGE PARTITION now prunes locks; only the exchanged table and the exchanged partition are locked.
However, ALTER TABLE ... EXCHANGE PARTITION still requires table metadata lock.
How to repeat:
-- In this script we create a table FOO1 with two partitions, p_one and p_two. We run a select statement
-- on partition p_one (which should only lock partition p_one), and simultaneously we run an
-- ALTER TABLE ... EXCHANGE PARTITION on partition p_two (which should only lock partition p_two).
-- Nevertheless, the ALTER TABLE ... EXCHANGE PARTITION is waiting for a table metadata lock as longs as
-- the select statement on p_one is running. We expect that the ALTER TABLE ... EXCHANGE PARTITION
-- doesn't need a table lock and will be executed immediately.
-- Create a SP for filling table FOO1 with some random data.
delimiter $$
drop procedure if exists fill_table_foo1$$
create procedure fill_table_foo1()
modifies sql data
begin
declare l_index bigint;
declare l_count bigint default 100000;
set l_index = 0;
loop1: loop
insert into FOO1( col1
, col2 )
values( 1
, round(1000*rand()) )
;
insert into FOO1( col1
, col2 )
values( 2
, round(1000*rand()) )
;
set l_index = l_index + 1;
if (l_index=l_count) then
leave loop1;
end if;
end loop;
end$$
delimiter ;
-- Create tables FOO1 (partitioned), FOO2 and F003.
drop table if exists FOO1;
drop table if exists FOO2;
drop table if exists FOO3;
create table FOO1
(
col1 int not null,
col2 int not null,
key col1 (col1)
) engine=myisam
partition by range (col1)
(
partition p_one values less than (2) engine = myisam,
partition p_two values less than (3) engine = myisam
)
;
create table FOO2
(
col1 int not null,
col2 int not null,
key col1 (col1)
) engine=myisam
;
create table FOO3
(
col1 int not null,
col2 int not null,
key col1 (col1)
) engine=myisam
;
-- Fill table FOO1 with some rows (each partition with 1,000,000 rows).
call fill_table_foo1();
-- Insert some rows table FOO2.
insert into FOO2( col1
, col2 )
select col1
, col2
from FOO1
;
-- MySQL is using partition pruning correctly.
explain partitions
select count(*)
from FOO1 f1
inner join FOO2 f2 on f1.col2 = f2.col2
where f1.col1 = 1
and f2.col1 = 1
;
-- +----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
-- | 1 | SIMPLE | f2 | NULL | ref | col1 | col1 | 4 | const | 97551 | NULL |
-- | 1 | SIMPLE | f1 | p_one | ALL | col1 | NULL | NULL | NULL | 100000 | Using where; Using join buffer (Block Nested Loop) |
-- +----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
-- Run a query to keep MySQL busy.
select count(*)
from FOO1 f1
inner join FOO2 f2 on f1.col2 = f2.col2
where f1.col1 = 1
and f2.col1 = 1
;
-- Run in a second terminal the following statement. It will block as long as the above select staement is running.
-- alter table FOO1 EXCHANGE PARTITION p_two with table FOO3;
-- Run in a third terminal:
-- show processlist.
-- This will show something like:
-- | 14 | root | localhost | test | Query | 2 | Waiting for table metadata lock | alter table FOO1 EXCHANGE PARTITION p_two with table FOO3 |
-- | 26 | root | localhost | test | Query | 46 | Sending data | select count(*) from FOO1 f1 inner join FOO2 f2 on |
