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

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

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

ロック動作

ある行が他のトランザクションによってロックされている場合、そのロックが解放されるまで待機することを避けるため、SELECT ... FOR UPDATE のようなロック読み取り文と合わせてNOWAIT および SKIP LOCKED オプションを使用することができます。詳細は以下のとおりです：

• SELECT ... FOR UPDATE 句を使用する際、次のような動作になります。

  • ロック待機：特定の行が他のトランザクションによってロックされている場合、現在のトランザクションはロックが解除されるか、待機時間がタイムアウトするまで待機します。必要なロックを取得すると、トランザクションは実行を続けます。
  • 他のトランザクションをブロックします：現在のトランザクションが特定の行のロックを保持している場合、別のトランザクションが同じ行をロックしようとすると、そのトランザクションは現在のトランザクションが行ロックを解除するまでブロックされます。

• SELECT ... FOR UPDATE NOWAIT 句を使用する場合、次のような動作になります。

  トランザクションが行をロックしようとしたとき、その行が別のトランザクションによってロックされている場合、ロックが解放されるのを待つのではなく、即座にエラーが返されます。

• SELECT ... FOR UPDATE SKIP LOCKED 句を使用する場合、次のような動作になります。

  トランザクションが行をロックしようとしたとき、その行が別のトランザクションによってロックされている場合、その行をスキップして次の行の処理を続けます。

例

サンプルテーブルを作成し、テストデータを入力します。

1. テーブル 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. テーブル fruit_order にテストデータを挿入します。

   INSERT INTO fruit_order(user_id, user_name, fruit_price, order_year) VALUES
     (1011,'Zhang San',13.11,'2019'),
     (1011,'Zhang San',22.21,'2020'),
     (1011,'Zhang San',58.83,'2020'),
     (1022,'Li Si',23.34,'2019'),
     (1022,'Li Si',12.22,'2019'),
     (1022,'Li Si',14.66,'2021'),
     (1022,'Li Si',34.44,'2021'),
     (1033,'Wang Wu',51.55,'2020'),
     (1033,'Wang Wu',63.66,'2021');
   

FOR UPDATEを使用してクエリ結果をロックする

1. 以下のステートメントを実行して、自動コミット機能を無効にします。

   SET GLOBAL autocommit = 0;
   

   自動コミット機能に関する情報は、autocommitを参照してください。

   注意

   Globalレベルの変数を設定しても、現在のセッションには無効であり、有効にするには、再ログインして新しいセッションを確立する必要があります。

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

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

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

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | Li Si      |       34.44 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set
   

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

   obclient [test]> 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に変更します。

   obclient [test]> 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. セッション2で次のステートメントを実行し、トランザクションをコミットします。

   COMMIT;
   

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

   obclient [test]> 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
   

NOWAITまたはSKIP LOCKEDオプションを使用してクエリ結果をロックする

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

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

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

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

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

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE NOWAIT;
   

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

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

3. セッション3で以下のステートメントを実行し、FOR UPDATE SKIP LOCKED を使用して注文IDが7以上のクエリ結果をロックします。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id >= 7 FOR UPDATE SKIP LOCKED;
   

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

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        8 |    1033 | Wang Wu      |       51.55 |       2020 |
   |        9 |    1033 | Wang Wu      |       63.66 |       2021 |
   +----------+---------+-----------+-------------+------------+
   2 rows in set
   

関連ドキュメント

SELECT