MySQLのトランザクションとデータ一貫性処理の問題を分析する

この記事では、セキュリティ、使用方法、同時処理などを通じて、MySQL トランザクションとデータの一貫性処理を詳細に分析します。全文は次のとおりです。

仕事では、このような問題によく遭遇します。在庫を更新する必要があります。利用可能な在庫を照会して変更する準備をしているときに、他のユーザーが在庫データを変更している可能性があります。その結果、照会したデータに問題が発生します。解決策を見てみましょう。

MySQL InnoDBでは、デフォルトのトランザクション分離レベルはREPEATABLE READ（再読み取り可能）です。

SELECT 後に同じテーブルを UPDATE する場合は、SELECT ... UPDATE を使用するのが最適です。

例えば：

商品の数量を格納するための商品フォーム products に数量があるとします。注文を行う前に、まず商品の数量が十分かどうか (数量 > 0) を判断し、数量を 1 に更新する必要があります。コードは次のとおりです。

数量を製品から選択します (ID=3)。 数量を 1 に設定します (ID=3)。 製品を更新します (ID=1)。

なぜ安全ではないのですか?

少量の場合は問題ないかもしれませんが、大量のデータにアクセスする場合は必ず問題が発生します。数量 > 0 の場合にのみ在庫を減算する必要がある場合、プログラムが最初の SELECT 行で数量 2 を読み取るとします。数値は正しいように見えますが、MySQL が UPDATE を実行しようとしているときに、誰かがすでに在庫を 0 に減算している可能性がありますが、プログラムはそれに気付かず、エラーなしで UPDATE を続行します。したがって、読み取られて送信されるデータが正しいことを確認するには、トランザクションメカニズムを使用する必要があります。

このように MySQL でテストできます。コードは次のようになります。

SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;

このとき、商品データ内のid=3のデータはロックされており（注3）、他のトランザクションは、このトランザクションがコミットされるまで待機しないと実行できません。

SELECT * FROM products WHERE id=3 FOR UPDATE

これにより、他のトランザクションで読み取られた数量番号が正しいことが保証されます。

製品を更新します。数量を '1' に設定し、ID を 3 に設定します。作業をコミットします。

コミットはデータベースに書き込み、製品のロックを解除します。

注 1: BEGIN/COMMIT はトランザクションの開始点と終了点です。2 つ以上の MySQL コマンドウィンドウを使用して、ロック状態を対話的に監視できます。

注 2: トランザクション中、同じデータに対するSELECT ... FOR UPDATEまたは LOCK IN SHARE MODE のみが、他のトランザクションが終了するまで待機してから実行されます。通常の SELECT ... はこれの影響を受けません。

注 3: InnoDB はデフォルトで行レベルロックに設定されているため、データ列のロックについてはこの記事を参照してください。

注 4: InnoDB テーブルに対して LOCK TABLES コマンドを使用しないでください。使用する必要がある場合は、システムで頻繁にデッドロックが発生するのを避けるために、まず InnoDB で LOCK TABLES を使用するための公式の説明をお読みください。

より高度な使い方

最初にクエリを実行してからデータを更新する必要がある場合は、次のようなステートメントを使用するのが最適です。

製品を更新します。set quantity = '1'、WHERE id=3、quantity > 0;

こうすることで、何も追加せずに処理することができます。

MySQLは在庫の過剰販売を防ぐために高い同時実行性を処理する

とても良い記事を見つけたので、勉強のためにここで共有しました。

今日、王さんは私たちにもう一つの教訓を教えてくれました。実は、王さんは昨年、MySQL で高並列性を処理して在庫の過剰販売を防ぐ方法について言及していました。しかし残念なことに、当時は誰もがそれを理解していたにもかかわらず、実際の開発ではまだこれに対する意識がありません。今日は、私の理解に基づいてこの問題を整理したいと思います。そして、将来的にはこのようなコースが増えることを願っています。

まず、在庫の過剰販売の問題について説明しましょう。一般的に、eコマースのウェブサイトでは、グループ購入、フラッシュセール、特別オファーなどのアクティビティが発生します。このようなアクティビティの共通の特徴は、訪問者が急増し、何千人、何万人もの人々が商品を購入するために殺到することです。しかし、販促品であるため、在庫は間違いなく限られています。過剰購入を防ぎ、不必要な損失を防ぐために在庫をどのように管理するかは、多くの電子商取引ウェブサイトのプログラマーにとって頭痛の種であり、最も基本的な問題でもあります。

テクニカル分析から、多くの人が間違いなくトランザクションを思い浮かべるでしょうが、トランザクションは在庫の過剰販売を抑制するための必要条件ではありますが、十分かつ必要な条件ではありません。

例：

総在庫数: 4 個

依頼者: a、製品 1 個 b、製品 2 個 c、製品 3 個

手順は次のとおりです。

beginTranse (トランザクションを開始する)
試す{
 $result = $dbca->query('postID = 12345 の場合、s_store から amount を選択');
 if(結果->金額> 0){
  //quantity は削減を要求された在庫数量です$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 }
}catch($e 例外){
 ロールバック
}
コミット（トランザクションのコミット）

上記のコードは、在庫を管理するために通常記述するものです。ほとんどの人はこのように記述します。大きな問題はないように見えますが、実際には大きな抜け穴が隠れています。データベースへのアクセスは、実際にはディスクファイルへのアクセスです。データベース内のテーブルは実際にはディスクに保存されているファイルであり、1 つのファイルに複数のテーブルが含まれている場合もあります。たとえば、同時実行性が高いため、3 人のユーザー A、B、C がこのトランザクションを入力しました。このとき、共有ロックが生成されます。したがって、選択時に、この 3 人のユーザーが見つけた在庫数は 4 です。同時に、MySQL InnoDB によって見つかった結果はバージョン管理されていることに注意してください。他のユーザーが更新してコミットする前 (つまり、新しいバージョンが生成される前)、現在のユーザーが見つけた結果はまだ古いバージョンです。

次に更新が来ます。3 人のユーザーが同時に更新に到着した場合、更新ステートメントは同時実行をシリアル化します。つまり、同時にここに到着した 3 人のユーザーはソートされ、1 つずつ実行され、排他ロックが生成されます。現在の更新ステートメントがコミットされる前に、他のユーザーは実行を待機します。コミット後、新しいバージョンが生成されます。実行後、インベントリは必ずマイナスになります。ただし、上記の説明に従って、コードを変更して買われすぎ現象を回避することができます。コードは次のとおりです。

beginTranse (トランザクションを開始する)
試す{
 //quantity は削減を要求された在庫数量です$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 $result = $dbca->query('postID = 12345 の場合、s_store から amount を選択');
 if(結果->金額<0){
  新しい例外をスローします('在庫不足');
 }
}catch($e 例外){
 ロールバック
}
コミット（トランザクションのコミット）

あるいは、より簡潔な方法:

beginTranse (トランザクションを開始する)
試す{
 //quantity は削減を要求された在庫数量です$dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');
}catch($e 例外){
 ロールバック
}
コミット（トランザクションのコミット）

1. フラッシュセールの場合、データベースをそれほど高い頻度で読み書きすることはできないため、パフォーマンスに重大な問題が発生します。

フラッシュ販売する必要がある製品をキャッシュに格納するにはキャッシュを使用し、同時実行を処理するにはロックを使用する必要があります。ユーザーからフラッシュセールの注文を受け取ると、他の処理の前に商品の数量が減算（ロック/ロック解除）されます。処理が失敗した場合は、データが 1 増加（ロック/ロック解除）されます。それ以外の場合は、トランザクションは成功です。
商品数が 0 になった場合、その商品は売り切れており、他のユーザーのリクエストは拒否されていることを意味します。

2. これではデータベースを直接操作することはできないので、失敗します。データベースを直接読み書きするとデータベースに過度の負荷がかかるため、キャッシュが必要になります。

販売したい製品 (たとえば 10 個) をキャッシュに入れ、memcache にカウンターを設定してリクエストの数を記録します。販売したい製品の数をこのリクエストブックのベース数として使用できます。たとえば、10 個の製品を販売したい場合、許可されるリクエストは 100 件のみです。カウンターが 100 に達すると、後続の注文はフラッシュセールの終了として表示され、サーバーへの負荷を軽減できます。そして、これらの 100 件のリクエストに基づいて、先着順で最後に支払われたリマインダー製品は数秒で完売します。

3. まず、複数のユーザーが同時に同じレコードを変更すると、後から送信したユーザーが必ず先に送信した結果を上書きします。

これは、ロックメカニズム、楽観的ロック、または悲観的ロックを使用することで直接解決できます。

楽観的ロック: データベースにバージョン番号フィールドを設計し、変更されるたびに 1 ずつ増加します。このようにして、送信時に送信前のバージョン番号を比較して、同時送信かどうかを知ることができます。ただし、アプリケーション内でしか制御できないという欠点があります。アプリケーション間で同じデータが変更されると、楽観的ロックは機能しません。このとき、悲観的ロックを検討できます。

悲観的ロック: これは、口頭でのselect xxxxx from xxxx where xx=xx for update使用するのと同様に、データベースレベルで直接データをロックし、他のスレッドがデータを送信できないようにします。

ロック方式に加えて、受信ロック方式も使用できます。アイデアは、データベースにステータスフラグを設計することです。ユーザーがデータを変更する前に、ステータスフラグが編集中としてマークされます。このようにして、他のユーザーがこのレコードを編集したい場合、システムは他のユーザーが編集中であることを認識し、編集要求を拒否します。これは、オペレーティングシステムでファイルが実行されているときに、そのファイルを変更すると、システムがファイルを編集または削除できないことを通知するのと似ています。

4. データベースレベルでのロックは推奨されません。サーバー側のメモリロック（主キーのロック）を使用することをお勧めします。ユーザーが特定の ID のデータを変更する場合、変更する ID は memcache に保存されます。他のユーザーがこの ID のデータの変更をトリガーし、memcache でこの ID の値を読み取った場合、ユーザーは変更できなくなります。

5. 実際のアプリケーションでは、MySQL は大規模な同時読み取りおよび書き込み操作を直接処理する必要はありません。代わりに、キャッシュ、マスタースレーブライブラリを使用した読み取りと書き込みの分離、テーブルのパーティション分割、キュー書き込みを使用した同時読み取りおよび書き込み操作の削減などの「外部要因」を使用します。

悲観的ロックと楽観的ロック

まず、複数のユーザーが同時に同じレコードを変更すると、後から送信したユーザーが送信した結果が、先に送信したユーザーが送信した結果を必ず上書きします。これは、ロックメカニズム、楽観的ロック、または悲観的ロックを使用することで直接解決できます。

悲観的ロックは、その名前が示すように、非常に悲観的です。データを取得するたびに、他の人がデータを変更する可能性があるので、データを取得するたびにロックします。このようにして、データを取得しようとする他の人は、ロックを取得するまでブロックされます。従来のリレーショナルデータベースでは、行ロック、テーブルロック、読み取りロック、書き込みロックなど、操作を実行する前にすべてロックされるロックメカニズムが多数使用されています。

楽観的ロックは、その名の通り、非常に楽観的です。データを取得するたびに、他の人がそれを変更しないと想定するため、ロックは行いません。ただし、更新時には、この期間中に他の人がデータを更新したかどうかを判断します。バージョン番号などのメカニズムを使用できます。楽観的ロックは、スループットを向上できるマルチ読み取りアプリケーションタイプに適しています。たとえば、データベースが write_condition に類似したメカニズムを提供する場合、実際には楽観的ロックが提供されます。

どちらのロックにもそれぞれ長所と短所があり、どちらが優れているかを単純に定義することは不可能です。楽観的ロックは、データの変更はまれだが、頻繁に読み取られるシナリオに適しています。少数の競合が発生しても、大量のロックオーバーヘッドが節約されるため、システムのスループットが向上します。ただし、競合が頻繁に発生する場合（大量のデータを書き込む場合など）は、上位層アプリケーションが再試行し続けるため、パフォーマンスが低下します。この場合は、悲観的ロックを使用する方が適切です。

実際の戦闘

このテーブルの量を変更し、2つのコマンドラインウィンドウを開きます

最初のウィンドウは A です。

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;

2番目のウィンドウB:

# 注文 ID 124 の在庫数量を更新します。UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

ウィンドウ A がトランザクションを追加し、データをロックしていることがわかります。ウィンドウ B が実行されると、次の問題が発生します。

最初のウィンドウでは送信トランザクションが完了します。

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;
`order_tbl` を更新し、amount = 10 とし、order_id = 124 とします。
仕事をコミットする。

MySQL は高い同時実行性を処理し、在庫の過剰販売を防止します。上記がこの記事の全内容です。まだ質問がある場合は、以下にメッセージを残してディスカッションしてください。

以下もご興味があるかもしれません: