PolarDB:X-Engine usage notes

Engine feature limits

X-Engine does not support the following SQL features, index types, and lock options. Features not listed in this table behave the same as in InnoDB.

Limits on large transactions

X-Engine does not support large transactions. When a transaction modifies 10,000 or more rows, X-Engine automatically enables the commit in middle feature, which internally commits the transaction and starts a sub-transaction to continue. This does not strictly adhere to transaction atomicity.

The following behaviors apply when commit in middle is triggered:

• INSERT operations: A portion of the inserted data is committed mid-transaction. Other sessions can read those rows before the entire transaction completes.

• Modify operations: Rows committed by commit in middle cannot be rolled back. In the following example, rolling back after inserting 12,000 rows only removes the last 2,000 — the first 10,000 are already committed:

  DROP TABLE t1;
  CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
  BEGIN;
  call insert_data(12000); -- Inserts 12,000 rows; the first 10,000 are committed by commit in middle
  rollback;                -- Only the last 2,000 rows are rolled back
  SELECT COUNT(*) FROM t1; -- Returns 10,000
  +----------+
  | COUNT(*) |
  +----------+
  |    10000 |
  +----------+
  1 row in set (0.00 sec)

• INSERT followed by DELETE in the same transaction: commit in middle may prevent the DELETE from reading rows inserted earlier in the same transaction, leaving those rows undeleted:

  DROP TABLE t1;
  CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
  call insert_data(10000);
  BEGIN;
  INSERT INTO t1 VALUES(10001,10001), (10002,10002);
  DELETE FROM t1 WHERE c1 >= 0; -- commit in middle prevents reading the newly inserted rows
  commit;
  SELECT * FROM t1;              -- Rows 10001 and 10002 are still present
  +-------+-------+
  | c1    | c2    |
  +-------+-------+
  | 10001 | 10001 |
  | 10002 | 10002 |
  +-------+-------+
  2 rows in set (0.00 sec)