インベントリヒントを使用してトランザクションを迅速にコミットまたはロールバックする - PolarDB

インベントリヒントを使用すると、トランザクション制御ロジックを SQL ステートメント内に直接埋め込み、ステートメントの実行結果に基づいてトランザクションをコミットまたはロールバックできます。タイムセールや在庫引当などの高同時実行書き込みシナリオでは、複数のトランザクションが同時に同一行の更新を競合し、ロック競合が発生してスループットが低下します。インベントリヒントは、競合する各書き込み操作を 1 ラウンドトリップで解決し、アプリケーション側でのリトライロジックを不要にします。ステートメントキュー機能と併用することで、競合する書き込みをバケット単位でシリアル化し、スループットを最大化できます。

対応バージョン

インベントリヒントは、以下の PolarDB for MySQL バージョンで使用できます。

バージョン	最小カーネルマイナーバージョン
PolarDB for MySQL 8.0	8.0.1.1.1
PolarDB for MySQL 5.7	5.7.1.0.17
PolarDB for MySQL 5.6	すべてのマイナーバージョン

ご利用のクラスターのカーネルマイナーバージョンを確認するには、「エンジンバージョンの照会」をご参照ください。

仕組み

インベントリヒントは、SELECT、UPDATE、INSERT、DELETE ステートメントをサポートします。利用可能なヒントは次の 3 種類です。

ヒント	効果	トリガー条件
`COMMIT_ON_SUCCESS`	現在のトランザクションをコミットします。	ステートメントが正常に実行されました
`ROLLBACK_ON_FAIL`	現在のトランザクションをロールバックします。	ステートメントが失敗した場合
`TARGET_AFFECT_ROW(n)`	影響を受けた行数が `n`	ステートメント実行時

これらのヒントを組み合わせることで、DML 操作とトランザクションの結果を 1 つのステートメント内で一括処理できます。

COMMIT_ON_SUCCESS は、ステートメントが成功した時点で直ちにトランザクションをコミットします。
ROLLBACK_ON_FAIL は、ステートメントが失敗した時点で直ちにトランザクションをロールバックします。
TARGET_AFFECT_ROW(n) は、例えば在庫切れ商品の更新など、行数の不一致をステートメントの失敗として扱い、その失敗を ROLLBACK_ON_FAIL が捕捉します。

上記 3 つのヒントをステートメントキューと組み合わせることで、競合する書き込みをバケット単位でシリアル化し、各トランザクションを 1 回のラウンドトリップで解決できます。

構文

構文は PolarDB for MySQL 5.6 と PolarDB for MySQL 5.7/8.0 で異なります。

COMMIT_ON_SUCCESS

ステートメントが成功した場合にトランザクションをコミットします。

PolarDB for MySQL 5.6

UPDATE COMMIT_ON_SUCCESS t
SET c = c - 1
WHERE id = 1;

PolarDB for MySQL 5.7 and 8.0

UPDATE /*+ COMMIT_ON_SUCCESS */ t
SET c = c - 1
WHERE id = 1;

ROLLBACK_ON_FAIL

ステートメントが失敗した場合にトランザクションをロールバックします。

PolarDB for MySQL 5.6

UPDATE ROLLBACK_ON_FAIL t
SET c = c - 1
WHERE id = 1;

PolarDB for MySQL 5.7 and 8.0

UPDATE /*+ ROLLBACK_ON_FAIL */ t
SET c = c - 1
WHERE id = 1;

TARGET_AFFECT_ROW

影響を受けた行数が指定値と一致しない場合、ステートメントを失敗とみなします。1 を指定すると、少なくとも 1 件のレコードが更新された場合にステートメントは成功します。WHERE 句に一致する行がない場合は、ステートメントは失敗し、エラーが返されます。

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 5.7 and 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.

ステートメントキューとの併用

COMMIT_ON_SUCCESS、ROLLBACK_ON_FAIL、および TARGET_AFFECT_ROW をステートメントキューと組み合わせて使用すると、競合する書き込みを同一バケットにキューイングし、各トランザクションを 1 回のパスで解決できます。

PolarDB for MySQL 5.6 — キーの式またはリテラル値とともに POLARDB_STATEMENT_CONCURRENT_QUEUE を使用します。

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

PolarDB for MySQL 5.7 and 8.0 — /*+ ... */ ヒントブロック内に CCL_QUEUE_VALUE を使用します。

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) CCL_QUEUE_VALUE('id') */ 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 ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) CCL_QUEUE_VALUE(1) */ t
SET col1 = col1 + 1
WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

制限事項

制限事項	適用範囲	詳細
`オートコミット` モード	PolarDB for MySQL 8.0 のみ	`COMMIT_ON_SUCCESS` および `ROLLBACK_ON_FAIL` は、`オートコミット` が有効な状態では実行できません。これらのヒントを使用する前に `オートコミット` を無効にするか、明示的なトランザクション内にステートメントをラップしてください。

オートコミット モードで COMMIT_ON_SUCCESS または ROLLBACK_ON_FAIL を実行すると、次のエラーが返されます。

UPDATE /*+ ROLLBACK_ON_FAIL */ t
SET c = c - 1
WHERE id = 1;
-- ERROR 7531 (HY000): Inventory transactinal hints didn't allowed in autocommit mode