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 SELECT statement's WHERE condition are locked. This has the effect of increasing the speed and efficiency of concurrent operations on partitioned MyISAM tables. This improvement becomes particularly noticeable when working with 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.

Affects on DML statements

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.

An UPDATE prunes locks only for tables on which no partitioning columns are updated.

REPLACE and INSERT now 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.

INSERT ... ON DUPLICATE KEY UPDATE is pruned as long as no partitioning column is updated.

INSERT ... SELECT now 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 INSERT and 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 INSERT or 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 UPDATE or INSERT ... ON DUPLICATE KEY UPDATE cannot be pruned.

Affected DDL statements

CREATE VIEW no longer causes any locks.

ALTER TABLE ... EXCHANGE PARTITION now prunes locks; only the exchanged table and the exchanged partition are locked.

ALTER 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.

Other statements

LOCK TABLES cannot prune partition locks.

CALL stored_procedure(expr) supports lock pruning, but evaluating expr does not.

DO and SET statements do not support partitioning lock pruning.