Constraints of global secondary indexes on DML for PolarDB-X - PolarDB - Alibaba Cloud - PolarDB

When a global secondary index (GSI) write fails in PolarDB-X 1.0, the entire table is blocked: no further DML statements can execute on it, and the active transaction cannot be committed. Understanding this behavior helps you avoid unexpected transaction failures and design safe write patterns around GSIs.

Prerequisites

Before you begin, make sure you have:

ApsaraDB RDS for MySQL instances at version 5.7 or later
PolarDB-X 1.0 instances at version 5.4.1 or later

Example table

The examples in this topic use the following table, which defines a regular GSI (g_i_seller) and a unique GSI (g_i_buyer):

CREATE TABLE t_order(
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `l_i_order` (`order_id`),
  GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
  GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

DML behavior when a GSI write fails

Important

A GSI write failure puts the table into a blocked state. While blocked, no DML statements can execute on the table and the transaction cannot be committed.

The following example demonstrates this behavior:

SET DRDS_TRANSACTION_POLICY='XA';

-- Succeeds: inserts order_1 with buyer_id='buyer_1'.
-- The unique GSI g_i_buyer records buyer_id='buyer_1'.
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');

-- A GSI failed to be written to the table.
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');

-- Blocked: no DML can execute on t_order while the GSI write failure persists.
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');

-- Blocked: the transaction cannot be committed.
COMMIT;