22.6.4 Partitioning and Locking
For storage engines such as
MyISAM 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 issue.
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) 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 ... ON DUPLICATE KEY UPDATE is pruned as long as no partitioning column is updated.
INSERT ... 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
LOAD DATA 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
UPDATE statements updating this table cannot be pruned, since the trigger can alter its values: A
BEFORE INSERT 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 pruned.
CREATE VIEW does not cause any locks.
ALTER TABLE ... EXCHANGE PARTITION prunes locks; only the exchanged table and the exchanged partition are locked.
ALTER TABLE ... TRUNCATE PARTITION prunes locks; only the partitions to be emptied are locked.
ALTER TABLE statements take metadata locks on the table level.
LOCK TABLES cannot prune partition locks.
CALL stored_procedure( supports lock pruning, but evaluating
expr does not.