In MySQL 5.6.5 and earlier, for storage engines such as
MyISAM that actually execute
table-level locks when executing DML or DDL statements, such a
statement affecting a partitioned table imposed a lock on the
table as a whole; that is, all partitions were locked until the
statement was finished. MySQL 5.6.6 implements
partition lock pruning, which eliminates
unneeded locks in many cases. In MySQL 5.6.6 and later, most
statements reading from or updating a partitioned
MyISAM table cause only the effected
partitions to be locked. For example, prior to MySQL 5.6.6, a
SELECT from a partitioned
MyISAM table caused a lock on the entire
table; in MySQL 5.6.6 and later, only those partitions actually
containing rows that satisfy the
WHERE condition are locked.
This has the effect of increasing the speed and efficiency of
concurrent operations on partitioned
tables. This improvement becomes particularly noticeable when
MyISAM tables that have many (32
or more) partitions.
This change in behavior does not have any impact on statements
effecting 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 partition lock pruning for various MySQL statements on tables using storage engines that employ table-level locks.
SELECT statements (including
those containing unions or joins) now lock only those partitions
that actually need to be read. This also applies to
SELECT ... PARTITION.
UPDATE prunes locks only for
tables on which no partitioning columns are updated.
ON DUPLICATE KEY UPDATE is pruned as long as no
partitioning column is updated.
SELECT now locks only those partitions in the source
table that need to be read, although all partitions in the
target table are locked.
DELAYED is not supported for partitioned tables.
Locks imposed by
DATA statements on partitioned tables cannot be
The presence of
BEFORE INSERT or
BEFORE UPDATE triggers using any partitioning
column of a partitioned table means that locks on
statements updating this table cannot be pruned, since the
trigger can alter its values: A
trigger on any of the table's partitioning columns means
that locks set by
REPLACE cannot be pruned, since the
BEFORE INSERT trigger may change a row's
partitioning columns before the row is inserted, forcing the row
into a different partition than it would be otherwise. A
BEFORE UPDATE trigger on a partitioning
column means that locks imposed by
INSERT ... ON DUPLICATE KEY UPDATE cannot be
CREATE VIEW no longer causes any
TABLE ... EXCHANGE PARTITION now prunes locks; only
the exchanged table and the exchanged partition are locked.
TABLE ... TRUNCATE PARTITION now prunes locks; only
the partitions to be emptied are locked.
ALTER TABLE statements still take
metadata locks on the table level.
LOCK TABLES cannot prune
supports lock pruning, but evaluating
expr does not.