Question 38: How many locks are required on the partition table?

ask

Why do I use the partition table, sometimes there are a few locks, sometimes hundreds of locks, and the weather is uncertain

experiment

Let's start with a database:

Build a partition table:

We want to partition based on the date of timestamp, with id as the primary key. Since the partition key must be the primary key, we add timestamp to the primary key.

Let's study how many locks the partition table will use when using the partition table.

Insert two pieces of data first:

scene 1:

We use RC isolation level to lock the record with id = 1

At this point, check the lock information:

can be seen:

Since we did not use the partition key timestamp in the where condition, MySQL needs to lock each table if it wants to access each table.

Scene 2:

This time we changed to RR isolation level:

View lock information:

This time the number of locks has become 64, and the gap interval of supremum is locked on each partition table. This is easy to understand: we let MySQL lock all the possible occurrences of id=1, which includes all partitions Related gaps.

Scene 3:

This time we use the partition key in the where condition:

View lock information:

Since we used the partition key directly, this time only the relevant partition will have a lock.

It seems that using the partition key in the where condition can greatly reduce the scope of the lock.

Scene 4:

We only use the partition key in where, but the condition is a little more complicated, and replaced with a comparison character (greater than/less than):

View lock information:

It can be seen that the IX locks on each partition have appeared again, and there has been a deviation from the conclusion of Scenario 3.

Let's not worry, let's do an experiment

Scene 5:

This time we make a similar table, but replace the partition key function with YEAR

Use a similar SQL for scenario 4:

View lock information:

The lock here only involves p0 and p1 (that is, the shards that contain data that matches the where condition), and does not include the irrelevant shard p2

This test is again consistent with the conclusion of scenario 3

The conclusions of scenes 3/4/5 are inconsistent, how do we understand? We have to return to the official website:

MySQL can filter out the relevant shards based on the shard key information in the where condition, and only use locks on the relevant shards.This technique is called partition pruning.

a section of the official website document. For details, please refer to 160c020acdf7d4 https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html: :

In scene 4 and scene 5, when the comparison operator is used in where, the DAYOFYEAR function in scene 4 does not support partition pruning, while the YEAR function in scene 5 can support, so there are differences between the two scenes.

Through today's experiment, we can see:

In the use of partitioned tables, where has a partition key, and the form is simple ("equal" is simpler than "comparator"), and the function in the partition key meets the conditions (YEAR/TO_DAYS/TO_SECONDS/...), then The partition pruning mechanism optimizes the number of locks.

Otherwise, the use of partition tables will increase the number of locks.

Tips

You can also limit the scope of SQL to a certain slice by selecting ... from table partition(p1) where ....

But this intrusion into the business is more serious and can be used as an extraordinary means

Questions

You can think about the reasons for the following phenomena when you have time:

Let's create a table similar to the previous experiment, and create some data:

The same SQL as in scenario 1:

View lock information:

It is found that it is different from scenario 1, except that there are IX locks on all partitions, and all rows have row locks.

When you are interested, you can try to explain this phenomenon

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!