MySQL のロック タイプとロック原則についての今日の深い理解
- coldplay.xixi転載
- 2020-10-05 15:06:271961ブラウズ
関連する無料学習の推奨事項: mysql チュートリアル
序文
- MySQL インデックスの最後レイヤー データ構造とアルゴリズム
- MySQL パフォーマンス最適化の原則 - パート 1
- MySQL パフォーマンス最適化 - 実践 1
- MySQL パフォーマンス最適化 - 実践 2
- MySQL ロックおよびトランザクション分離レベル
#MySQL データベースの基礎となるデータ構造とアルゴリズム、および MySQL パフォーマンスの最適化に関する内容について説明しました。前回の記事では、MySQL の行ロックとトランザクション分離レベルについて説明しました。この記事では、ロックの種類とロックの原則に焦点を当てます。
最初に mysql ロックを分割します:
- ロックの粒度に従って分割します: 行ロック、テーブル ロック、ページ ロック
- ロックの使用方法: 共有ロック、排他ロック (悲観的ロックの実装)
- また、悲観的ロックと楽観的ロックという 2 つのイデオロギー ロックもあります。
- InnoDB には行レベルのロック タイプがいくつかあります: レコード ロック、ギャップ ロック、ネクストキー ロック
- レコード ロック: インデックス レコードをロックします
- ギャップ ロック: ギャップLock
- Next-key Lock：レコード ロック ギャップ ロック
テーブル ロック
テーブル レベルのロックは、MySQL ロックの中で最も粒度の細かいロックであり、現在のテーブル全体をロックする操作です。リソースのオーバーヘッドは行ロックよりも低く、デッドロックは発生しませんが、ロック競合が発生する可能性が高くなります。ほとんどの mysql エンジンでサポートされている MyISAM と InnoDB はどちらもテーブル レベルのロックをサポートしていますが、InnoDB のデフォルトは行レベルのロックです。
テーブル ロックは MySQL Server によって実装されており、通常、ALTER TABLE やその他の操作などの DDL ステートメントを実行すると、テーブル全体がロックされます。 SQL ステートメントを実行するときに、ロックするテーブルを明示的に指定することもできます。
テーブル ロックはワンタイム ロック テクノロジを使用します。つまり、セッションの開始時に lock コマンドを使用して、後で使用されるすべてのテーブルをロックします。テーブルが解放される前は、これらの追加されたテーブルのみがロックされます。ロックされたテーブルは、テーブルのロックを解除してすべてのテーブルのロックが最終的に解放されるまで、他のテーブルにアクセスできません。
unlock テーブルを使用してリリース ロックを表示することに加え、セッションが他のテーブル ロックを保持しているときに lock table ステートメントを実行すると、セッションによって以前に保持されていたロックが解放されます。開始トランザクションを実行するか、トランザクションのオープンが開始されると、セッションが他のテーブル ロックを保持している場合、以前に保持されていたロックも解放されます。
共有ロックの使用法：
LOCK TABLE table_name [ AS alias_name ] READ复制代码
排他的ロックの使用法：
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码
ロック解除の使用法：
unlock tables;复制代码
行ロック
行レベルのロックは Mysql で最も詳細なロックであり、現在の操作の行のみがロックされることを意味します。 行レベルのロックにより、データベース操作の競合を大幅に軽減できます。ロックの粒度は最も小さくなりますが、ロックのオーバーヘッドも最大になります。デッドロック状況が発生する可能性があります。 行レベルのロックは、使用方法に応じて共有ロックと排他ロックに分類されます。
ストレージ エンジンが異なれば、行ロックの実装も異なります。後で特別な説明がない場合、行ロックは特に InnoDB によって実装される行ロックを指します。
InnoDB のロック原理を理解する前に、そのストレージ構造についてある程度理解しておく必要があります。 InnoDB はクラスター化インデックスです。つまり、B ツリーのリーフ ノードには主キー インデックスとデータ行の両方が格納されます。 InnoDB のセカンダリ インデックスのリーフ ノードには主キーの値が格納されるため、セカンダリ インデックスを通じてデータをクエリする場合は、クラスター化インデックス内の対応する主キーを取得して再度クエリする必要があります。 MySQL インデックスの詳細については、「MySQL インデックスの基礎となるデータ構造とアルゴリズム」を参照してください。
update user set age = 10 where id = 49; update user set age = 10 where name = 'Tom';复制代码最初の SQL は主キー インデックスを使用してクエリを実行し、ID = 49 の主キー インデックスに書き込みロックを追加するだけで済みます。2 番目の SQL はセカンダリ インデックスを使用しますquery するには、最初にインデックス名 = Tom に書き込みロックを追加し、次に InnoDB セカンダリ インデックスを使用して主キー インデックスに基づいて再度クエリを実行するため、主キー インデックス ID = 49 にも書き込みロックを追加する必要があります。上図に示すように。 つまり、主キー インデックスを使用するにはロックを追加する必要があり、セカンダリ インデックスを使用するにはセカンダリ インデックスと主キー インデックスにロックを追加する必要があります。 インデックスに基づいてデータの単一行を更新するロック原理を理解したところで、次の SQL 実行シナリオのように、更新操作に複数の行が含まれる場合はどうなるでしょうか。
update user set age = 10 where id > 49;复制代码
共有ロック/排他ロック
共有ロック (Share Lock)
読み取りロックとも呼ばれる共有ロックは、読み取り操作によって作成されるロックです。他のユーザーは同時にデータを読み取ることができますが、すべての共有ロックが解放されるまで、トランザクションはデータを変更する (データの排他的ロックを取得する) ことはできません。
トランザクション T がデータ A に共有ロックを追加した場合、他のトランザクションは共有ロックを A に追加することしかできず、排他ロックを追加することはできません。共有ロックが付与されたトランザクションはデータの読み取りのみが可能で、データを変更することはできません。
使用法
SELECT ... LOCK IN SHARE MODE;
クエリ ステートメントの後に LOCK IN SHARE MODE を追加します。 Mysql は、クエリ結果セットの各行に共有ロックを追加します。他のスレッドがクエリ結果セットの行に排他ロックを使用していない場合、そのスレッドは共有ロックを正常に適用できますが、それ以外の場合はブロックされます。他のスレッドも共有ロックを使用してテーブルを読み取ることができ、これらのスレッドは同じバージョンのデータを読み取ります。
排他ロック (eXclusive Lock)
排他ロックは書き込みロックとも呼ばれます。トランザクション T がデータ A に排他ロックを追加すると、他のトランザクションは A にいかなるタイプのブロックも追加できなくなります。排他的ロックが付与されたトランザクションは、データの読み取りと変更の両方が可能です。
使用法
SELECT ... FOR UPDATE;
クエリ ステートメントの後に FOR UPDATE を追加すると、Mysql は排他的な処理を実行します。結果セットの各行にロックが追加されます。他のスレッドがクエリ結果セットの行に対して排他ロックを使用していない場合、そのスレッドは排他ロックを正常に適用できますが、それ以外の場合はブロックされます。
オプティミスティック ロックとペシミスティック ロック
データベースのロック メカニズムで紹介したように、データベース管理システム (DBMS) における同時実行制御のタスクは、複数のトランザクションが同じデータベースにアクセスできるようにすることです。データ処理は、トランザクションの分離と統一性、およびデータベースの統一性を破壊しません。
オプティミスティック同時実行制御 (オプティミスティック ロック) とペシミスティック同時実行制御 (ペシミスティック ロック) は、同時実行制御に使用される主な技術手段です。
悲観的ロックであれ、楽観的ロックであれ、それらは人によって定義された概念であり、一種の思想と考えることができます。実際、リレーショナル データベース システムには楽観的ロックと悲観的ロックの概念が存在するだけでなく、memcache、hibernate、tair などにも同様の概念があります。
さまざまなビジネス シナリオでは、さまざまな同時実行制御方法を選択する必要があります。したがって、狭義のオプティミスティック同時実行制御およびペシミスティック同時実行制御をDBMSの概念として理解せず、データに備わっているロック機構(行ロック、テーブルロック、排他ロック、共有ロック)と混同しないでください。実際、DBMS では、データベース自体が提供するロック機構を使用して悲観的ロックが実装されます。
悲観的ロック
リレーショナル データベース管理システムでは、悲観的同時実行制御 (「悲観的ロック」、悲観的同時実行制御、略称「PCC」とも呼ばれます) は同時実行制御の一種です。方法。これにより、トランザクションが他のユーザーに影響を与えるような方法でデータを変更することを防ぎます。トランザクションによって実行される操作によってデータの特定の行にロックが適用される場合、そのトランザクションがロックを解放する場合にのみ、他のトランザクションはロックと競合する操作を実行できます。ペシミスティック同時実行制御は主に、データ競合が激しい環境、および同時実行競合が発生したときにデータを保護するためにロックを使用するコストがトランザクションをロールバックするコストよりも低い環境で使用されます。
悲観的ロックは、その名前が示すように、外部 (このシステムの他の現在のトランザクションや外部システムからのトランザクション処理を含む) によって変更されるデータに対する保守的な態度 (悲観的) を指します。 、データ処理プロセス全体を通じてデータをロック状態に保ちます。悲観的ロックの実装は、多くの場合、データベースによって提供されるロック メカニズムに依存します (データ アクセスの排他性を真に保証できるのは、データベース層によって提供されるロック メカニズムだけです。それ以外の場合、たとえロック メカニズムがこのシステムに実装されていても、データ)
悲観的ロックの特定のプロセス
- レコードを変更する前に、レコードに排他的ロックを追加してみてください;
- ロックが失敗した場合は、レコードが変更中であることを意味し、現在のクエリは待機するか例外をスローする必要がある可能性があります。具体的な応答方法は、実際のニーズに基づいて開発者によって決定されます。
- ロックが正常にロックされた場合、レコードは変更でき、トランザクションの完了後にロックが解除されます。
- レコードを変更するか排他ロックを追加する他の操作がある場合、それらはロックを解除するか直接例外をスローするまで待機します。
悲観的ロックの長所と短所
悲観的ロックは実際には「アクセスする前に最初にロックを取得する」戦略を採用しており、データ処理のセキュリティを保証しますが、追加のロック機構により追加のオーバーヘッドが生成され、デッドロックの可能性が増加するため、効率が低下します。また、同時実行性も低下します。あるものがデータ行を取得すると、他のものはそのデータ行を操作する前にトランザクションが送信されるまで待機する必要があります。
オプティミスティック ロック
リレーショナル データベース管理システムでは、オプティミスティック同時実行制御 (「オプティミスティック ロック」、Optimistic Concurrency Control、略称「OCC」とも呼ばれます) は同時実行制御の 1 つの方法です。マルチユーザーの同時トランザクションは処理中に相互に影響を与えず、各トランザクションはロックを生成せずに影響するデータの部分を処理できることを前提としています。データ更新をコミットする前に、各トランザクションはまず、トランザクションがデータを読み取った後に他のトランザクションがデータを変更したかどうかを確認します。他のトランザクションに更新がある場合、コミット中のトランザクションはロールバックされます。
楽観的ロック (楽観的ロック) 悲観的ロックと比較すると、楽観的ロックは、通常の状況ではデータが競合を引き起こさないことを前提としているため、データが更新のために送信されたときにのみデータが形式的に競合します。検出するかどうか、競合が見つかった場合はエラー情報をユーザーに返し、ユーザーが何をするかを決定します。
悲観的ロックと比較して、楽観的ロックは、データベースの処理時にデータベースが提供するロック メカニズムを使用しません。オプティミスティック ロックを実装する一般的な方法は、データのバージョンを記録することです。
データ バージョン。データに追加されるバージョン識別子。データを読み込む際にはバージョン識別子の値も一緒に読み込まれ、データが更新されるたびにバージョン識別子も同時に更新されます。更新を送信するとき、データベース テーブルの対応するレコードの現在のバージョン情報と、最初に取り出したバージョン識別値を比較します。データベース テーブルの現在のバージョン番号が、取り出したバージョン識別値と等しい場合は、初めて更新する場合は更新してください。更新しない場合は、期限切れのデータとみなされます。
オプティミスティック ロックの長所と短所
オプティミスティック同時実行制御では、トランザクション間でデータ競合が発生する可能性は比較的小さいと考えられるため、ロックがロックされるまでできるだけ直接実行します。送信時にロックやデッドロックが発生しません。ただし、これを単純に実行した場合でも、予期しない結果が発生する可能性があります。たとえば、2 つのトランザクションがデータベースの特定の行を読み取り、変更後にデータベースに書き戻す場合、問題が発生します。
意図共有ロック/意図排他ロック
テーブルロックと行ロックはロック範囲が異なるため、相互に競合します。したがって、テーブル ロックを追加する場合は、まずテーブル内のすべてのレコードを調べて、排他ロックが追加されているかどうかを判断する必要があります。このトラバーサル チェック方法は明らかに非効率的な方法であり、MySQL ではテーブル ロックと行ロックの間の競合を検出するためにインテンション ロックが導入されています。
意図ロックもテーブル レベルのロックであり、読み取り意図ロック (IS ロック) と書き込み意図ロック (IX ロック) に分けることもできます。トランザクションがレコードに読み取りロックまたは書き込みロックを追加する場合は、まずテーブルにインテンション ロックを追加する必要があります。このように、テーブル内にロックされているレコードがあるかどうかの判断は非常に簡単で、テーブルに意図的なロックがかかっているかどうかを確認するだけで済みます。
インテンション ロックは互いに競合せず、AUTO_INC テーブル ロックとも競合しません。ブロックされるのはテーブル レベルの読み取りロックまたはテーブル レベルの書き込みロックのみです。また、インテンション ロックは行と競合しません。ロック。行ロックは行ロックとのみ競合します。
インテンション ロックは InnoDB によって自動的に追加されるため、ユーザーの介入は必要ありません。
挿入、更新、削除の場合、InnoDB は関係するデータに排他ロック (X) を自動的に追加します;
一般的な Select ステートメントの場合、InnoDB はロックやトランザクションを追加しません。次のステートメントを使用して、共有ロックまたは排他ロックを表示に追加できます。
意図共有ロック (意図共有ロック)
意図共有ロック (IS): トランザクションがデータ行に共有ロックを追加する準備をしていることを示します。つまり、データ行は、テーブルの IS ロック
意図排他ロック (排他ロック)
意図排他ロック (IX): 上記と同様、トランザクションが準備中であることを示します。データ行に排他ロックを追加し、トランザクションがデータ行にあることを示します。 排他ロックを追加する前に、まずテーブルの IX ロックを取得する必要があります。
レコード ロック
レコード ロックは最も単純な行ロックであり、言うことはありません。上で説明した InnoDB ロック原則のロックはレコード ロックであり、id = 49 または name = 'Tom' のレコードのみをロックします。
SQL ステートメントがインデックスを使用できない場合、テーブル全体のスキャンが実行されます。このとき、MySQL はテーブル全体のすべてのデータ行にレコード ロックを追加し、MySQL Server レイヤーがそれらをフィルタリングします。 。ただし、MySQL Server レイヤーでフィルタリングする場合、WHERE 条件が満たされていないことが判明すると、対応するレコードのロックが解除されます。これにより、最終的には条件を満たすレコードのみに排他がかかることになりますが、各レコードの排他操作を省略することはできません。
したがって、更新操作はインデックスに基づいて実行する必要があります。インデックスがないと、大量のロック リソースが消費され、データベースのオーバーヘッドが増加するだけでなく、データベースの同時実行パフォーマンスも大幅に低下します。
ギャップ ロック
等値条件ではなく範囲条件を使用してデータを取得し、共有ロックまたは排他ロックをリクエストすると、InnoDB は条件を満たす既存のデータ レコードのインデックスを提供します。項目は次のとおりです。ロック済み; キー値が条件範囲内にあるが存在しないレコードの場合、InnoDB は「ギャップ」もロックします。このロック メカニズムはいわゆるギャップ ロックです。
间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。
要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog
show variables like 'innodb_locks_unsafe_for_binlog';复制代码
innodb_locks_unsafe_for_binlog：默认
值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。
# 在 my.cnf 里面的[mysqld]添加 [mysqld] innodb_locks_unsafe_for_binlog = 1复制代码
案例1：唯一索引的间隙锁
测试环境：
MySQL5.7，InnoDB，默认的隔离级别（RR）
示例表：
CREATE TABLE `my_gap` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码
在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：
- (-infinity, 1]
- (1, 5]
- (5, 7]
- (7, 11]
- (11, +infinity]
只使用记录锁（行锁），不会产生间隙锁
/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。
产生间隙锁
恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：
/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。
恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？
/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
结论
- 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
- 对于查找某一范围内的查询语句，会产生间隙锁。
案例2：普通索引的间隙锁
示例表：id 是主键，在 number 上，建立了一个普通索引。
# 注意：number 不是唯一值CREATE TABLE `my_gap1` ( `id` int(1) NOT NULL AUTO_INCREMENT, `number` int(1) NOT NULL COMMENT '数字', PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码
在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：
- (-infinity, 1]
- (1, 3]
- (3, 8]
- (8, 12]
- (12, +infinity]
测试1
我们执行以下的事务（事务1最后提交），分别执行下面的语句：
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码
我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：
这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。
测试2
我们再进行以下测试，这里将数据还原成初始化那样
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码
查看表中的数据；
这里有一个奇怪的现象：
- 事务3 添加 id = 6，number = 8 的数据，阻塞了；
- 事务4 添加 id = 8，number = 8 的数据，正常执行了；
- 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。
这是为什么？我们来看看下面的图：
从图中库看出，当 number 相同时，会根据主键 id 来排序
- 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
- 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
- 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。
结论
- 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
- 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。
临键锁（Next-key Locks）
临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：
(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码
通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)
此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。
注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。
插入意向锁（Insert Intention Locks）
插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。
插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。
插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。
插入意向锁的作用：
- 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
- 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
- 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。
自增锁（Auto-inc Locks）
AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：
- AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
- 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。
自增操作
使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：
- auto_increment_offset 表示起始数字
- auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）
通过使用last_insert_id()函数可以获得最后一个插入的数字
select last_insert_id();复制代码
自增锁
首先insert大致上可以分成三类：
- simple insert 如insert into t(name) values('test')
- bulk insert 如load data | insert into ... select .... from ....
- mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');
如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：
- 0 ：traditonal (每次都会产生表锁)
- 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
- 2 ：interleaved （不会锁表，来一个处理一个，并发最高）
MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。
show variables like 'innodb_autoinc_lock_mode';复制代码
traditonal
innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。
- 它提供来一个向后兼容的能力
- 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
- 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
- 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。
consecutive
innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。
- 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
- 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。
interleaved
innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。
- 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。
如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。
由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。
概要
InnoDB ロックの特徴
- インデックス条件なしでクエリを実行する場合、InnoDB は実際にテーブル ロックを使用します。
- MySQL の行ロックはレコードではなくインデックスに対するロックであるため、異なる行のレコードにアクセスしても同じインデックスキーを使用するとロックの競合が発生します。
- テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なる行をロックできます。さらに、主キー インデックス、一意のインデックス、または通常のインデックスのいずれを使用しているかに関係なく、InnoDB は行ロックを使用してデータをロックします。
- 条件でインデックス フィールドが使用されている場合でも、データの取得にインデックスを使用するかどうかは、さまざまな実行プランのコストを判断して MySQL によって決定されます。MySQL がテーブル全体のスキャンの方が効率的であると判断する場合は、非常に大きなテーブルなど、小さなテーブルの場合はインデックスを使用しません。その場合、InnoDB は行ロックの代わりにテーブル ロックを使用します。そのため、ロック競合を分析する際には、SQL実行プラン(Explain View)をチェックして実際にインデックスが使用されているかどうかを確認することを忘れないでください。
ロック モード
ロック モードは、読み取り意図ロック、書き込み意図ロック、読み取りロック、書き込みロック、および自動インクリメント ロック (auto_inc) です。
さまざまなモード ロックの互換性マトリックス
| ##IS | IX | S | XX | AI | |
|---|---|---|---|---|---|
| ##互換性あり | ##互換性あり | 互換性のある | 互換性のある | IX | |
| #互換性のある | 互換性あり | 互換性あり | |||
| 互換性 | ##XX |
||||
|
# #AI |
|||||
| ##要約すると、次の点があります: |
インテンション ロックは相互に排他的な競合ではありません。 | S ロックは S/IS ロックとのみ互換性があり、他のロックと競合します;
XX ロックは他のすべてのロックと競合します; |
ロックの種類
- ロックの粒度に応じて、ロックは
- テーブル ロック と
- 行ロック#に分割できます。 ##、行ロックはさまざまなシナリオに従ってさらに細分化でき、その後に Next-Key Lock
- 、 Gap Lock ギャップ ロック
- 、 Record Lock レコード ロック
を挿入します。
異なるロックは異なる位置をロックします。たとえば、レコード ロックは対応するレコードのみをロックしますが、ギャップ ロックはレコード間の間隔をロックし、ネクスト キー ロックはレコードの前のレコードをロックします。各種ロックのロック範囲は大まかに下図のとおりです。 #さまざまなタイプのロックの互換性マトリックス
レコード
|
II ギャップ | ##記録 | ##互換性のある | ||
|---|---|---|---|---|
| GAP |
互換性あり | #互換性のある |
互換性のある | |
| ##NEXT-KEY | 互換性 | 互換性あり | ||
| II ギャップ | ## ＃＃＃＃＃＃＃＃＃＃＃＃互換性がある＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃ このうち、最初の行は既存のロックを表し、最初の列は追加するロックを表します。インテンション ロックの挿入は特殊なので、最初にインテンション ロックの挿入について次のように要約します。
他のタイプのロックのルールは比較的単純です:
|
以上がMySQL のロック タイプとロック原則についての今日の深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。