クエリ結果のロック SELECT FOR UPDATE

本記事では、具体的な例を通じて SELECT FOR UPDATE を使用してクエリ結果をロックする方法を説明します。

SELECT FOR UPDATE

OceanBaseデータベースはマルチバージョン同時実行制御（MVCC）をサポートしています。デフォルトでは、読み取りトランザクションは書き込みトランザクションの実行をブロックしませんが、SELECT ... FOR UPDATE を使用して読み取り対象にロックをかけることで、書き込みトランザクションをブロックすることができます。

例

サンプルテーブルを作成し、適切なデータを挿入します。

1. 以下のSQLステートメントを実行して、fruit_order テーブルを作成します。

   CREATE TABLE fruit_order(
     order_id INT NOT NULL AUTO_INCREMENT COMMENT '注文ID',
     user_id BIGINT NOT NULL COMMENT '顧客ID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT '顧客名',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '注文金額',
     order_year SMALLINT NOT NULL COMMENT '注文年',
     PRIMARY KEY (order_id)
     ) COMMENT '注文テーブル';
   

2. 以下のSQLステートメントを実行して、fruit_order テーブルにデータを挿入します。

   INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
     (1,1011,'張三',13.11,'2019'),
     (4,1011,'張三',22.21,'2020'),
     (6,1011,'張三',58.83,'2020'),
     (2,1022,'李四',23.34,'2019'),
     (3,1022,'李四',12.22,'2019'),
     (7,1022,'李四',14.66,'2021'),
     (8,1022,'李四',34.44,'2021'),
     (5,1033,'王五',51.55,'2020'),
     (9,1033,'王五',63.66,'2021');
   

SELECT FOR UPDATEを使用したクエリ結果のロック

1. セッション1で以下のステートメントを実行し、トランザクションを開始します。

   BEGIN;
   

2. セッション1で以下のステートメントを実行し、注文IDが7のクエリ結果をロックします。

   SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   実行結果は次のとおりです：

   +-----------+-------------+------------+
   | user_name | fruit_price | order_year |
   +-----------+-------------+------------+
   | 李四      |       14.66 |       2021 |
   +-----------+-------------+------------+
   1 row in set
   

3. セッション2で以下のステートメントを実行し、注文IDが7の行のfruit_priceデータを16.15に変更します。このSQLステートメントは、上記のセッション1のトランザクションがロールバックまたはCOMMITされるまで待機し、それ以外の場合はタイムアウトエラーが発生するまで待機します。

   UPDATE fruit_order SET fruit_price = 16.15  WHERE order_id = 7;
   

   実行結果は次のとおりです：

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

4. セッション1で以下のステートメントを実行し、トランザクションをコミットします。

   COMMIT;
   

5. セッション2で再度以下のステートメントを実行し、注文IDが7の行のfruit_priceデータを16.15に変更します。

   UPDATE fruit_order SET fruit_price = 16.15  WHERE order_id = 7;
   

   実行結果は次のとおりです：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

6. セッション1で以下のステートメントを実行し、更新後のデータをクエリします。

   SELECT * FROM fruit_order WHERE order_id = 7;
   

   実行結果は次のとおりです：

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | 李四      |       16.15 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set