参照と更新の競合を防ぐ: MySQLのJSON Duality Viewでのロック無しでの同時実行

MySQL HeatWaveは、トランザクション、分析、機械学習、生成AIサービスを組み合わせた唯一のフルマネージドのMySQLデータベース・サービスです。また、MySQL HeatWave Lakehouseも含まれており、ユーザーはオブジェクト・ストレージ、MySQLデータベース、またはその両方の組合せに格納されているデータを分析できます。ユーザーは、MySQL HeatWaveを利用したアプリケーションを、Oracle Cloud Infrastructure (OCI)、Amazon Web Services (AWS)およびMicrosoft Azureのいずれかのパブリック・クラウドにデプロイできます。

マルチユーザー・データベース・アプリケーションでは、同じ行またはドキュメントへの同時更新は避けられないため、競合やデータの不整合が発生する可能性があります。RESTリクエストなどのステートレス操作を使用して読取りおよび書込み操作を実行すると、問題はさらに複雑になります。これに対処するために、MySQL HeatWaveはETAGを使用して、ロック無しでの楽観的な同時実行性制御を導入しました。この機能により、読取り/書込みの競合を検出する最新の軽量な方法が提供されます。

この投稿では、ETAGがJSON Duality Viewのコンテキストでどのように機能するかを探り、同時操作の保護におけるETAGの使用方法を示します。JSON Duality ViewとETAGは、バージョン9.4で導入され、MySQL HeatWaveマネージド・クラウド・サービスやMySQL 9.4で利用できるようになっています。

なぜ楽観的な排他制御が必要なのか?

あるユーザーのモバイル・アプリケーションがREST GETリクエストを介してデータにアクセスし、その後、別のユーザーがREST PUTリクエストを使用して一部の情報を更新することを決定したとします[図のJillを参照]。基礎となるデータが2つのRESTリクエストで[図のJackを参照]変更された場合、Jillからの書込み(PUT)リクエストは、Jackの変更を知らずに破棄します。このシナリオでは、Jillの2つのRESTリクエスト間の非決定的な時間のためにリソースをロックできないため、主に一般的なデータベース・トランザクションは機能しません。さらに重要なことは、RESTコールはステートレスであり、トランザクションが開始されたデータベースと同じ接続を再利用するという保証はありません。

トランザクションを使用してレコードをロックするのではなく、ロック無しの楽観的な同時実行性制御は、データが前回の読取り以降に変更されたかどうかを更新の時点でチェックします。その場合、更新は拒否されます。これにより、アプリケーションはデータの最新の状態を再読み取りし、アプリケーション・ロジックに基づいて競合を適切に処理できます。

組み込まれた競合検知の仕組みによりアプリケーションをシンプルに

Lロック無しの楽観的なな同時実行性制御が、MySQL JSON Duality Viewsでサポートされています。ETAGは、オブジェクトのコンテンツから計算されたハッシュ値であり、オブジェクトごとに一意のシグネチャとして機能します。これにより、データベースで競合検出を自動的に処理できるため、ユーザーはアプリケーションを簡素化し、ビジネス・ロジックの開発に集中できます。次に、エンドツーエンドの変更ワークフローを順を追って説明し、これがユーザー・エクスペリエンスにどのようなメリットをもたらすかを示します:

• アプリケーションは、REST GETリクエストを発行してMySQLからJSONオブジェクトを取得します。
  • バックグラウンドでは、SELECT文がJSON Duality Viewsで実行され、RESTレスポンスでJSONオブジェクトとそれに関連付けられたETAGの両方が返されます。
• アプリケーションがオブジェクトを変更する場合、REST PUTリクエストを送信します。
  • 変更されたJSONオブジェクトは、最初に取得されたETAGとともに、PUTリクエストで送信されます。
  • この場合は、UPDATE文がJSON Duality Viewsで実行されます。
• データベースは、指定されたETAGがストレージ内の現在のETAGと一致する場合にのみ更新を適用し、オブジェクトが前回の読取り以降に変更されないようにします。
  • ETAGが一致しない場合、変更の競合を防ぐために更新が拒否されます。
  • 拒否の場合、アプリケーションはオブジェクトの最新バージョンをフェッチして、変更を再試行するだけです。

リレーショナル表customerおよびordersを作成し、両方の表を使用してJSON二面性ビューcustomer_orders_dvを作成することで、この機能について説明します。また、いくつかのサンプル・データをJSON二面性ビューに挿入します。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product VARCHAR(100),
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
 
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
    '_id': customer_id,
    'customer_name': name,
    'orders': (
        SELECT JSON_ARRAYAGG(
            JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
                'order_id': order_id,
                'product': product,
                'amount': amount
            )
        )
        FROM orders
        WHERE orders.customer_id = customers.customer_id
    )
)
FROM customers;
 
mysql> INSERT INTO customer_orders_dv VALUES ( '{  "customer_name": "Alice",
    "_id": 1,
    "orders": [
      {"order_id": 1, "product": "Laptop", "amount": 1299.99},
      {"order_id": 2, "product": "Mouse", "amount": 19.99}
    ]
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3  Warnings: 0.

2つの異なるアプリケーションが同じJSONオブジェクトにアクセスしているシナリオを考えてみましょう。どちらのアプリケーションも、JSON Duality Viewからオブジェクトを読み取ることから始まります。次に、各REST GETリクエストに対して実行される基礎となるSQL文を示します。各文は個別の接続で実行され、RESTインタラクションのステートレスな性質をシミュレートします。

mysql> SELECT * FROM customer_orders_dv WHERE data->'$._id' = 1;
+--------------------------------------------------+
| data                                             |
+--------------------------------------------------+
|{                                                 |
| "_id": 1,                                        |
|  "orders": [                                     |
|    {                                             |
|      "amount": 1299.99,                          |
|      "product": "Laptop",                        |
|      "order_id": 1                               |
|    },                                            |
|    {                                             |
|      "amount": 19.99,                            |
|      "product": "Mouse",                         |
|      "order_id": 2                               |
|    }                                             |
|  ],                                              |
|  "_metadata": {                                  |
|    "etag": "e6d40eabf2e070ffd2719c6755d50f1a"    |
|  },                                              |
|  "customer_name": "Alice"                        |
|}                                                 |
+--------------------------------------------------+
1 row in set (0.001 sec)

オブジェクトが読み取った後、1つめののアプリケーションでは、前述のSELECT文からフェッチされたETAGを含むUPDATEを実行して、”orders”テーブルの”amount”列の値を1000に変更します。

mysql> UPDATE customer_orders_dv SET data = '{
   "_id": 1,
   "orders": [
     {
       "amount": 1000,
       "product": "Laptop",
       "order_id": 1
     },
     {
       "amount": 19.99,
       "product": "Mouse",
       "order_id": 2
     }
   ],
   "_metadata": {
     "etag": "e6d40eabf2e070ffd2719c6755d50f1a"
   },
   "customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
Query OK, 1 row affected (0.007 sec)
Rows affected: 1  Warnings: 0.

2つ目のアプリケーションも似たような操作をしたと想定します。ここでは1つめのアプリケーションがすでに変更を行ったレコードの”amount”列の値を2099.99に変更したとします。

mysql> UPDATE customer_orders_dv SET data = '{
   "_id": 1,
   "orders": [
     {
       "amount": 2099.99,
       "product": "Laptop",
       "order_id": 1
     },
     {
       "amount": 19.99,
       "product": "Mouse",
       "order_id": 2
     }
   ],
   "_metadata": {
     "etag": "e6d40eabf2e070ffd2719c6755d50f1a"
   },
   "customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
ERROR 6494 (HY000): Cannot update JSON duality view. The ETAG of the document
in the database did not match the ETAG '"e6d40eabf2e070ffd2719c6755d50f1a"' passed in.

データベース内でオブジェクトがすでに変更されていたためETAGが一致しなくなり、結果として2番目のアプリケーションによる変更の試行は拒否されます。続行するには、アプリケーションでオブジェクトの最新バージョンを再度取得し、変更が必要かどうかを判断する必要があります。その新しいETAGでUPDATEを送信すると、変更が正常に適用されます。

mysql> SELECT * FROM customer_orders_dv WHERE data->'$._id' = 1;
+--------------------------------------------------+
| data                                             |
+--------------------------------------------------+
|{                                                 |
| "_id": 1,                                        |
|  "orders": [                                     |
|    {                                             |
|      "amount": 1299.99,                          |
|      "product": "Laptop",                        |
|      "order_id": 1                               |
|    },                                            |
|    {                                             |
|      "amount": 19.99,                            |
|      "product": "Mouse",                         |
|      "order_id": 2                               |
|    }                                             |
|  ],                                              |
|  "_metadata": {                                  |
|    "etag": "70f912fba716a2cb615d708e99d0f44c"    |
|  },                                              |
|  "customer_name": "Alice"                        |
|}                                                 |
+--------------------------------------------------+
 
mysql> UPDATE customer_orders_dv SET data = '{
   "_id": 1,
   "orders": [
     {
       "amount": 2099.99,
       "product": "Laptop",
       "order_id": 1
     },
     {
       "amount": 19.99,
       "product": "Mouse",
       "order_id": 2
     }
   ],
   "_metadata": {
     "etag": "70f912fba716a2cb615d708e99d0f44c"
   },
   "customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
Query OK, 1 row affected (0.004 sec)
Rows affected: 1  Warnings: 0.

JSON Duality ViewでETAGがない場合

ETAGは、指定された列値を使用して行の128ビット・ハッシュ(ETAG)値を生成する、9.4のMySQLの組込み関数として追加されます。ETAGのドキュメントはこちらです。ユーザーは、この機能を使用して、特定のアプリケーション・ニーズに合せて同時実行性制御メカニズムをカスタマイズできます。

たとえば、次に、ETAG一致が列のサブセットでのみ実行されるリレーショナル表を使用するシナリオを説明します。”students”テーブルの”course”列以外の列でETAGを計算しています。ここでは、受講者のコースが変更された場合でも、ステートレスな同時変更が許可されます。

mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+
| student_id | name  | course | ETAG(student_id, name)           |
+------------+-------+--------+----------------------------------+
|          1 | Alice | CS101  | 8c7b29a51aab99d67d44c0ff559e746b |
+------------+-------+--------+----------------------------------+

mysql> UPDATE students SET course='ML100' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b'; 
Query OK, 1 row affected (0.005 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+ 
| student_id | name  | course | ETAG(student_id, name)           | 
+------------+-------+--------+----------------------------------+
| 1          | Alice | ML100  | 8c7b29a51aab99d67d44c0ff559e746b | 
+------------+-------+--------+----------------------------------+

mysql> UPDATE students SET name='Peter' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b'; 
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+
| student_id | name  | course | ETAG(student_id, name)           |
+------------+-------+--------+----------------------------------+
| 1          | Peter | ML100  | 2642513c12300911c71ed488a6785f0c |
+------------+-------+--------+----------------------------------+

mysql> UPDATE students SET name='Sam' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b';
Query OK, 1 row affected (0.005 sec)
Rows matched: 0 Changed: 0 Warnings: 0  # Update failed

mysql> UPDATE students SET name='Sam' WHERE student_id=1 AND ETAG(student_id, name) = '2642513c12300911c71ed488a6785f0c';
Query OK, 1 row affected (0.006 sec)
Rows matched: 1 Changed: 1 Warnings: 0 # Update successful

この例では、ETAGが”course”列の変更の影響を受けないように、SELECT文およびUPDATE文を用意しました。これにより、ユーザーは独自の要件を用意して、アプリケーションのロジックに基づいて更新を拒否できます。ETAGの計算では、必ず主キーを引数の1つとして指定することをお薦めします。主キーが含まれていない場合は重複レコードで同一のETAGが生成される可能性があります。したがって、この例では、student_id列をname列とともに1つの引数として含めました。

まとめ

JSON Duality ViewのETAGサポートにより、MySQLは、ドキュメントベースのJSONを活用したアプリケーションでロック無しで楽観的な同時実行性制御のための堅牢なソリューションを提供します。このアプローチにより、複数の変更が同時多発的に発生するアプリケーションで高いパフォーマンスを維持しながら、データの整合性が向上します。最適化された同時実行制御メカニズムを使用する主な利点は次のとおりです:

• 明示的なロック不要: スケーラビリティの向上
• 軽量:ETAGチェックのみが必要
• アプリケーションでの制御: 競合処理(ユーザーの意思確認、変更のマージ、再試行など)が柔軟に

詳しくは: MySQL JSON Duality Viewリファレンス・マニュアル

MySQL 9.4でぜひお試しください!

• MySQL Heatwaveで利用可能  (無料で利用する)
• MySQL Enterprise Editionで利用可能 (無料でダウンロード / 開発ライセンスで永続利用可能)
• MySQL Community Editionで利用可能 (参照専用ビュー)