PolarDB:X-Engine usage notes

Engine feature limits

Limits on large transactions

X-Engine does not support large transactions. If a transaction modifies 10,000 or more rows, X-Engine enables the commit in middle feature. This feature allows X-Engine to internally commit the transaction and start a sub-transaction to continue the original transaction. However, commit in middle does not strictly adhere to the principle of transaction atomicity. Take note of the following points when you use this feature:

• If you start a transaction to insert a large amount of data, a portion of the data may be committed during the insertion process because of the commit in middle feature. As a result, other requests can access the committed data before the entire transaction is complete.

• If you start a transaction to modify a large amount of data, any part of the transaction that is committed by the commit in middle feature cannot be rolled back.

  DROP TABLE t1;
  CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
  BEGIN;
  call insert_data(12000); // Insert 12,000 rows to trigger commit in middle. The first 10,000 rows are committed.
  rollback;// The rollback operation can only roll back the last 2,000 rows.
  SELECT COUNT(*) FROM t1; // You can still query the 10,000 rows.
  +----------+
  | COUNT(*) |
  +----------+
  |    10000 |
  +----------+
  1 row in set (0.00 sec)

• If you start a transaction to insert and then delete a large amount of data, the commit in middle feature may prevent the DELETE operation from reading the rows that were just inserted within the same transaction. As a result, the newly inserted data is not deleted.

  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;// The DELETE operation triggers commit in middle, which prevents the operation from reading the rows inserted in the current transaction.
  commit;
  SELECT * FROM t1;
  +-------+-------+
  | c1    | c2    |
  +-------+-------+
  | 10001 | 10001 |
  | 10002 | 10002 |
  +-------+-------+
  2 rows in set (0.00 sec)