MySQLリードロックの違いと適用シーン分析

リードロックの概念と違いMySQLのトランザクションでデータを問合せ、同じトランザクションで関連データを挿入または更新した場合、従来のSELECT文は十分な保護を提供できません.他の並列トランザクションは、最初のトランザクションでクエリーされた同じローを更新または削除できます.InnoDBは、2つのタイプのリードロックをサポートし、追加のセキュリティを提供します.

SELECT ... LOCK IN SHARE MODEは、読み込んだ行に共有ロックを設定します.他のセッションでは、ローを読み込むことも、ローに共有ロックを追加することもできますが、現在のトランザクションがコミットされるまで、他のセッションでは共有ロックが追加されたローを変更することはできません.ローのいずれかがコミットされていない別のトランザクションによって変更された場合、現在のクエリはトランザクションが終了するまで待機し、最新の値を使用します.

SELECT ... FOR UPDATEは、これらの行でUPDATE文を実行するように、行と関連するインデックスエントリを排他ロックでロックします.ロックされたローに対して、他のトランザクションがUPDATEを実行したり、LOCK IN SHARE MODEを実行したり、特定のトランザクション独立性レベルのデータを読み出したりすることを禁止します.

対照的に、FOR UPDATEのロック方式は同時プログラミングの書き込みロックに似ているが、LOCK IN SHARE MODEは読み取りロックであり、同じ時点で同じ行に1つの書き込みロック、または複数の読み取りロックしか認められていないことが分かった.データ行にロックが正常に追加されると、別のロック試行が待機します.
両方のロックは、通常のSELECT文の読み取りをブロックしません.一貫した読み取り(スナップショット読み取り)は、ロー・レコードに設定されたロックを無視します.(ロー・レコードの古いバージョンはロックできません.ロー・レコードのメモリ・コピーにundo logを適用して再構築できます.)
シーンの適用SELECT ... LOCK IN SHARE MODEのアプリケーションシーンは、2つのテーブルが関係している場合の書き込み操作に適しており、MySQLの公式ドキュメントの例では、2つの関係のあるテーブル:PARENTとCHILDが存在する場合、通常のSELECT文(スナップショット読み)を使用してテーブルPARENTを検索し、親行が存在するかどうかを検証してからCHILDテーブルに子行を挿入するのは安全ですか?答えは否定的です.他のセッションはあなたのこのセッションのSELECTとINSERTの間のある時点で親行を削除する可能性があるので、この削除操作は気づかないでしょう.
このような潜在的な問題を回避するために、SELECTを実行するには、「共有ロックを追加」を使用します.

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

LOCK IN SHARE MODEクエリーでPARENTテーブルの「Jones」というデータ行を返すと、CHILDテーブルにサブレコードを安全に追加してトランザクションをコミットできます.トランザクションのコミット前に、PARENTテーブルの対応するローで排他的ロックを取得しようとするトランザクションは、アクションのコミットが完了するまで待機します.
しかし、同じ表のアプリケーションシーンであれば、例えば、電子商取引システムでは、注文が発生する前に商品の数が1より大きいことを確認し、注文が発生した後に商品の数を1に減らす必要があります.
1.
select id, amount from products where product_name='Apple11'
2.
update products set amount=amount-1 where id = {id}
明らかに上記の方法は問題があります.1でamountの値が1であることが判明した場合、ちょうど他のセッションもその商品を購入して注文が発生した場合、amountは0になり、このセッションの2番目のステップが実行されると問題があります.
では、LOCK IN SHARE MODEを使ってこの行に共有ロックをかけてもいいですか?2つのセッションが同時に共有リード・ロックでロー・レコードをロックしている場合、2つのセッションが2ステップ目のUPDATEを実行すると、他のトランザクションのリード・ロックの解放を待つため、デッドロックが発生して1つのトランザクションがロールバックすることになります.
時間
トランザクション1
トランザクション2
1
SELECT id, amount FROM products WHERE product_name='Apple11'  LOCK IN SHARE MODE;
2
判定amount>1
SELECT id, amount FROM products WHERE product_name='Apple11'  LOCK IN SHARE MODE;
3
UPDATE products SET amount=amount-1 WHERE id = {id};
判定amount>1
4
トランザクション2の読み込みロックの解放を待つ
UPDATE products SET amount=amount-1 WHERE id = {id};
5
待ち続ける
デッドロック検出(Deadlock found when trying to get lock;try restarting transaction)
6
待ち続ける
トランザクションのロールバック
7
トランザクションのコミット
この例では、LOCK IN SHARE MODEがこのシーンでは適用されないことがわかります.2つのトランザクションが共有モードでローをロックしている場合、ローは誰も更新できません(他のトランザクションがローのロックを持っている場合、更新は許可されません).この場合、どちらかがタイムアウトし、ロックが解除され、もう一方が正常に更新されます.
FOR UPDATEを使用して直接書き込みロックを追加し、トランザクション2を一時的にブロックする必要があります.次に、発生時間に基づいた2つの操作の推論手順を示します.
時間
トランザクション1
トランザクション2
1
SELECT id, amount FROM products WHERE product_name='Apple11' FOR UPDATE
2
判定amount>1
SELECT id, amount FROM products WHERE product_name='Apple11'  FOR UPDATE
3
UPDATE products SET amount=amount-1 WHERE id = {id};
トランザクション1の書き込みロックの解放を待つ
4
トランザクションのコミット
トランザクション1の書き込みロックの解放を待つ
5
amount>1が成立しないと判断
6
トランザクションのコミットの終了
上記の2つのケースの解析から,2つのリードロックの適用シーンを得ることができる.LOCK IN SHARE MODEは、2つのテーブルにビジネス関係がある場合のコンシステンシ要件に適しており、FOR UPDATEは、同じテーブルを操作する際にビジネスを保証するコンシステンシ要件に適しています.
まとめ

LOCK IN SHARE MODEは共有ロックであり、複数のトランザクションが1行のリードロックを同時に持つことを許可する.

FOR UPDATEは排他ロックであり、トランザクションがFOR UPDATEでローをロックすると、そのローに対する他のトランザクションの書き込みロックと読み取りロックの取得がブロックされ、逆も同様である.

行ロックは、通常のSELECTクエリのスナップショット読み取りに影響を与えず、MySQLの同時実行能力を保証します.

LOCK IN SHARE MODEは、2つのテーブルにビジネス関係上の一貫性の要件がある場合の操作シーンに適しています.

FOR UPDATEは、同じテーブルを操作する際にビジネスの一貫性を保証する要件に適しています.