PolarDB-X Storage Engine Internals | Native Multi-Version Unique Key Index

This article introduces Panda Index, PolarDB-X's native multi-version unique key index that eliminates gap-lock contention and table-lookup visibility checks.

By Yichang, Zanye, Xiahua

Background

PolarDB-X is a distributed database built on a unified centralized-and-distributed architecture. To handle hybrid workloads flexibly, each Data Node (DN) — the data storage layer — employs multiple data structures, with a row store providing online transaction processing (OLTP) capability. As a database 100% compatible with the MySQL ecosystem, the DN builds on InnoDB's storage structures with deep optimizations that significantly improve data access efficiency.

In OLTP workloads, the unique key (UK) index is one of the most common constraint mechanisms. Business tables rely on UK indexes to enforce uniqueness of critical fields such as order IDs, phone numbers, IDs, and transaction serial numbers — a cornerstone of data integrity. However, InnoDB's current MVCC implementation for UK indexes introduces two categories of problems under high concurrency:

Lock waits and deadlocks: High-concurrency inserts and updates on tables with UK indexes frequently cause lock waits — and even deadlocks — between transactions that are logically unrelated.
Covering index invalidation: Covering index queries on UK indexes still trigger table lookups (i.e., fetching the full row from the clustered index), resulting in lower-than-expected query performance.

Both problems trace back to the same root cause: design limitations in InnoDB's multi-version management of UK indexes. To address this, the PolarDB-X DN storage engine introduces Panda Index — a new-generation UK index structure that grants UK indexes native multi-version capability, fundamentally eliminating the issues of inflated lock scope and table-lookup-dependent visibility checks.

InnoDB UK index MVCC challenges

Two approaches to historical version storage in database MVCC

To understand the problems with UK indexes, we first need to understand how MVCC stores historical versions. Mainstream TP databases use two broad paradigms for storing historical versions of primary table data:

	PostgreSQL: append-only storage	InnoDB/Oracle: rollback segment storage
Approach	Multi-version physical append: historical versions stay in place, new versions are inserted at new locations	In-place update: the latest version directly overwrites the historical version
Historical version location	Data tablespace	Separate undo tablespace
Historical version record format	Complete record: each version is a self-describing full tuple	Delta patch: only the diff of the modified record is stored
Historical version space management	Space tends to bloat: relies on the vacuum mechanism to scan the data tablespace for cleanup	Space is more compact: undo segments can be reused cyclically, and purge can perform precise cleanup far more efficiently
Historical version reads	Fast reads: directly reads the complete tuple with no extra I/O	Slow reads: requires extra undo page I/O and applying deltas to reconstruct the full tuple
Impact of updates on indexes	Significant write amplification: the new version's physical location changes, so all indexes must be updated (when HOT updates are not triggered)	Stable write behavior: primary table data is updated in place, and logical index pointers remain unchanged

InnoDB's clustered index uses the rollback segment approach: each record contains a TRX_ID (transaction ID) and a ROLL_PTR (pointer to the undo chain). On modification, InnoDB first writes an undo log entry, then updates the record in place. Historical versions are traced through the undo chain, and the B+ tree always retains only the latest version — a clean and efficient structure.

InnoDB clustered index version chain diagram

The "multi-version physical append" problem of UK indexes

However, InnoDB's secondary indexes — including UK indexes — do not use the same version management strategy as the clustered index. Secondary index records carry no TRX_ID or ROLL_PTR, no version information, and no undo chain. When a UK record needs to be updated, InnoDB uses a delete-mark-then-insert approach: it first marks the old record as deleted, then inserts a new record.

This means that on the UK index's B+ tree, multiple physical records with the same unique key value can coexist simultaneously — a delete-marked old version and a new version. Until the purge thread cleans them up, these historical versions remain on the index structure.

InnoDB secondary index multi-version storage diagram

This "multi-version physical append" approach creates problems on three levels:

1. Gap lock scope inflation

In InnoDB's locking protocol, to enforce the uniqueness constraint (insert conflict detection) on a multi-version-appended UK index, the system must place gap locks on multiple physical version records sharing the same key value.

Consider this scenario: transaction A deletes the record with uk_col = 10, leaving a delete-marked record on the B+ tree. Transaction B then attempts to insert uk_col = 10 and must check for conflicts. Because the index may contain multiple physical records with the same UK value (the current version and deleted-but-not-yet-purged versions), insert conflict detection must scan all these records and place gap locks on the corresponding intervals.

The result: transactions that are logically non-conflicting end up waiting on — or even deadlocking with — each other due to overlapping lock ranges. This not only reduces system concurrency throughput but also introduces significant unpredictability into application-side SQL logic. The problem is especially acute in high-concurrency UK update scenarios such as trading systems, balance deductions, and flash sales.

2. Visibility checks depend entirely on table lookups

Because InnoDB secondary indexes carry no version information, an index-only scan cannot independently determine visibility. Even when all queried columns are present in the UK index (theoretically eligible for a covering index), MVCC queries must still go back to the clustered index records and their undo chains to check version information before determining whether a record is visible to the current transaction.

Additionally, implicit lock detection and purge paths also require table lookups to obtain transaction information. These extra table lookup operations place unnecessary pressure on the buffer pool and I/O resources.

3. Performance degradation from purge lag

Delete-marked UK records must wait for the purge thread to clean them up asynchronously. In write-intensive workloads, purge may fall behind the rate of deletions, causing delete-marked records to accumulate on the UK index. These residual records not only waste storage space but also expand the scan range during subsequent insert conflict detection, further exacerbating lock contention and latency.

Panda Index design

Core idea

The core idea behind Panda Index is to push the clustered index's native multi-version capability down into the UK index.

Specifically, Panda Index augments the traditional UK index record with the clustered index's transaction information columns and version chain, giving the UK index native multi-version capability.

This design introduces two fundamental changes:

Update mechanism transformation: The traditional delete-mark-then-insert approach is replaced by in-place update with historical versions recorded in undo, eliminating the root cause of multiple physical records for the same unique key coexisting on the B+ tree.
Self-contained visibility checks: UK index records now carry transaction information directly, removing the need to perform table lookups to the clustered index for version information.

Comparison of Panda Index vs. standard UK index

Record format

Panda Index adds four system columns to the traditional UK record:

System column	Description
TRX_ID	Transaction ID of the most recent modification, used for transaction state checks
ROLL_PTR	Pointer to Panda Index's own independent undo record, used to trace historical versions
SCN	Lizard transaction system commit sequence number, used for visibility checks
UBA	Undo block address (transaction slot address), used for visibility checks

The semantics of TRX_ID, SCN, and UBA are consistent with those in clustered index records, allowing Panda Index to reuse the Lizard transaction system's existing visibility determination framework (Vision). The ROLL_PTR, however, points to Panda Index's own independent undo records — a chain completely separate from the clustered index's undo chain — through which all historical versions of the current record can be retrieved.

Panda Index record format

In-place update: eliminating multi-version physical append

With ROLL_PTR on Panda Index records, updates no longer need the delete-mark-then-insert approach to retain historical versions on the data page. The update mechanism now aligns with that of the clustered index:

Record undo: First, write an independent undo record for Panda Index, saving the old version of the record.
Update in place: Directly modify the data content on the original record, storing the undo address in the ROLL_PTR field.

This "multi-version physical append" approach creates problems on three levels:

• The B+ tree index structure retains only the current version. There are no residual delete-marked records to impose extra costs on record scanning and locking.

• All historical versions are stored in a separate undo tablespace, managed by the purge system. Any historical version can be easily retrieved through ROLL_PTR without table lookups.

Insert conflict detection optimization

In-place update capability yields an important derivative benefit: only one physical record can exist for any given unique key on the index.

This dramatically simplifies insert conflict detection logic:

• Traditional UK: Must scan all index records that might share the same unique key (including delete-marked ones) and place gap locks on the entire interval to prevent concurrent transactions from inserting duplicate keys.

• Panda Index: If no record with the same key value exists in the index, the insert succeeds immediately. If one exists, only a record lock is placed on that single record, and the system further checks whether the record is delete-marked to determine if there is a true conflict.

The entire process requires no gap locks, shrinks the lock scope, and significantly reduces deadlock probability in high-concurrency UK operations.

Independent undo system

Panda Index has a completely independent undo log system that does not share an undo chain with the clustered index. This means Panda Index has self-driven, independent processing logic on the following critical paths:

• Forward DML: Each modification to a Panda Index record independently writes an undo log entry, separate from the clustered index's undo writes.

• Rollback: On transaction rollback, Panda Index's undo records are applied independently. The rollback logic does not depend on the clustered index.

• Purge: The background purge thread has an independent cleanup path for Panda Index's delete-marked records, verifying the record version through ROLL_PTR before performing physical deletion.

The independent undo system eliminates the coupling between the UK index and the clustered index in version management, making Panda Index's version lifecycle management fully self-contained.

Visibility checks

Panda Index records carry TRX_ID, SCN, and UBA transaction information directly, enabling visibility checks to be performed locally on the index without table lookups.

Similar to the Lizard transaction system's clustered index path, Panda Index's consistent read path works as follows:

Read the transaction information (TRX_ID, SCN, UBA) from the record.
Use the Lizard Vision framework to determine whether the record is visible to the current transaction.
If the current version is not visible, trace the undo chain through ROLL_PTR to reconstruct a historical version.

The entire process completes entirely within the Panda Index B+ tree, requiring no table lookup operations. This enables covering index queries on Panda Index to achieve true index-only scans, and paths such as implicit lock detection no longer need to access the clustered index.

Verification

A simple concurrent scenario demonstrates the effect of Panda Index's gap lock elimination.

Data setup:

CREATE TABLE t1(
  id int,
  c1 int,
  PRIMARY KEY(id),
  UNIQUE KEY uk1(c1)
);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (100, 100);

Concurrent test:

Session 1 performs a delete-then-insert operation (transaction uncommitted):

BEGIN;
DELETE FROM t1 WHERE id = 1;
INSERT INTO t1 VALUES (2, 1);

Session 2 inserts a non-conflicting record that falls within the gap:

INSERT INTO t1 VALUES (3, 2);

Standard UK behavior: Session 2's insert times out waiting for Session 1's gap lock. Inspecting lock information reveals multiple gap locks on the UK:

SELECT lock_data, lock_mode FROM performance_schema.data_locks WHERE index_name = 'uk1';
+-----------+---------------+
| lock_data | lock_mode     |
+-----------+---------------+
| 1, 1      | X,REC_NOT_GAP |
| 1, 1      | S,GAP         |
| 100, 100  | S,GAP         |
| 1, 2      | S,GAP         |
+-----------+---------------+

Panda Index behavior: Session 2's insert succeeds immediately, because Panda Index does not produce unnecessary gap locks:

SELECT lock_data, lock_mode FROM performance_schema.data_locks WHERE index_name = 'uk1';
+-----------+---------------+
| lock_data | lock_mode     |
+-----------+---------------+
| 1, 2      | X,REC_NOT_GAP |
+-----------+---------------+

Community

PolarDB-X Storage Engine Internals | Native Multi-Version Unique Key Index — Panda Index

Background

InnoDB UK index MVCC challenges

Two approaches to historical version storage in database MVCC

The "multi-version physical append" problem of UK indexes

1. Gap lock scope inflation

2. Visibility checks depend entirely on table lookups

3. Performance degradation from purge lag

Panda Index design

Core idea

Record format

In-place update: eliminating multi-version physical append

Insert conflict detection optimization

Independent undo system

Visibility checks

Verification

Data setup:

Concurrent test:

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for MySQL

PolarDB for Xscale

LedgerDB

ApsaraDB for OceanBase