Exploring the Relationship Between InnoDB Locks and Buffer Pool Size in MySQL

When a transaction manipulates a large amount of data, MySQL may report the error "The total number of locks exceeds the lock table size". The official documentation suggests increasing the buffer pool size to mitigate this.

The experiment sets the InnoDB buffer pool to its minimum of 5 MiB to reproduce the issue, then creates a transaction that repeatedly doubles a table to consume locks.

Lock usage is observed via information_schema.INNODB_TRX, focusing on four metrics:

trx_tables_locked – number of tables locked by the transaction

trx_rows_locked – number of rows locked

trx_lock_structs – total lock structures used (each can lock multiple tables or rows)

trx_lock_memory_bytes – memory consumed by lock structures

Buffer pool status is also monitored, with two key values:

total – total number of pages in the buffer pool

misc – number of non‑data pages (metadata, lock structures, etc.)

As the transaction creates more data, lock memory grows (e.g., from 24 784 B to 1 216 kB) and the number of misc pages increases correspondingly, showing that lock memory is allocated from the buffer pool.

When the buffer pool’s non‑data pages exceed roughly 73% of its capacity, the transaction aborts and rolls back, confirming the practical threshold aligns with the theoretical 75% limit documented in MySQL source code.

The experiment concludes:

If a transaction uses many locks, the memory for lock structures is taken from the buffer pool.

High buffer‑pool utilization (around 75%) triggers the "total number of locks exceeds the lock table size" error.

A tip: when the buffer pool is undersized, it can recycle clean data pages but cannot directly reclaim non‑data pages, which is why the 75% threshold is enforced to preserve space for data caching.