MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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
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.
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
.
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.
INSERT
DELAYED
is not supported for partitioned tables.
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.
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.
LOCK TABLES
cannot prune
partition locks.
CALL
stored_procedure(
supports lock pruning, but evaluating
expr
)expr
does not.
DO
and
SET
statements do not support partitioning lock pruning.