Understanding Lock Behavior in MySQL Partitioned Tables: Experiments and Insights

We start by creating a MySQL database and a partitioned table where the partition key is the timestamp column and id serves as the primary key.

We then insert two rows into the table.

Scenario 1 – RC isolation, lock on id = 1

Using the RC isolation level and locking the row with id = 1 , the WHERE clause does not reference the partition key. MySQL therefore acquires an IX lock on every partition.

Scenario 2 – RR isolation

Switching to the RR isolation level, we again lock id = 1 . This time MySQL acquires 64 locks, one for each partition’s supremum gap, because the lock must cover every possible location of the row across all partitions.

Scenario 3 – Using the partition key in the WHERE clause

When the WHERE clause includes the partition key ( timestamp = … ) with an equality condition, only the relevant partition is locked.

Scenario 4 – Using a comparison operator with the partition key

We modify the WHERE clause to use a range condition (e.g., timestamp > … ). The DAYOFYEAR function used for partitioning does not support pruning for such comparisons, so IX locks appear on all partitions again.

Scenario 5 – Replacing the partition function with YEAR

We create a similar table but use YEAR(timestamp) as the partition function. The same range query now benefits from partition pruning, and only the matching partitions ( p0 and p1 ) acquire locks.

The final analysis explains that MySQL can prune partitions when the WHERE clause contains the partition key with a simple equality and when the partitioning function (e.g., YEAR , TO_DAYS ) satisfies the pruning requirements. Otherwise, lock count increases.

Tip

You can also limit the SQL scope to a specific partition using SELECT ... FROM table PARTITION(p1) WHERE ... , though this approach is more invasive.

Thought Exercise

Build a table similar to the experiments, insert data, and run the same query as in Scenario 1. Observe that all partitions acquire IX locks and each row receives a row lock, differing from the earlier result.

Readers are encouraged to investigate the cause of this phenomenon.