This topic describes the inventory hints feature that is provided by PolarDB. This feature can be used together with the statement queue feature. This allows you to quickly commit or roll back transactions.

Prerequisites

The version of the PolarDB cluster is PolarDB for MySQL 8.0 and the revision version is 8.0.1.1.1 or later. For more information about how to confirm the cluster version, see Query the kernel version.

Background

In scenarios such as flash sales, inventory reduction is a common task model that requires high concurrency and serialization. In this model, PolarDB uses queues and transactional hints to control concurrency and commit or roll back transactions. This increases the throughput of your business.

Syntax

The inventory hint feature provided by PolarDB supports the SELECT, UPDATE, INSERT, and DELETE statements.

The following inventory hints are used:

  • COMMIT_ON_SUCCESS: commits the transaction if the current statement is successfully executed.

    Example:

    • PolarDB for MySQL 5.6
      UPDATE COMMIT_ON_SUCCESS T
      SET c = c - 1
      WHERE id = 1;
    • PolarDB for MySQL 8.0
      UPDATE /*+ COMMIT_ON_SUCCESS */ T
      SET c = c - 1
      WHERE id = 1;
  • ROLLBACK_ON_FAIL: rolls the transaction back if the current statement fails to be executed.

    Example:

    • PolarDB for MySQL 5.6
      UPDATE ROLLBACK_ON_FAIL T
      SET c = c - 1
      WHERE id = 1;
    • PolarDB for MySQL 8.0
      UPDATE /*+ ROLLBACK_ON_FAIL */ T
      SET c = c - 1
      WHERE id = 1;
  • TARGET_AFFECT_ROW number: the execution succeeds if the number of rows affected by the current statement matches the specified number of rows. Otherwise, the execution fails.

    You can set Target Affect Row to 1. If the UPDATE statement hits at least one record, the update operation succeeds. If no record is updated, the update operation fails.

    Example:

    • PolarDB for MySQL 5.6
      UPDATE TARGET_AFFECT_ROW 1  T
      SET c = c - 1
      WHERE id = 1;
      ERROR HY000: The affected row number does not match that of user specified.
    • PolarDB for MySQL 8.0
      UPDATE /*+ TARGET_AFFECT_ROW(1) */ T
      SET c = c - 1
      WHERE id = 1;
      ERROR HY000: The affected row number does not match that of user specified.

Work with the statement queue feature

The COMMIT_ON_SUCCESS, ROLLBACK_ON_FAIL, and TARGET_AFFECT_ROW number hints in the UPDATE, INSERT, and DELETE statements can be used with Statement Queue to queue statements.

Example in PolarDB for MySQL 5.6:

UPDATE COMMIT_ON_SUCCESS POLARDB_STATEMENT_CONCURRENT_QUEUE id ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 t
SET col1 = col1 + 1
WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE COMMIT_ON_SUCCESS POLARDB_STATEMENT_CONCURRENT_QUEUE 1 ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 t
SET col1 = col1 + 1
WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0