状況によっては、一貫性 (非ロック) 読み取りでは都合が悪く、代わりにロック読み取りが必要になることがあります。InnoDB では次の 2 種類のロック読み取りがサポートされています。

LOCK IN SHARE MODE と FOR UPDATE 読み取りによって設定されたロックは、トランザクションがコミットされたりロールバックされたりしたときにリリースされます。

ロック読み取りが役立つ状況の例として、テーブル child に新しい行を挿入する前に、その子行に対応する親行がテーブル parent 内に存在することを確認する場合を考えます。次の解説は、応用コード内でどのように参照整合性を実装するのかを説明したものです。

テーブル parent を読み取るために一貫性読み取りをし、実際にテーブル内に挿入対象となる子行の親を確認したと仮定してください。テーブル child に子行を安全に挿入することができるでしょうか。答えは NO です。あなたの知らない間にほかのセッションがその親行をテーブル parent から削除する可能性があるからです。

この解決法は、LOCK IN SHARE MODE を利用して SELECT をロックモードで実行することです:

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

LOCK IN SHARE MODE での読み取りでは利用可能な最新のデータが読み取られ、その読み取られた行に共有モードロックが設定されます。共有モードロックは、読み取られた行が別の人によって更新されたり、削除されたりすることを防ぎます。また、もし最新データが別のセッションのコミットされていないトランザクションに属していたら、そのトランザクションが終了されるまで待ちます。LOCK IN SHARE MODE クエリーが親 'Jones' を返すのを確認したあと、child テーブルに子レコードを安全に追加し、トランザクションをコミットすることができます。

別の例を見てみましょう:テーブル child に追加された各子供に固有識別子を割り当てるために利用する整数カウンタフィールドが、テーブル child_codes 内にあります。カウンタの現在値を読むために、一貫性読み取りや共有モード読み取りを利用することは、そのデータベースの 2 ユーザーが同じカウンタ値を確認する可能性があり、またその 2 ユーザーが同じ識別子を利用してテーブルに子供を追加しようとすると重複キーエラーが発生するため、良い考えとは言えません。

もし 2 ユーザーがカウンタを同時に読むと、少なくとも 1 ユーザーはカウンタを更新しようとするときにデッドロックになってしまうため、LOCK IN SHARE MODE はこの場合良い解決法とはいえません。

この場合、カウンタの読み取りとインクリメントを適切に実装する方法として、次の 2 つが考えられます。

後者の方法は、次のように実装できます:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A 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 ステートメントは (現在の接続に固有の) 識別子情報を取得するだけです。これはテーブルにアクセスしません。