Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 25.7Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 158.5Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Partitioning and Table-Level Locking

19.5.4 Partitioning and Table-Level Locking

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 imposes a lock on the table as a whole; that is, all partitions are locked until the statement was finished. For example, a SELECT from a partitioned MyISAM table causes a lock on the entire table.

In practical terms, what this means is that the statements discussed later in this section tend to execute more slowly as the number of partitions increases. This limitation is greatly reduced in MySQL 5.6, with the introduction of partition lock pruning in MySQL 5.6.6.

This is not true 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.

The next few paragraphs discuss the effects of MySQL statements on partitioned tables using storage engines that employ table-level locks.

DML statements

SELECT statements lock the entire table. SELECT statements containing unions or joins lock all tables named in the union or join.

UPDATE also locks the entire table.

REPLACE and INSERT (including INSERT ... ON DUPLICATE KEY UPDATE) lock the entire table.

INSERT ... SELECT locks both the source table and the target table.


INSERT DELAYED is not supported for partitioned tables.

A LOAD DATA statement on a partitioned table locks the entire table.

A trigger on a partitioned table, once activated, locks the entire table.

DDL statements

CREATE VIEW causes a lock on any partitioned table from which it reads.

ALTER TABLE locks the affected partitioned table.

Other statements

LOCK TABLES locks all partitions of a partioned table.

Evaluating the expr in a CALL stored_procedure(expr) statement locks all partitions of any partitioned table referenced by expr.

ALTER TABLE also takes a metadata lock on the table level.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.