For storage engines such as
that actually execute table-level locks when executing DML or
DDL statements, such a statement affecting a partitioned table
imposes a lock on the table as a whole; that is, all partitions
are locked until the statement was finished. For example, a
SELECT from a partitioned
MyISAM table causes a lock on the entire
In practical terms, what this means is that the statements discussed later in this section tend to execute more slowly as the number of partitions increases. This limitation is greatly reduced in MySQL 5.6, with the introduction of partition lock pruning in MySQL 5.6.6.
This is not true for statements affecting partitioned tables
using storage engines such as
InnoDB, that employ row-level
locking and do not actually perform (or need to perform) the
locks prior to partition pruning.
The next few paragraphs discuss the effects of MySQL statements on partitioned tables using storage engines that employ table-level locks.
SELECT statements lock the entire
SELECT statements containing unions or
joins lock all tables named in the union or join.
UPDATE also locks the entire
SELECT locks both the source table and the target
DELAYED is not supported for partitioned tables.
LOAD DATA statement on a
partitioned table locks the entire table.
A trigger on a partitioned table, once activated, locks the entire table.
CREATE VIEW causes a lock on any
partitioned table from which it reads.
ALTER TABLE locks the affected
LOCK TABLES locks all partitions
of a partioned table.
expr in a
statement locks all partitions of any partitioned table
ALTER TABLE also takes a metadata
lock on the table level.