Understanding InnoDB Transaction Pool and Pool Manager in MySQL 8.0.32

1. Transaction Pool and Manager

InnoDB, the default storage engine that supports transactions in MySQL, executes all reads and writes on tables within a transaction. MySQL is designed for high concurrency, allowing many clients to connect and run SQL statements simultaneously.

When these SQL statements read or write InnoDB tables, InnoDB starts a transaction for each connection, which means many transactions can be active at the same time. In typical OLTP (TP) scenarios, transactions complete quickly, often within 1–2 ms.

Each transaction requires a transaction object that stores the transaction ID, state, undo log number, and the owning thread. Allocating and freeing memory for every transaction object would degrade performance.

To avoid frequent memory allocation, InnoDB introduces a transaction pool that holds pre‑allocated transaction objects. The pool has a size limit, and multiple pools may be used when the system is busy.

Because multiple pools raise questions such as how to create new pools and from which pool a new transaction object should be taken, InnoDB also provides a transaction pool manager (PoolManager) to coordinate pool usage.

2. Creating the Transaction Pool Manager

During MySQL startup, InnoDB creates a single transaction pool manager. The manager has a property m_size that determines the memory size of each pool. This size is defined by a hard‑coded constant:

/** Size of on trx_t pool in bytes. */
static const ulint MAX_TRX_BLOCK_SIZE = 1024 * 1024 * 4;

The constant means each pool can use 4 MiB (4,194,304 bytes) of memory. The manager creates one pool and stores it in the m_pools vector.

Each transaction object occupies 992 bytes, so a 4 MiB pool can hold roughly 4,228 objects.

3. Initializing the Transaction Pool

After the pool is created, InnoDB initializes a small number of transaction objects. The pool maintains a queue called the transaction queue . Only the first 16 objects are pre‑initialized and placed into this queue.

When those 16 objects are all in use and the queue becomes empty, InnoDB lazily initializes the remaining 4,212 objects in a single batch and adds them to the queue.

Initialization occurs in a loop: each iteration allocates a small memory block, creates a transaction object, and enqueues it. Detailed attribute initialization is omitted for brevity.

4. Summary

InnoDB has one transaction pool manager that can manage one or more pools, each capable of holding up to 4,228 transaction objects. At startup, the manager creates a pool and pre‑initializes 16 objects. When those objects are exhausted, the remaining objects are initialized on demand, ensuring efficient memory usage and high‑throughput transaction processing.

Question for readers: During runtime, when a new transaction pool is created, how much memory is allocated and how many transaction objects are initialized?

Next topic preview: Does the BEGIN statement immediately start a transaction?