データのクエリーを実行してから、同じトランザクション内で関連データを挿入または更新する場合は、通常の SELECT ステートメントで十分な保護が提供されません。 ほかのトランザクションは、クエリーが実行されたばかりの同じ行を更新または削除できます。 InnoDB では、追加の安全性が提供される 2 つのタイプのロック読み取りがサポートされています。
-
SELECT ... FOR SHARE読み取られる行に共有モードロックを設定します。 ほかのセッションもその行を読み取ることができますが、トランザクションがコミットするまで変更することはできません。 これらの行のいずれかがコミットされていない別のトランザクションによって変更された場合、クエリーはそのトランザクションが終了するまで待機してから、最新の値を使用します。
注記SELECT ... FOR SHAREはSELECT ... LOCK IN SHARE MODEの代替機能ですが、LOCK IN SHARE MODEは下位互換性のために引き続き使用できます。 ステートメントは同等です。 ただし、FOR SHAREはOF、table_nameNOWAITおよびSKIP LOCKEDオプションをサポートしています。 NOWAIT および SKIP LOCKED による読取り同時実行性のロックを参照してください。MySQL 8.0.22 より前は、
SELECT ... FOR SHAREにはSELECT権限と、DELETE、LOCK TABLESまたはUPDATEのいずれかの権限が必要です。 MySQL 8.0.22 では、SELECT権限のみが必要です。MySQL 8.0.22 の時点では、
SELECT ... FOR SHAREステートメントは MySQL 付与テーブルの読取りロックを取得しません。 詳細は、テーブル同時実行性の付与を参照してください。 -
SELECT ... FOR UPDATE検索で検出されたインデックスレコードについては、それらの行に対して
UPDATEステートメントを発行した場合と同じように、行および関連するインデックスエントリがロックされます。 他のトランザクションは、これらの行の更新、SELECT ... FOR SHAREの実行、または特定のトランザクション分離レベルでのデータの読取りをブロックされます。 一貫性読み取りでは、読み取られたビュー内に存在するレコードに設定されたロックはすべて無視されます。 (古いバージョンのレコードはロックできません。レコードのインメモリーコピー上の Undo ログに適用することで、再構築されます。)SELECT ... FOR UPDATEには、SELECT権限と、DELETE、LOCK TABLESまたはUPDATEのいずれかの権限が必要です。
これらの句は、主に、単一のテーブル内または複数のテーブルに分割された状態で、ツリー構造またはグラフ構造のデータを処理する際に役立ちます。 エッジまたはツリー分岐をある場所から別の場所にトラバースしても、これらの「ポインタ」に戻ってその値を変更する権利を保有しています。
FOR SHARE および FOR UPDATE クエリーによって設定されたすべてのロックは、トランザクションがコミットまたはロールバックされると解放されます。
ロック読取りは、(START TRANSACTION でトランザクションを開始するか、autocommit を 0 に設定することで) 自動コミットが無効になっている場合にのみ可能です。
外部ステートメントのロック読取り句では、サブクエリーにロック読取り句も指定されていないかぎり、ネストしたサブクエリーのテーブルの行はロックされません。 たとえば、次のステートメントでは、テーブル t2 の行はロックされません。
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;
テーブル t2 の行をロックするには、サブクエリーにロック読取り句を追加します:
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
child テーブルに新しい行を挿入し、子の行が parent テーブル内に親の行を持っていることを確認すると仮定します。 アプリケーションコードを使用して、この操作シーケンス全体の参照整合性を確保できます。
まず、一貫性読み取りを使用して、PARENT テーブルでクエリーを実行し、親の行が存在することを確認します。 CHILD テーブルに子の行を安全に挿入できますか。 気付かないうちに、その他の一部のセッションで、SELECT と INSERT との間に親の行が削除された可能性もあるため、できません。
この潜在的な問題を回避するには、FOR SHARE を使用して SELECT を実行します:
SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;
FOR SHARE クエリーで親'Jones'が返された後、子レコードを CHILD テーブルに安全に追加し、トランザクションをコミットできます。 PARENT テーブルの該当する行で排他ロックを取得しようとするトランザクションは、終了するまで、つまりすべてのテーブルのデータが一貫性のある状態になるまで待機します。
もう 1 つの例では、CHILD テーブルに追加された各子に一意の識別子を割り当てる際に使用される CHILD_CODES テーブル内の整数カウンタフィールドを検討します。 一貫性読み取りまたは共有モード読み取りを使用すると、データベースの 2 人のユーザーが同じカウンタ値を参照する可能性があり、2 つのトランザクションが同じ識別子を持つ行を CHILD テーブルに追加しようとすると、重複キーのエラーが発生するため、カウンタの現在の値を読み取る際には使用しないでください。
ここでは、2 人のユーザーがカウンタを同時に読み取った場合、カウンタを更新しようとすると少なくとも 1 人のユーザーがデッドロックになるため、FOR SHARE は適切なソリューションではありません。
カウンタの読み取りおよび増分を実装するには、まず FOR UPDATE を使用してカウンタのロック読み取りを実行してから、カウンタを増分します。 例:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
SELECT ... FOR UPDATE は使用可能な最新データを読み取り、読み取られる各行上に排他ロックを設定します。 したがって、検索された SQL UPDATE によって行上に設定される場合と同じロックが設定されます。
前述の説明は、単に SELECT ... FOR UPDATE がどのように機能するのかを示した例です。 MySQL では、テーブルへの単一アクセスを使用するだけで、一意の識別子を生成する特定のタスクを実現できます。
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
この SELECT ステートメントは、単に (現在の接続に固有の) 識別子情報を取得するだけです。 どのテーブルにもアクセスしません。
行がトランザクションによってロックされている場合、同じロックされた行をリクエストする SELECT ... FOR UPDATE または SELECT ... FOR SHARE トランザクションは、ブロックしているトランザクションが行ロックを解放するまで待機する必要があります。 この動作により、トランザクションは、他のトランザクションによる更新をクエリーする行を更新または削除できなくなります。 ただし、リクエストされた行がロックされたときにすぐにクエリーを戻す場合、またはロックされた行を結果セットから除外できる場合は、行ロックの解放を待機する必要はありません。
他のトランザクションによる行ロックの解放を待機しないように、SELECT ... FOR UPDATE または SELECT ... FOR SHARE のロック読取りステートメントで NOWAIT および SKIP LOCKED オプションを使用できます。
-
NOWAITNOWAITを使用するロック読取りは、行ロックの取得を待機しません。 クエリーはただちに実行され、リクエストされた行がロックされている場合はエラーで失敗します。 -
SKIP LOCKEDSKIP LOCKEDを使用するロック読取りは、行ロックの取得を待機しません。 クエリーはただちに実行され、ロックされた行が結果セットから削除されます。注記ロックされた行をスキップするクエリーは、データの一貫性のないビューを返します。 したがって、
SKIP LOCKEDは一般的なトランザクション作業には適していません。 ただし、複数のセッションが同じキューに類似したテーブルにアクセスする場合、ロックの競合を回避するために使用できます。
NOWAIT および SKIP LOCKED は、行レベルロックにのみ適用されます。
NOWAIT または SKIP LOCKED を使用するステートメントは、ステートメントベースのレプリケーションでは安全ではありません。
次の例は、NOWAIT および SKIP LOCKED を示しています。 セッション 1 は、単一のレコードで行ロックを取得するトランザクションを開始します。 セッション 2 は、NOWAIT オプションを使用して、同じレコードに対するロック読取りを試行します。 リクエストされた行はセッション 1 によってロックされているため、ロック読取りはすぐにエラーとともに返されます。 セッション 3 では、SKIP LOCKED で読み取られたロックは、セッション 1 でロックされている行を除いて、リクエストされた行を返します。
# Session 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+
# Session 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.
# Session 3:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+