MySQL チュートリアルコラムでは、ロックがファントム読み取りの問題を解決する方法を紹介します。

ロックとは何か、および MySQL のファントム読み取り問題を解決する方法を理解する

まえがき

今日は、MySQL のロックに関する知識を紹介します。

特に明記されていない限り、この記事ではデフォルトの InnoDB エンジンを使用します。他のエンジンやデータベースが関係する場合は、特に指摘されます。

ロックとは

ロックは、各トランザクションが同時シナリオで一貫した方法でデータの読み取りと変更を確実に行えるようにするために使用される方法です。データがロックされた後、他のトランザクションは変更できないか、ブロックしてロックが解放されるまで待つことしかできないため、ロックの粒度はデータベースへのアクセスのパフォーマンスにある程度影響を与える可能性があります。

ロックの粒度の観点からは、ロックをテーブルロックと行ロックに分けることができます。

テーブルロック

テーブルロックはその名の通り、テーブルを直接ロックするものですが、MyISAMエンジンではテーブルロックのみです。

テーブルロックのロック方法は次のとおりです:

LOCK TABLE 表名 READ;--锁定后表只读
UNLOCK TABLE; --解锁复制代码

ログイン後にコピー

行ロック

行ロックは、その名前から、データの行をロックすることを意味しますが、実際の実装は行ロックこのアルゴリズムは比較的複雑で、特定のデータをロックするだけではない場合もあります。これについては後で詳しく説明します。

通常の考え方は、データ行をロックした後は、他のトランザクションはこのデータにアクセスできなくなります。次に、トランザクション A がデータにアクセスする場合、そのデータは読み取るために取り出すだけで、アクセスしたくないと想像します。トランザクション B もこのデータにアクセスしに来ますが、それを取り出して読みたいだけで、変更したくありません。この時点でブロックされると、少し面倒になります。パフォーマンスの無駄。したがって、このデータ読み取りシナリオを最適化するために、行ロックを 2 つの主要なタイプ (共有ロックと排他ロック) に分割します。

共有ロック

共有ロック、読み取りロック、S ロックとも呼ばれる共有ロックは、データの一部が S ロックで追加された後、他のトランザクションもデータを読み取ることができることを意味します. ロックを共有します。
次のステートメントを通じて共有ロックを追加できます:

select * from test where id=1 LOCK IN SHARE MODE;复制代码

ログイン後にコピー

ロック後、ロックされたトランザクションが終了する (コミットまたはロールバック) まで、ロックは解放されます。

排他ロック

排他ロック、排他ロック、書き込みロック、X ロックとも呼ばれます。つまり、X ロックがデータに追加された後、このデータにアクセスしたい他のトランザクションはブロックしてロックが解放されるのを待つことしかできず、これは排他的です。

挿入、更新、削除などのデータを変更すると、MySQL は自動的に排他ロックを追加します。同様に、次の SQL ステートメントを使用して排他ロックを手動で追加できます:

select * from test where id=1 for update;复制代码

ログイン後にコピー

InnoDB エンジンでは、行ロックとテーブルロックの共存が許可されています。

しかし、問題が発生します。トランザクション A がテーブル t の 1 行のデータをロックし、トランザクション B がその時点でテーブル t をロックしたい場合、このときどうすればよいでしょうか?トランザクション B は、テーブル t に行ロックがあるかどうかをどのようにして知るのでしょうか? テーブル全体のトラバーサルが使用される場合、テーブル内のデータが大きい場合、ロックに半日かかるため、MySQL は 意図ロック# # を導入します。＃。

インテンションロック

インテンションロックはテーブルロックであり、インテンション共有ロックとインテンション排他ロックの 2 種類に分けられ、それぞれ IS ロック、IX ロックと呼ばれます。

インテンションロックは MySQL 自体によって維持され、ユーザーが手動でインテンションを追加することはできません。

インテンションロックには 2 つの主要なロックルールがあります。

データ行に X ロックを追加する必要がある場合、MySQL はまずテーブルに IX ロックを追加します。

この場合、上記の問題は簡単に解決されます。テーブルをロックする必要がある場合、テーブルに対応するインテンションロックがあるかどうかを確認するだけで済みます。テーブルの表面全体。

各種ロックの互換性

各種ロックの互換性は以下の図の通りですので、公式サイトをご覧ください:

相互排除相互排除#相互排除共有S共有共有

#xxx

#相互排除

#IX

##共有

# #競合

#相互排除

##相互排除

相互排除

锁到底锁的是什么

建立以下两张表，并初始化5条数据，注意test表有2个索引而test2没有索引：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NAME_INDEX` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test VALUE(1,'张1');
INSERT INTO test VALUE(5,'张5');
INSERT INTO test VALUE(8,'张8');
INSERT INTO test VALUE(10,'张10');
INSERT INTO test VALUE(20,'张20');

CREATE TABLE `test2` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test2 VALUE(1,'张1');
INSERT INTO test2 VALUE(5,'张5');
INSERT INTO test2 VALUE(8,'张8');
INSERT INTO test2 VALUE(10,'张10');
INSERT INTO test2 VALUE(20,'张20');复制代码

ログイン後にコピー

举例猜测

在行锁中，假如我们对一行记录加锁，那么到底是把什么东西锁住了，我们来看下面两个例子：
举例1（操作test表）：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功
COMMIT; （释放锁）
	SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功

举例2（操作test2表）：

事务A	事务B
BEGIN;
SELECT * FROM test2 WHERE id=1 FOR UPDATE;
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功

从上面两个例子我们可以发现，test表好像确实是锁住了id=1这一行的记录，而test2表好像不仅仅是锁住了id=1这一行记录，实际上经过尝试我们就知道，test2表是被锁表了，所以其实MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表。

接下来再看一个场景：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

这个例子中我们是把name索引锁住了，然后我们在事务B中通过主键索引只查id，这样就用到name索引了，但是最后发现也被阻塞了。所以我们又可以得出下面的结论，MySQL索引不但锁住了辅助索引，还会把辅助索引对应的主键索引一起锁住。

到这里，可能有人会有怀疑，那就是我把辅助索引锁住了，但是假如加锁的时候，只用到了覆盖索引，然后我再去查主键会怎么样呢？

接下来让我们再验证一下：

事务A	事务B
BEGIN;
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

補助インデックスロックのみが使用されている場合でも、MySQL は引き続き主キーインデックスをロックし、主キーインデックスの B ツリーリーフノードにデータ全体が格納されるため、クエリされたフィールドはすべてロックされることがわかります。

この時点で、ロックが何であるかについて明確に結論を導き出すことができます:

結論

InnoDB エンジンでは、ロックされているのはインデックスです:

テーブルにインデックスがない場合、MySQL はテーブルをロックします (実際には、非表示カラム ROWID の主キーインデックスがロックされます)
補助インデックスをロックすると、補助インデックスもロックされます。インデックスは以下に対応します。主キーインデックスもロックされます。
主キーインデックスはロックされます。これは、実際にはレコード全体がロックされることを意味します (主キーインデックスのリーフノードにデータ全体が格納されます)

行ロックのアルゴリズム

前回の記事でトランザクションを紹介したときに、MySQL はロックによってファントム読み取りを防止すると述べましたが、行ロックがレコードの行をロックするだけの場合、ファントム読み取りは防止できないようです。したがって、レコードをロックする行ロックはケースの 1 つにすぎません。実際には、レコードロック、ギャップロック、ネクストキーロックの 3 つの行ロックアルゴリズムがあります。ファントム読み取りを防止できる理由は、まさにキーです。ロックはそうなります。

レコードロック

レコードロックは上で紹介されています。クエリがレコードにヒットすると、InnoDB はレコードロックを使用して、レコードのヒットした行をロックします。

ギャップロック

クエリがレコードにヒットしない場合、InnoDB はこの時点でギャップロックを追加します。

#トランザクション Aトランザクション B##BEGIN;#SELECT * FROM test WHERE id=1 FOR UPDATE;##INSERT INTO テスト値 (3 ,'Zhang 3');ブロック中#SELECT * FROM test WHERE id=2 FOR UPDATE;ロック成功#COMMIT;ギャップロックとギャップロックの間に競合はありません。つまり、トランザクション A はギャップロックを追加し、トランザクション B はギャップロックを追加します。同じギャップに追加されます。 (ギャップロックが使用される理由は、データヒットがない場合であるため、読み取りをブロックする必要がなく、他のトランザクションが同じギャップをロックするのをブロックする必要もありません)




INSERT INTO テスト値 (2,'Zhang 2'); ブロッキング

	(ロックを解除)
#From上記の例では、次のように結論付けることができます:

ギャップロック主に挿入操作をブロックします。

ギャップはどのように決定されますか。
(-∞,1),(1,5),(5,8),(8,10),(10,20),(20, ∞)主キーが int 型でない場合は、ASCII コードに変換してギャップを判定します。

ネクストキーロック

ネクストキーロックは、レコードロックとギャップロックを組み合わせたものです。範囲クエリを実行し、1 つ以上のレコードにヒットするだけでなくギャップも含む場合、一時的なキーロックが使用されます。キーレスロックは、InnoDB の行ロックのデフォルトのアルゴリズムです。

これは RR 分離レベルのみに適用されることに注意してください。RC 分離レベルの場合、外部キー制約と一意性制約に加えて、ギャップロックが追加されます。ギャップロックがなければ、当然、一時的なキーロックがないため、RC レベルで追加された行ロックはすべてレコードロックです。レコードがヒットしない場合、ロックはロックされません。したがって、RC レベルではファントム読み取りの問題は解決されません。

一時キーロックは、次の 2 つの条件下でギャップロックまたはレコードロックにダウングレードされます。

クエリがタスクレコードを欠落すると、クエリはタスクレコードにダウングレードされます。ギャップロック。

主キーまたは一意のインデックスを使用してレコードがヒットすると、そのレコードはレコードロックにダウングレードされます。

#トランザクション A

トランザクション B

BEGIN;SELECT * FROM test WHERE id>=2 AND id##INSERT INTO テスト値 (2,'Zhang 2') ;ブロッキング##INSERT INTO test VALUE (6,'Zhang 6');ブロッキングブロッキングブロッキング挿入が成功しました(ロックを解放します)

##INSERT INTO test VALUE (8,'Zhang 8');

##SELECT * FROM test WHERE id=8 FOR UPDATE;

INSERT INTO test VALUE (9,'Zhang 9');

##COMMIT;

## ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

上面这个例子，事务A加的锁跨越了(1,5)和(5,8)两个间隙，且同时命中了5，然后我们发现我们对id=8这条数据进行操作也阻塞了，但是9这条记录插入成功了。

临键锁加锁规则

临键锁的划分是按照左开右闭的区间来划分的，也就是我们可以把test表中的记录划分出如下区间：(-∞,1],(1,5],(5,8],(8,10],(10,20],(20,+∞)。

那么临键锁到底锁住了哪些范围呢？

**临键锁中锁住的是最后一个命中记录的 key 和其下一个左开右闭的区间**

那么上面的例子中其实锁住了(1,5]和(5,8]这两个区间。

临键锁为何能解决幻读问题

临键锁为什么要锁住命中记录的下一个左开右闭的区间？答案就是为了解决幻读。

我们想一想上面的查询范围id>=2且id

当然，其实如果我们执行的查询刚好是id>=2且id

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务持有锁的时候，会阻止其他的事务获取锁，这个时候会造成阻塞等待，那么假如事务一直等待下去，就会一直占用CPU资源，所以，锁等待会有一个超时时间，在InnoDB引擎中，可以通过参数：innodb_lock_wait_timeout查询：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';复制代码

ログイン後にコピー

默认超时时间是50s，超时后会自动释放锁回滚事务。但是我们想一下，假如事务A在等待事务B释放锁，而事务B又在等待事务A释放锁，这时候就会产生一个等待环路了，而这种情况是无论等待多久都不可能会获取锁成功的，所以是没有必要去等50s的，这种形成等待环路的现象又叫做死锁。

死锁(Dead Lock)

什么是死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=10 FOR UPDATE;
	BEGIN;
	SELECT * FROM test WHERE id=20 FOR UPDATE;
SELECT * FROM test WHERE id=20 FOR UPDATE;
	SELECT * FROM test WHERE id=10 FOR UPDATE;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查询出结果

デッドロックが発生すると、トランザクションをロールバックする前に 50 秒のタイムアウトを無意味に待つのではなく、即座にロールバックされることがわかります。では、MySQL はどのようにしてデッドロックが発生したことを認識するのでしょうか。 ? はい、デッドロックの発生を検出するにはどうすればよいですか?

デッドロック検出

現在、ほとんどのデータベースはデッドロックの検出に待機グラフ (待機グラフ) メソッドを使用しています。InnoDB エンジンもこのメソッドを使用してデッドロックを検出します。データベースには 2 種類の情報が記録されます。

ロック情報リスト
トランザクション待機リスト
待機グラフアルゴリズムは、これら 2 つの情報に基づいてグラフを構築します。図にループがある場合、デッドロックがあることがわかります:
以下の図では、t1 と t2 の間にループがあり、t1 と t2 のトランザクション間にデッドロックがあることがわかります。

デッドロックの回避

where 条件文のないクエリを避けるクエリを実行するときは、可能な限りインデックスクエリを使用してください
可能であれば、同等のクエリを使用するようにしてください

ロック情報クエリ

InnoDB は、information_schema の下に 3 つのテーブルを提供しますトランザクションおよびロック関連の問題のクエリとトラブルシューティングを行うためのライブラリ。

INNODB_TRX

InnoDB で現在実行されている各トランザクションに関する情報 (トランザクションがロックを待機しているかどうか、トランザクションがいつ開始されたか、トランザクションが実行している SQL ステートメント (存在する場合) など) を記録します。）。

#列名意味##trx_idtrx_statetrx_startedtrx_requested_lock_idtrx_wait_startedtrx_weighttrx_mysql_thread_idtrx_querytrx_operation_state#trx_tables_in_use現在のトランザクションで実行された SQL ステートメントで使用されているテーブルの数trx_tables_lockedロックされたテーブルの数 (行ロックが使用されているため、テーブルがロックされているように示されていますが、1 行または数行のみがロックされている可能性があるため、他の行はロックされている可能性があります)他のトランザクションによってアクセスされる可能性があります)trx_lock_structs現在のトランザクションによって保持されているロックの数trx_lock_memory_bytes現在のトランザクションのインデックス構造はメモリ内にあります。サイズtrx_rows_locked現在のトランザクションでロックされている行のおおよその数。削除対象としてマークされており、物理的に存在しますが、現在のトランザクションには表示されませんtrx_rows_modified現在のトランザクションによって変更または挿入された行の数trx_concurrency_tickets同時実行数とは、現在のトランザクションが終了するまでにまだ実行できる同時実行の数を指します。システム変数 innodb_concurrency_tickets現在のトランザクション分離レベル現在のトランザクションに対して一意制約がオープンされているかクローズされているか: 0-いいえ 1-はい##trx_foreign_key_checks現在のトランザクションかどうか外部キー制約をオンまたはオフにします: 0-いいえ 1-はいtrx_last_foreign_key_error最後の外部キーのエラーメッセージ (そうでない場合は空になります)trx_adaptive_hash_latched適応ハッシュかどうかインデックスは現在のトランザクションによってロックされています。分割適応ハッシュインデックス検索システムを使用する場合、単一のトランザクションでは適応ハッシュインデックス全体がロックされません。アダプティブハッシュインデックスパーティショニングは、innodb_adaptive_hash_index_parts によって制御されます。これはデフォルトで 8 に設定されます。 trx_adaptive_hash_timeout アダプティブハッシュインデックスの検索ラッチをすぐに破棄するか、MySQL からの呼び出し全体で検索ラッチを保持するか。適応型ハッシュインデックスの競合がない場合、この値はゼロのままで、ステートメントは完了するまでラッチを保持します。競合中、そのカウントはゼロに減らされ、ステートメントは各行検索の直後にラッチを解放します。アダプティブハッシュインデックス検索システムがパーティション化されている場合 (innodb_adaptive_hash_index_parts によって制御される)、この値は 0 のままです。 trx_is_read_only現在のトランザクションが読み取り専用かどうか: 0-いいえ 1-はいtrx_autocommit_non_locking 値 1 は、これが共有モデルの更新とロックを含まないステートメントであり、自動コミットが有効な状態で実行される唯一のステートメントであることを意味します。この列と TRX_IS_READ_ONLY が両方とも 1 の場合、InnoDB はトランザクションを最適化します。テーブルデータを変更するトランザクションに関連するオーバーヘッドを削減するため。 #INNODB_LOCKSトランザクションが要求したが取得できなかった各ロック、およびロックを保持していたが別のトランザクションをブロックしていた各トランザクションに関する情報を記録します。情報。


InnoDD エンジン内で一意トランザクションの ID
トランザクションステータス: RUNNING、LOCK WAIT、ROLLING BACK、COMMITTING
トランザクションの開始時刻
待機中のトランザクションのロック ID。trx_state が LOCK WAIT でない場合は null
トランザクションの開始を待機する時間
トランザクションの重みは行を反映します。トランザクション番号によって変更およびロックされている。デッドロックが発生すると、InnoDB はロールバックする最小値を持つトランザクションを選択します。
MySQL のスレッド ID は、 SHOW PROCESSLIST
トランザクションによって実行される SQL ステートメントを通じてクエリされる
トランザクションの現在の操作状態 (そうでない場合は NULL)






## を通じて設定できます。	#trx_isolation_level
	trx_unique_checks

#列名

意味

lock_trx_id#lock_mode lock_typelock_tablelock_indexlock_spacelock_pagelock_rec## に対してロックされているページの数locks #トランザクションロック行の数、テーブルロックは NULLlock_dataトランザクションロック、テーブルロックの主キー値は NULL

INNODB_LOCK_WAITS

ロック待機情報を記録します。ブロックされた各 InnoDB トランザクションには、リクエストしたロックとリクエストをブロックしていたロックを表す 1 つ以上の行が含まれます。

##lock_id	ロックID (現在、LOCK_ID には TRX_ID が含まれていますが、LOCK_ID のデータ形式はいつでも変更される可能性があります。LOCK_ID 値を解析するアプリケーションを作成しないでください)
前の図トランザクション IDテーブルの
ロックモード: S、X、IS、IX、GAP、AUTO_INC、UNKNOWN
ロックタイプはテーブルロックですか、それとも行ロックですか?
ロックされたテーブル
ロックされたインデックス、テーブルロックは NULLです
ロックレコードのスペース ID、テーブルロックはNULL NULL
トランザクションおよびテーブルの NULL

#列名意味##lock_idrequesting_trx_idrequested_lock_idblocking_trx_idblocking_lock_id


ロックID (現在、LOCK_ID には TRX_ID が含まれていますが、LOCK_ID のデータ形式はいつでも変更される可能性があります。LOCK_ID 値を解析するアプリケーションを作成しないでください)
ロックリソースのリクエストトランザクションID
リクエストされたロックの ID
ブロックされたトランザクション ID
ブロッキングロックの ID