Question 43: Why do we need to adjust the Buffer Pool because too many locks are used

ask

When we use a transaction to manipulate a lot of data, MySQL sometimes reports an error: The total number of locks exceeds the lock table size

According to the official documentation, we need to increase the size of the buffer pool:

In this experiment, let's explore the relationship between how much locks are used and the size of the buffer pool

experiment

We used the old method to build a database, and adjusted the buffer pool size to the minimum value of 5M, which is convenient for us to reproduce the problem

Now let's simulate a transaction that uses a lot of locks:

We still use the old method to double the table to keep occupying the lock.

Take a look at the effect:

We can use information_schema.INNODB_TRX to see how many locks the transaction uses, and explain the states marked in the above figure:

• trx_tables_locked: How many tables are locked by this transaction
• trx_rows_locked: How many rows of data are locked by this transaction
• trx_lock_structs: How many lock structures are shared by the transaction. A lock structure is used to lock multiple tables or multiple rows
• trx_lock_memory_bytes: How much memory is shared by the lock structure of the transaction

Let's take a look at the status of the buffer pool:

Explain these two states of the Buffer pool:

• total is the total number of pages in the Buffer pool
• misc is the number of non-data pages in the buffer pool

Let's continue to create data, let the transaction use more and more locks, and then look at the status:

Compared with the initial state, the memory of the lock used by the transaction has increased (1269968-24784 =) 1245184 bytes = 1216 k, while the buffer pool non-data pages used more (84-8 =) 76 pages, 16k per page , A total of 1216 k

In other words, the lock memory of the transaction is equally divided from the buffer pool.

We continue to create data, until the error is reported, and keep checking the status:

It can be seen that when an error occurs, the transaction will be rolled back. Before the rollback, the memory size occupied by the transaction lock is 3842256 bytes, which is (3842256/1024/16 =) 234.5 pages. At this time, the buffer pool usage rate is about Yes (234.5 / 320 =) 73%

This concludes our experiment. Through this experiment we know:

1. If the transaction uses a lot of locks, the memory used by the lock structure is allocated from the Buffer pool
2. When the Buffer pool usage rate is relatively high, an error will be reported: The total number of locks exceeds the lock table size

Regarding how much Buffer pool is used, an error will be reported. Let's refer to the following MySQL source code:

We can see that this theoretical threshold is 75%.When the size of non-data pages in the Buffer pool exceeds 75%, we will no longer be able to allocate more locks.
(The 73% determined in the experiment is not much different from this theoretical threshold)

Tips

When the size of the Buffer pool is insufficient, the Buffer pool can reclaim data pages (clean pages can be reclaimed directly, and dirty pages can be reclaimed after flushing), but cannot directly reclaim non-data pages.

MySQL sets this 75% threshold in order to leave some space in the Buffer pool for data pages.After all, the biggest role of the Buffer pool is to cache data pages.

---

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