This topic describes the inventory hints feature provided by PolarDB. This feature can work with the statement queue feature to commit or roll back transactions rapidly.

Prerequisites

The version of the cluster is PolarDB for MySQL 5.6 or PolarDB for MySQL 8.0.

Background

In scenarios such as seckilling, 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 SELECT, UPDATE, INSERT, and DELETE statements.

The following hints are used:

  • COMMIT_ON_SUCCESS: commits the transaction if the execution of the current statement is successful.

    Example:

    UPDATE COMMIT_ON_SUCCESS T
    SET c = c - 1
    WHERE id = 1;
  • ROLLBACK_ON_FAIL: rolls the transaction back if the execution of the current statement fails.

    Example:

    UPDATE ROLLBACK_ON_FAIL T
    SET c = c - 1
    WHERE id = 1;
  • TARGET_AFFECT_ROW number: the execution is successful 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 is successful. If no record is updated, the update operation fails.

    Example:

    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:

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