Panda Index: A unique key index to prevent deadlocks - PolarDB

The PolarDB-X storage engine provides Panda Index, a next-generation multi-version unique key index. To address the performance bottlenecks of traditional MySQL non-clustered indexes, Panda Index uses its native Multi-Version Concurrency Control (MVCC) capability to avoid the overhead from cross-index lookups. It also optimizes global range constraint checks to a row-level lock granularity, which reduces lock scope escalation.

Prerequisites

Your instance must meet the following version requirements:

Instance edition: Standard Edition or Enterprise Edition.
Engine version: MySQL 8.0.
Data node version: xcluster8.4.20-20250527 or later, which corresponds to the version released on May 27, 2025 or later.
Note
- For information about the instance version naming rules, see Release notes.
- For information about how to view the version of an instance, see View and update the engine version of a PolarDB for Xscale instance.

Billing

Panda Index is free of charge. However, Panda Index adds 28 bytes of overhead to each unique index record. For most business tables, this results in a minor storage increase, typically less than 5%, and a corresponding increase in storage costs.

Limitations

Instance version: Because Panda Index involves changes to the underlying storage structure, an instance that uses Panda Index cannot be downgraded to an earlier version that does not support Panda Index.
Handling existing indexes: For tables with existing unique keys (UK), the keys are not automatically converted to a Panda Index after you enable Panda Index. You can rebuild the indexes to enable Panda Index capabilities.
1. Create a new index: ALTER TABLE ... ADD UNIQUE INDEX idx_new ...;
2. Rename the indexes: ALTER TABLE ... RENAME INDEX idx TO idx_old, RENAME INDEX idx_new TO idx;
3. Drop the original index: ALTER TABLE ... DROP INDEX idx_old;
Isolation level restrictions: Panda Index is primarily designed to mitigate gap lock issues in the Read Committed (RC) isolation level. In the Repeatable Read (RR) isolation level, the system must still use next-key locks (a combination of a record lock and a gap lock) to ensure repeatable reads. Therefore, Panda Index cannot prevent gap locks at this level.
Table and index type restrictions: Panda Index is not supported for temporary tables, system tables, compressed tables, or multi-valued indexes.

Use Panda Index

1. Enable the Panda Index feature

In the PolarDB-X console, go to the Storage Layer tab on the Configuration Management > Parameter Settings page for your target cluster. Set the opt_index_format_panda_enabled parameter to ON. This change takes effect immediately and does not require an instance restart.

Note

For new instances purchased after 00:00:00, June 4, 2025 (Singapore Time), the Panda Index feature is enabled by default.
If you set the opt_index_format_panda_enabled parameter to ON, PolarDB-X creates a Panda Index by default when you create a table or a unique key index.
If you set the opt_index_format_panda_enabled parameter to OFF, PolarDB-X creates a standard unique key index compatible with community edition MySQL by default when you create a table or a unique key index.

2. Verify the Panda Index effect

You can verify the effect in a simple concurrency scenario.

Prepare data

-- Create a table and a unique key index.
CREATE TABLE t1(
  id int,
  c1 int,
  PRIMARY KEY(id),
  UNIQUE KEY uk1(c1)
) SINGLE /* This example uses a single table in Enterprise Edition. This is not required for Standard Edition instances. */;
-- Insert data.
INSERT INTO t1 VALUES (1,1);
INSERT INTO t1 VALUES (100,100);

Run a concurrency test

In one transaction (Session 1), delete a record and then insert a new record to simulate a common update pattern.

BEGIN;
DELETE FROM t1 where id=1;
INSERT INTO t1 values (2,1);

At the same time, in another transaction (Session 2), insert a non-conflicting record into the data gap.

INSERT INTO t1 values (3,2);

At this point, a standard unique key and a Panda Index behave differently.

Standard unique key: The insert operation in Session 2 is blocked by the gap lock held by Session 1 and eventually times out.

mysql> INSERT INTO t1 values (3,2);
-- The error message contains the following: Lock wait timeout exceeded; try restarting transaction

On a Standard Edition instance, you can observe that the lock information contains a gap lock on the unique key uk1.

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: The insert operation in Session 2 succeeds immediately because Panda Index does not create an unnecessary gap lock.

mysql> INSERT INTO t1 values (3,2);
Query OK, 1 row affected (0.00 sec)

On a Standard Edition instance, you can observe that the lock information does not contain a gap lock on the unique key uk1.

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

FAQ

Does an instance upgrade automatically convert existing unique keys to Panda Indexes?

No. Panda Index involves changes to the underlying storage structure. After you upgrade an instance, you must rebuild existing unique keys to convert them to Panda Indexes. For a zero-downtime conversion, use the three-step rebuilding process described in the Limitations section instead of running the ALTER TABLE ... DROP INDEX ...; ALTER TABLE ... ADD UNIQUE INDEX ...; commands.

Why do I still encounter deadlocks that involve gap locks on unique keys after I enable Panda Index?

Check if your isolation level is set to Repeatable Read.

Panda Index is primarily designed to eliminate the gap locks that MySQL must add to enforce uniqueness constraints in the Read Committed (RC) isolation level. For a detailed discussion, see Bug #68021. However, in the Repeatable Read (RR) isolation level, the database must use next-key locks (a combination of record locks and gap locks) to ensure repeatable reads. Panda Index cannot prevent the resulting gap-lock deadlocks at this level.

Are there any risks to enabling the Panda Index parameter opt_index_format_panda_enabled?

No. You can enable or disable this parameter without restarting the instance. The change has no impact on your existing services.

What table types and database modes does Panda Index support?

Panda Index is supported in both Standard Edition and Enterprise Edition.

In Enterprise Edition, Panda Index is supported for partitioned tables, single tables, and broadcast tables, and for databases in both DRDS and AUTO modes.

Does using Panda Index have a performance overhead?

No. Panda Index does not introduce performance overhead. In fact, benchmark tests show that it improves performance in various scenarios. The only trade-off is a minor (typically less than 5%) increase in storage space.