For storage engines such as
that actually execute table-level locks when executing DML or
DDL statements, such a statement in older versions of MySQL
(5.6.5 and earlier) that affected a partitioned table imposed a
lock on the table as a whole; that is, all partitions were
locked until the statement was finished. In MySQL
5.7, partition lock
pruning eliminates unneeded locks in many cases, and
most statements reading from or updating a partitioned
MyISAM table cause only the effected
partitions to be locked. For example, a
SELECT from a partitioned
MyISAM table locks only those partitions
actually containing rows that satisfy the
WHERE condition are locked.
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, this is not an
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.
Effects on DML statements
SELECT statements (including
those containing unions or joins) 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.
INSERT lock only those partitions
having rows to be inserted or replaced. However, if an
AUTO_INCREMENT value is generated for any
partitioning column then all partitions are locked.
ON DUPLICATE KEY UPDATE is pruned as long as no
partitioning column is updated.
SELECT locks only those partitions in the source table
that need to be read, although all partitions in the target
table are locked.
Locks imposed by
statements on partitioned tables cannot be pruned.
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
Affected DDL statements
CREATE VIEW does not cause any
TABLE ... EXCHANGE PARTITION prunes locks; only the
exchanged table and the exchanged partition are locked.
TABLE ... TRUNCATE PARTITION prunes locks; only the
partitions to be emptied are locked.
statements take metadata locks on the table level.
LOCK TABLES cannot prune
supports lock pruning, but evaluating
expr does not.
statements do not support partitioning lock pruning.