MySQL の学習では InnoDB のロック状況について説明します

この記事では MySQL について説明し、InnoDB のロックの状況を紹介します。一定の参考値があるので、困っている友達が参考になれば幸いです。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

1. ロックの基本入門

MySQL のロック機構は他のデータベースと比べて比較的シンプルであるのが特徴です。 ストレージ エンジンが異なると、異なるロック メカニズムがサポートされます。たとえば、MyISAM および MEMORY ストレージ エンジンはテーブル レベルのロックを使用します。InnoDB ストレージ エンジンは行レベルのロック (行レベルのロック) とテーブル レベルのロックの両方をサポートしますが、デフォルトでは行レベルのロックが使用されます。

テーブル レベル ロック: 低いオーバーヘッド、高速なロック、デッドロックなし、大きなロック粒度、ロック競合の可能性が最も高く、同時実行性が最も低い。

行レベルのロック: オーバーヘッドが高く、ロックが遅い; デッドロックが発生します; ロックの粒度は最も小さく、ロック競合の可能性は最も低く、同時実行性は最も高くなります。

行レベルのロック タイプ:

レコード ロック: レコード ロック (単一レコードをロック)

レコード ロックインデックス レコードのみをロックします。InnoDB ストレージ テーブルの作成時にインデックスが存在しない場合、このロックは、以下に示すように、暗黙的な主キーを使用してロックします。

#ギャップ ロック: レコード自体を除く範囲をロックします (データの前の GAP のみをロックします)

下の図に示されているロックは GAP ロックです。他のトランザクションは、インデックス列 8 の前のギャップ、つまり間隔 (3, 8) に新しいレコードを挿入することはできません。 ギャップ ロックの役割は、ファントム レコードの挿入を防ぐことだけです

Next-Key Lock: 同時にレコードをロックしますそして前のギャップを記録します。つまり、Next-Key Lock = Record Lock Gap Lockです。

#2. ロックの分類

共有ロック (S ロックと呼ばれ、行ロックに属します)

排他的ロック (略して X ロック、行ロックに属する)

#インテンション共有ロック (略して IS ロック、テーブル ロックに属する)

インテンション排他ロック インテンション排他ロック(略して IX ロック、テーブル ロックに属します)

AUTO-INC ロック(テーブル ロックに属します)

以下は各タイプのロックの詳細な紹介です。最初にロックを構築しましょう。 Innodb テーブル、SQL は次のとおりです

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

共有ロック

共有ロックとは、複数のトランザクションが同じデータのロックを共有でき、すべてのトランザクションがデータベースにアクセスできることを意味しますただし、読み取りのみ可能ですが変更はできません;

トランザクション A:

共有モードの tab_with_index ロックから * を選択;

トランザクション B:

select * from tab_with_index where id =1; // データはクエリ可能

update tab_with_index set name = 'aa' where id = 1 ;

注意: ここでの変更ステートメントはブロックされ、トランザクション A が送信されるまで操作は成功しません。

排他ロック

排他ロックは他のロックと共存できません。トランザクションがデータ行の排他ロックを取得すると、他のトランザクションは同じロックを取得できません。行の場合ロックを使用すると、現在排他ロックを取得しているトランザクションのみがデータを変更できます。 (削除、更新、作成はデフォルトで排他ロックです)

トランザクション A:

select * from tab_with_index where id =1 for update;

トランザクション B:

select * from tab_with_index where id =1; //結果を取得できます

select * from tab_with_index where id =1 for update ; // ブロッキング

select * from tab_with_index where id = 1 lock for share mode; // ブロッキング

注: トランザクション B の SQL は両方ともブロックされています。つまり、共有ロックも排他ロックも取得できず、トランザクション A が送信されるまで操作は成功しません。

インテンション共有ロックとインテンション排他ロック

インテンション共有ロック: トランザクションがデータ行に共有ロックを追加する準備をしていることを示します。データ行は共有ロックを追加しています。テーブルの IS ロックは事前に取得する必要があります。

意図的な排他ロック: トランザクションがデータ行に排他ロックを追加する準備をしていることを示します。つまり、データ行に排他ロックを追加する前に、テーブルの IX ロックを取得する必要があります。初め。

IS ロックと IX ロックはテーブル レベルのロックです。これらは、テーブル レベルの S ロックを追加するときに、テーブル内のレコードがロックされているかどうかを迅速に判断するためにのみ提案されており、トラバーサルを使用してロックされているかどうかを確認します。つまり、IS ロックと IX ロックは互換性があり、IX ロックと IX ロックは互換性があります。 「MySQL の実行方法」

#自動インクリメント ロック

自動インクリメント カラム用の特別なテーブル レベルのロック。

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失

三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。  排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

概要

InnoDB テーブルについて、この記事では主に次の内容について説明します: (1) InnoDB の行ロックはインデックスに基づいています。データがインデックスを介してアクセスされていない場合、InnoDB テーブルはロックが使用されます。 (2) 分離レベルが異なると、InnoDB のロック メカニズムと一貫した読み取り戦略が異なります。

InnoDB のロック特性を理解した後、ユーザーは次のような設計と SQL の調整を通じてロックの競合とデッドロックを減らすことができます:

• より低い分離レベルを使用してみてください。インデックスを慎重に設計してください。インデックスを使用してデータにアクセスし、ロックをより正確にして、ロック競合の可能性を減らします。
• 適切なトランザクション サイズを選択すると、小規模なトランザクションの場合はロック競合の可能性が低くなります;
• レコード セットを明示的にロックする場合は、一度に十分なレベルのロックを要求するのが最善です。たとえば、データを変更する場合は、最初に共有ロックを適用するのではなく、排他ロックを直接適用し、変更時に排他ロックを要求することをお勧めします。これにより、デッドロックが発生しやすくなります。さまざまなプログラムがテーブルのグループにアクセスする場合、同じ順序で各テーブルにアクセスすることが合意されています。テーブルの場合、テーブル内の行はできる限り固定された順序でアクセスされる必要があります。これにより、デッドロックの可能性が大幅に減少します。
• 同時挿入に対するギャップ ロックの影響を避けるために、データにアクセスする際には等しい条件を使用するようにしてください。実際の必要性を超えるロック レベルを適用しないでください。必要な場合を除き、Lock をクエリするときに追加値を表示しないでください。
• 一部の特定のトランザクションでは、テーブル ロックを使用して処理速度を向上させたり、デッドロックの可能性を軽減したりできます。
• [関連する推奨事項:

mysql ビデオ チュートリアル

]

以上がMySQL の学習では InnoDB のロック状況について説明しますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。