MySQLの行ロックとテーブルロックの意味と違いの詳細な説明

1. はじめに

行ロックとテーブルロックの違いは面接で頻繁に出てくるはずです。MySQL のロックについて体系的に理解しておく必要があります。詳細については、自分で情報を調べる必要があります。この記事は、その答えを大まかにまとめたものです。

一般的な MySQL エンジンには MyISAM と InnoDB があり、InnoDB は MySQL のデフォルト エンジンです。 MyISAM は行ロックをサポートしていませんが、InnoDB は行ロックとテーブルロックをサポートしています。

他のデータベースと比較すると、MySQL のロック メカニズムは比較的単純です。最も注目すべき特徴は、異なるストレージ エンジンが異なるロック メカニズムをサポートしていることです。

MySQL は、大まかに次の 3 種類のロックにまとめることができます。

• テーブル レベルのロック: オーバーヘッドが低く、ロックが高速で、デッドロックがなく、ロックの粒度が大きく、ロック競合の可能性が最も高く、同時実行性が最も低い。
• 行レベル ロック: オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最も低く、同時実行性は最も高くなります。
• ページロック: オーバーヘッドとロック時間はテーブルロックと行ロックの中間です。デッドロックが発生する可能性があります。ロックの粒度はテーブルロックと行ロックの中間で、同時実行性は平均的です。

ロックするには？

クエリ ステートメント (SELECT) を実行する前に、MyISAM は関連するすべてのテーブルに読み取りロックを自動的に追加します。更新操作 (UPDATE、DELETE、INSERT など) を実行する前に、関連するテーブルに書き込みロックを自動的に追加します。このプロセスではユーザーの介入は必要ないため、通常、ユーザーは LOCK TABLE コマンドを使用して MyISAM テーブルを直接明示的にロックする必要はありません。

明示的なロック:

共有ロック (読み取りロック) を書き込む方法は、共有モードでロックすることです。例:

math>60 の zje から math を選択し、共有モードでロックします。

排他ロック (書き込みロック) は、更新の場合と同じように記述されます。例:

更新のために、math >60 となる zje から math を選択します。

2. テーブルロック

デッドロックは発生せず、ロック競合の可能性が高く、同時実行性は低くなります。

MyISAM エンジン

クエリ ステートメント (選択) を実行する前に、MyISAM は関係するすべてのテーブルに読み取りロックを自動的に追加し、追加、削除、および変更操作を実行する前に、関係するテーブルに書き込みロックを自動的に追加します。

MySQL テーブル レベルのロックには 2 つのモードがあります。

• テーブル共有読み取りロック
• テーブルへの排他書き込みロック

読み取りロックは書き込みをブロックし、書き込みロックは読み取りと書き込みの両方をブロックします。

• MyISAM テーブルに対する読み取り操作では、同じテーブルに対する他のプロセスの読み取り要求はブロックされませんが、同じテーブルに対する書き込み要求はブロックされます。読み取りロックが解除された場合にのみ、他のプロセスからの書き込み操作を実行できます。
• MyISAM テーブルへの書き込み操作は、同じテーブルに対する他のプロセスの読み取りおよび書き込み操作をブロックします。書き込みロックが解除された場合にのみ、他のプロセスの読み取りおよび書き込み操作を実行できます。

MyISAM は、書き込みロック後は他のスレッドが操作を実行できなくなり、更新回数が多いとクエリがロックを取得するのが困難になり、永続的にブロックされるため、書き込みプライマリ テーブル エンジンとしては適していません。

3. 行ロック

デッドロックは発生しますが、ロック競合の可能性は低く、同時実行性は高くなります。

MySQL の InnoDB エンジンは行ロックをサポートしています。Oracle とは異なり、MySQL の行ロックはインデックスを通じてロードされます。つまり、行ロックはインデックスに対応する行に追加されます。対応する SQL ステートメントがインデックスを使用しない場合、テーブル全体がスキャンされ、行ロックは実現できません。代わりに、テーブル ロックが使用されます。このとき、他のトランザクションは現在のテーブルに対して更新または挿入操作を行うことはできません。

テーブル `user` を作成します (
`name` VARCHAR(32) デフォルト NULL,
`count` INT(11) デフォルト NULL,
`id` INT(11) NOT NULL AUTO_INCREMENT,
主キー (`id`)
) エンジン=INNODB AUTO_INCREMENT=7 デフォルト文字セット=utf8

-- ここでは、主キーを id としてユーザーテーブルを作成します。

 

-- A は主キーを使用して挿入操作を実行しますが、トランザクションはコミットされません。update user set count=10 where id=1;
-- B もこの時点で更新操作を実行します: update user set count=10 where id=2;
-- 主キーによって選択されるため、行レベルのロックです。A と B は同じ行に対して操作しないため、B によって実行される操作は実行可能です。-- A は名前による挿入操作を実行しますが、トランザクションはコミットされません。update user set count=10 where name='xxx';
-- B もこの時点で更新操作を実行します: update user set count=10 where id=2;
-- 非主キーまたはインデックスで選択されるため、テーブルレベルのロックにアップグレードされます。
-- B は​​テーブルを更新または挿入できません。A がトランザクションをコミットした場合にのみ、B は正常に実行できます。

更新用

select ステートメントの後に for update を追加すると、クエリされたデータに排他ロックが追加されます。他のトランザクションはそれを読み取ることはできますが、更新または挿入操作を実行することはできません。

-- ユーザー A は、id=1 のレコードをロックします。select * from user where id=1 for update;

-- ユーザー B はこのレコードに対して操作を実行できません。update user set count=10 where id=1;

-- ユーザーAがコミットした後、ユーザーBはレコードを操作できる

行ロックの実装には注意が必要です。

• 行ロックはインデックスを使用してのみ実装できます。そうでない場合、テーブル全体が自動的にロックされ、行ロックにはなりません。
• 2 つのトランザクションが同じインデックスをロックすることはできません。
• 挿入、削除、更新では、トランザクションにデフォルトで排他ロックが自動的に追加されます。

行ロックのシナリオ:

ユーザー A が購入を行うと、サービス レイヤーはまずユーザーのアカウント残高を照会します。残高が十分であれば、その後の控除操作を実行します。この場合、照会中はレコードをロックする必要があります。

それ以外の場合、ユーザー B は、ユーザー A がアカウントを照会した後に購入する前に、ユーザー A のアカウントから資金を送金し、ユーザー A はユーザーの残高が十分かどうかをすでに判断しているため、残高が不足している可能性がありますが、引き落としは成功します。

この状況を回避するには、ユーザー A がレコードを操作するときに更新ロックを実行する必要があります。

拡張機能: ギャップロック

等価条件ではなく範囲条件を使用してデータを取得し、共有ロックまたは排他ロックを要求すると、InnoDB は条件を満たす既存のデータ レコードのインデックス項目をロックします。条件範囲内にキー値が存在しないレコードについては、ギャップと呼ばれます。

InnoDBはこの「ギャップ」もロックします。このロック機構はギャップロックと呼ばれます。

-- ユーザーA
ユーザーを更新し、ID>2かつID<6の場合、count=8を設定します。

-- ユーザー B
ユーザーを更新し、ID=5 の箇所で count=10 を設定します。

ユーザー A が上記の操作を実行した後にトランザクションをコミットしていない場合、B は 2 から 6 までのレコードを更新または挿入できず、ブロックされます。A がトランザクションをコミットすると、B の更新操作が実行されます。

提案：

• 非インデックス行ロックがテーブルロックにエスカレートするのを避けるために、すべてのデータ取得をインデックス経由で行うようにしてください。
• ロックの範囲を最小限に抑えるためにインデックスを合理的に設計する
• インデックス条件を最小限に抑え、ギャップロックを回避する
• トランザクションのサイズを制御し、ロックされるリソースの量と時間の長さを減らすようにしてください。

これで、MySQL の行ロックとテーブルロックの意味と違いに関するこの記事は終了です。MySQL の行ロックとテーブルロックの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。