19.6.4 Partitioning and Locking

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.

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

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.