|
共有
|
锁到底锁的是什么
建立以下两张表，并初始化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 テスト値 (2,'Zhang 2');
ブロッキング |
|
##INSERT INTO テスト値 (3 ,'Zhang 3');ブロック中
|
| #SELECT * FROM test WHERE id=2 FOR UPDATE; ロック成功
| #COMMIT;(ロックを解除)
|
|
#From上記の例では、次のように結論付けることができます: |
| ギャップ ロックとギャップ ロックの間に競合はありません。つまり、トランザクション A はギャップ ロックを追加し、トランザクション B はギャップ ロックを追加します。同じギャップに追加されます。 (ギャップ ロックが使用される理由は、データ ヒットがない場合であるため、読み取りをブロックする必要がなく、他のトランザクションが同じギャップをロックするのをブロックする必要もありません)
ギャップ ロック 主に挿入操作をブロックします。
- ギャップはどのように決定されますか。
テスト テーブルには 5 つのレコードがあり、主キーの値があります。は、1、5、8、10、20です。この場合、次の 6 つのギャップが存在します: - (-∞,1),(1,5),(5,8),(8,10),(10,20),(20, ∞)主キーが int 型でない場合は、ASCII コードに変換してギャップを判定します。
ネクストキーロックネクストキーロックは、レコードロックとギャップロックを組み合わせたものです。範囲クエリを実行し、1 つ以上のレコードにヒットするだけでなくギャップも含む場合、一時的なキー ロックが使用されます。キーレス ロックは、InnoDB の行ロックのデフォルトのアルゴリズムです。
これは RR 分離レベルのみに適用されることに注意してください。RC 分離レベルの場合、外部キー制約と一意性制約に加えて、ギャップ ロックが追加されます。ギャップ ロックがなければ、当然、一時的なキー ロックがないため、RC レベルで追加された行ロックはすべてレコード ロックです。レコードがヒットしない場合、ロックはロックされません。したがって、RC レベルではファントム読み取りの問題は解決されません。 一時キー ロックは、次の 2 つの条件下でギャップ ロックまたはレコード ロックにダウングレードされます。
クエリがタスク レコードを欠落すると、クエリはタスク レコードにダウングレードされます。ギャップロック。
主キーまたは一意のインデックスを使用してレコードがヒットすると、そのレコードはレコード ロックにダウングレードされます。
#トランザクション A
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_TRXInnoDB で現在実行されている各トランザクションに関する情報 (トランザクションがロックを待機しているかどうか、トランザクションがいつ開始されたか、トランザクションが実行している SQL ステートメント (存在する場合) など) を記録します。 ）。
#列名 | 意味 |
##trx_id
| InnoDD エンジン内で一意トランザクションの ID |
|
trx_state
| トランザクション ステータス: RUNNING、LOCK WAIT、ROLLING BACK、COMMITTING |
|
trx_started
| トランザクションの開始時刻 |
|
trx_requested_lock_id
| 待機中のトランザクションのロック ID。trx_state が LOCK WAIT でない場合は null | |
trx_wait_started
| トランザクションの開始を待機する時間 |
|
trx_weight
| トランザクションの重みは行を反映します。トランザクション番号によって変更およびロックされている。デッドロックが発生すると、InnoDB はロールバックする最小値を持つトランザクションを選択します。 |
|
trx_mysql_thread_id
| MySQL のスレッド ID は、 SHOW PROCESSLIST |
|
trx_query
| トランザクションによって実行される SQL ステートメントを通じてクエリされる |
|
trx_operation_state
| トランザクションの現在の操作状態 (そうでない場合は NULL) |
| #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| ## を通じて設定できます。 |
#trx_isolation_level |
現在のトランザクション分離レベル
|
trx_unique_checks |
現在のトランザクションに対して一意制約がオープンされているかクローズされているか: 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 | トランザクションが要求したが取得できなかった各ロック、およびロックを保持していたが別のトランザクションをブロックしていた各トランザクションに関する情報を記録します。情報。
#列名
意味
| ##lock_id |
ロックID (現在、LOCK_ID には TRX_ID が含まれていますが、LOCK_ID のデータ形式はいつでも変更される可能性があります。LOCK_ID 値を解析するアプリケーションを作成しないでください) |
lock_trx_id
| 前の図 トランザクション IDテーブルの |
|
#lock_mode
| ロック モード: S、X、IS、IX、GAP、AUTO_INC、UNKNOWN |
|
lock_type
| ロック タイプはテーブル ロックですか、それとも行ロックですか? |
|
lock_table
| ロックされたテーブル |
| lock_index
| ロックされたインデックス、テーブル ロックは NULLです |
|
lock_space
| ロック レコードのスペース ID、テーブル ロックはNULL NULL |
|
lock_page
| トランザクションおよびテーブルの NULL |
|
lock_rec## に対してロックされているページの数locks #トランザクション ロック行の数、テーブル ロックは NULL |
| lock_data
トランザクション ロック、テーブル ロックの主キー値は NULL | |
INNODB_LOCK_WAITS
ロック待機情報を記録します。ブロックされた各 InnoDB トランザクションには、リクエストしたロックとリクエストをブロックしていたロックを表す 1 つ以上の行が含まれます。
#列名 | 意味 |
##lock_id
| ロックID (現在、LOCK_ID には TRX_ID が含まれていますが、LOCK_ID のデータ形式はいつでも変更される可能性があります。LOCK_ID 値を解析するアプリケーションを作成しないでください) |
|
requesting_trx_id
| ロック リソースのリクエスト トランザクションID |
|
requested_lock_id
| リクエストされたロックの ID |
|
blocking_trx_id
| ブロックされたトランザクション ID | |
blocking_lock_id
| ブロッキング ロックの ID |
|
その他の関連する無料学習 推奨: mysql チュートリアル #(ビデオ)
|
|
