更新とデータ整合性処理のためのMySQLトランザクション選択の説明

MySQL のトランザクションはデフォルトで自動的にコミットされます (autocommit = 1)。

しかし、状況によっては問題が発生する可能性があります。たとえば、

一度に 1000 件のレコードを挿入する場合、MySQL は 1000 回コミットします。

自動コミットをオフにして [autocommit = 0]、プログラムを通じて制御すると、必要なコミットは 1 つだけになり、トランザクションの特性をより適切に反映できます。

金額や個数など数値を必要とする演算に！

1つの原則を覚えておいてください：まずロックし、次に判断し、最後に更新します

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

SELECT には主に 2 種類の読み取りロックがあります。

• 選択...共有モードでロック
• 更新するには...を選択

トランザクション中に同じデータ テーブルから選択する場合、両方のメソッドは、他のトランザクション データがコミットされるまで実行を待機する必要があります。

主な違いは、1 つのトランザクションが同じフォームを更新しようとすると、LOCK IN SHARE MODE によって簡単にデッドロックが発生する可能性があることです。

簡単に言えば、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;

このとき、 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 を使用するための公式の説明をお読みください。

MySQL SELECT ... FOR UPDATE 行ロックとテーブルロック

上記ではSELECT ... FOR UPDATEの使い方を紹介しましたが、ロックされたデータの判定には注意が必要です。 InnoDB はデフォルトで行レベル ロックを使用するため、主キーが「明示的に」指定されている場合にのみ、MySQL は行ロック (選択したデータのみをロック) を実行します。それ以外の場合、MySQL はテーブル ロック (データ テーブル全体をロック) を実行します。

例えば：

id と name の 2 つのフィールドを持つフォーム products があり、id が主キーであるとします。

例 1: (主キーを明示的に指定し、このデータと行をロックする)

SELECT * FROM products WHERE id='3' FOR UPDATE;

例 2: (主キーなし、テーブルロック)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例3: (主キーが不明、テーブルロック)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例 4: (主キーが不明、テーブルロック)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

楽観的および悲観的なロック戦略

悲観的ロック: データの読み取り時に行をロックし、これらの行に対するその他の更新は、悲観的ロックが終了するまで待機してから続行する必要があります。

楽観的ロック: データの読み取り時にロックは実行されず、更新時にデータが更新されているかどうかが確認され、更新されている場合は現在の更新がキャンセルされます。一般的に、悲観的ロックの待機時間が長すぎて許容できない場合は、楽観的ロックを選択します。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。