PolarDB:Usage of X-Engine

Limits on features

Limits on large transactions

X-Engine does not support large transactions. If the number of rows modified in a transaction is greater than or equal to 10,000, X-Engine enables the commit in middle feature. This way, X-Engine can internally commit the transaction and start a sub-transaction to continue to perform the transaction. However, the commit in middle feature may not ensure the atomicity of transactions. Take note of the following items when you use the commit in middle feature:

• Assume that you want to start a transaction to insert a large amount of data. During the insertion, part of the data is committed because of the commit in middle feature. The inserted data can be queried by other requests.

• Assume that you want to start a transaction to modify a large amount of data. The data that has been 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);// 12,000 rows are inserted and a commit in middle operation is triggered. As a result, the first 10,000 rows of data are committed. 
  rollback;// You can roll back only the last 2,000 rows. 
  SELECT COUNT(*) FROM t1;// The committed 10,000 rows of data can be queried. 
  +----------+
  | COUNT(*) |
  +----------+
  |    10000 |
  +----------+
  1 row in set (0.00 sec)

• Assume that you want to modify and delete a large amount of data in a transaction. The DELETE operation cannot read the committed rows in this transaction because of the commit in middle feature. As a result, you cannot delete the newly committed data.

  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 deletion operation triggers a commit in middle operation, and the two rows of data inserted by the current transaction are not deleted. 
  commit;
  SELECT * FROM t1;
  +-------+-------+
  | c1    | c2    |
  +-------+-------+
  | 10001 | 10001 |
  | 10002 | 10002 |
  +-------+-------+
  2 rows in set (0.00 sec)