mysqlのロック機構の原理を詳しく解説(2)

Lock は、コンピューターが複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするためのメカニズムです。データベースでは、コンピューティング リソース (CPU、RAM、I/O など) をめぐる従来の競争に加え、データは多くのユーザーによって共有されるリソースでもあります。データへの同時アクセスの一貫性と有効性をどのように確保するかは、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースへの同時アクセスのパフォーマンスに影響を与える重要な要素です。この観点から見ると、ロックはデータベースにとって特に重要かつ複雑です。この章では、MySQL ロック メカニズムの特性、一般的なロックの問題、および MySQL ロックの問題を解決するためのいくつかの方法や提案に焦点を当てます。
Mysql は、行ロック、テーブル ロック、読み取りロック、書き込みロックなどの多くのロック メカニズムを使用しており、これらはすべて操作前にロックされます。これらのロックは総称して悲観的ロックと呼ばれます。

InnoDB ロック

InnoDB と MyISAM の最大の違いは 2 点です。

第一に、トランザクション (TRANSACTION) をサポートしています。 );

2 つ目は、行レベルのロックの使用です。行レベルのロックとテーブルレベルのロックには多くの違いがあるほか、トランザクションの導入によりいくつかの新しい問題も生じます。

1. トランザクション (トランザクション) とその ACID 属性
トランザクションは、一連の SQL ステートメントで構成される論理的な処理単位であり、トランザクションには 4 つの属性があり、通常はこれらの属性と呼ばれますトランザクションの ACID 属性。

1. アトミック性: トランザクションはアトミックな操作単位であり、データに対するすべての変更は実行されるか、まったく実行されないかのどちらかです。

2. 一貫性: データはトランザクションの開始時と完了時に一貫性を維持する必要があります。これは、整合性を維持するために、関連するすべてのデータ ルールをトランザクションの変更に適用する必要があることを意味し、トランザクションの終了時には、すべての内部データ構造 (B ツリー インデックスや二重リンク リストなど) も正しくなければなりません。

3. 分離: データベース システムは、外部の同時操作の影響を受けない「独立した」環境でトランザクションが実行されることを保証する、特定の分離メカニズムを提供します。つまり、トランザクション中の中間状態は外部からは見えず、その逆も同様です。

4. 耐久性: トランザクション完了後のデータへの変更は永続的であり、システム障害が発生した場合でも維持できます。

2. 同時トランザクションによって引き起こされる問題
同時トランザクション処理は、逐次処理と比較して、データベース リソースの使用率を大幅に向上させ、データベース システムのトランザクション スループットを向上させることができます。より多くのユーザーをサポートします。ただし、トランザクションを同時に処理すると、主に次のような問題が発生します。

1. 更新の喪失: 2 つ以上のトランザクションが同じ行を選択し、最初に選択された値に基づいて行を更新すると、各トランザクションは他のトランザクションの存在を知らないため、更新の喪失の問題が発生します。 - 最後の更新は、他のトランザクションによって行われた更新を上書きします。たとえば、2 人の編集者が同じ文書の電子コピーを作成するとします。各編集者は独自にコピーを変更し、変更されたコピーを保存して元の文書を上書きします。最後に自分の変更のコピーを保存した編集者は、別の編集者によって加えられた変更を上書きします。この問題は、あるエディターがトランザクションを完了してコミットするまで、あるエディターが同じファイルにアクセスできない場合に回避できます。

2. ダーティ リード: トランザクションがレコードを変更しています。トランザクションがコミットされる前は、このレコードのデータは不整合な状態にあります。この時点で、別のトランザクションも読み取りに来ます。制御なしで取得された場合、2 番目のトランザクションは「ダーティ」データを読み取り、それに応じてさらなる処理を実行します。その結果、コミットされていないデータ依存関係が生じます。この現象は明確に「ダーティ リーディング」と呼ばれています。

3. 反復不可能な読み取り: 一部のデータの読み取り時にトランザクションが変更されたか、一部のレコードが削除されました。この現象は「非反復読み取り」と呼ばれます。

4. ファントム リード: トランザクションは、同じクエリ条件に従って以前に取得したデータを再読み込みしますが、他のトランザクションがそのクエリ条件を満たす新しいデータを挿入していることがわかります。この現象は「ファントム リード」と呼ばれます。 」。

3. トランザクション分離レベル
同時トランザクション処理によって引き起こされる問題のうち、「更新損失」は通常完全に回避する必要があります。ただし、更新消失の防止はデータベーストランザクションコントローラだけで解決できるものではなく、アプリケーション側で更新対象のデータに必要なロックを付与する必要があるため、更新消失の防止はアプリケーション側で行う必要があります。

「ダーティ リード」、「非反復読み取り」、および「ファントム リード」は、実際にはデータベースの読み取り一貫性の問題であり、データベースが特定のトランザクション分離メカニズムを提供することで解決する必要があります。データベースがトランザクション分離を実装する方法は、基本的に次の 2 種類に分類できます。

1. 1 つは、他のトランザクションがデータを変更できないように、データを読み取る前にデータをロックすることです。

2. もう 1 つは、ロックを追加せずに特定のメカニズムを通じてデータ要求時点の一貫したデータ スナップショット (スナップショット) を生成し、このスナップショットを使用して特定のレベル (ステートメント レベルまたはトランザクション レベル) の一貫したデータ スナップショットを提供することです。読んで、選んでください。ユーザーの観点から見ると、データベースは同じデータの複数のバージョンを提供できるように見えるため、この技術はデータ マルチバージョン同時実行制御 (MVCC または略して MCC) と呼ばれ、マルチバージョン データベースとも呼ばれます。

MVCC 同時実行制御では、読み取り操作はスナップショット読み取りと現在の読み取りの 2 つのカテゴリに分類できます。スナップショット読み取りでは、ロックせずに、レコードの表示されているバージョン (履歴バージョンである可能性があります) を読み取ります。現在の読み取りはレコードの最新バージョンを読み取ります。現在の読み取りによって返されるレコードは、他のトランザクションがこのレコードを同時に変更しないようにロックされます。
MVCC 同時実行制御をサポートするシステムでは、どの読み取り操作がスナップショット読み取りになりますか?現在どの操作が読み取られていますか? MySQL InnoDB を例に挙げます。

スナップショット読み取り: 単純な選択操作。スナップショット読み取りであり、ロックは必要ありません。 (もちろん例外はあります)

select * from table where ?;

現在の読み取り: 特殊な読み取り操作、挿入/更新/削除操作は現在の読み取りに属しており、ロックする必要があります。
次のステートメントはすべて現在の読み取りであり、レコードの最新バージョンを読み取ります。さらに、読み取り後、他の同時トランザクションが現在のレコードを変更したり、読み取りレコードをロックしたりできないようにすることも必要です。このうち、読み取りレコードに S ロック (共有ロック) を追加する最初のステートメントを除き、他のすべての操作は X ロック (排他的ロック) を追加します。

データベースのトランザクション分離が厳格であればあるほど、同時発生する副作用は小さくなりますが、支払う代償は大きくなります。これは、トランザクション分離では本質的にトランザクションがある程度まで「シリアル化」されるためであり、これは明らかに「」と矛盾します。同時実行性」は矛盾しています。同時に、アプリケーションが異なれば、読み取りの一貫性とトランザクションの分離に対する要件も異なります。たとえば、多くのアプリケーションは「反復不可能な読み取り」や「ファントム読み取り」に敏感ではなく、同時にデータにアクセスする能力をより重視する可能性があります。

「分離」と「同時実行性」の間の矛盾を解決するために、ISO/ANSI SQL92 では 4 つのトランザクション分離レベルが定義されています。各レベルは分離の度合いが異なり、さまざまな副作用が許容されます。アプリケーションは以下に基づくことができます。独自のビジネス ロジックでは、さまざまな分離レベルを選択することで、「分離」と「同時実行」の間の矛盾のバランスを取る必要があります。次の表は、これら 4 つの分離レベルの特性をわかりやすくまとめたものです。

InonoD 行ロック競合の取得

InnoDB_row_lock ステータス変数 Row をチェックすることでシステムを分析できます。ロック競合:

mysql> show status like 'innodb_row_lock%';

InnoDB_row_lock_waits や InnoDB_row_lock_time_avg の値が比較的高いなど、ロック競合が深刻であることがわかった場合は、InnoDB を設定することもできます。ロック競合が発生しているテーブルやデータ行などを監視し、ロック競合の原因を分析します。

InnoDB の行ロック モードとロック方法

InnoDB は、次の 2 種類の行ロックを実装します。

共有ロック: 読み取りロックとも呼ばれます。 1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが同じデータ セットに対して排他ロックを取得できないようにします。トランザクション T がデータ オブジェクト A に S ロックを追加した場合、トランザクション T は A を読み取ることはできますが、A を変更することはできません。他のトランザクションは A に S ロックを追加することのみできますが、T が A の S ロックを解放するまで X ロックを追加することはできません。これにより、他のトランザクションは A を読み取ることができますが、T が A の S ロックを解放するまでは A を変更できないことが保証されます。

排他ロック (X): 書き込みロックとも呼ばれます。排他的ロックを取得するトランザクションがデータを更新できるようにし、他のトランザクションが同じデータ セットに対する共有読み取りロックや排他的書き込みロックを取得できないようにします。トランザクション T がデータ オブジェクト A に X ロックを追加すると、トランザクション T は A の読み取りまたは変更を行うことができます。T が A のロックを解放するまで、他のトランザクションは A にそれ以上ロックを追加できません。
共有ロックについては誰もがよく理解しているかもしれません。つまり、複数のトランザクションはデータの読み取りのみが可能で、データの変更はできません。

对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表: 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 
事务可以通过以下语句显式给记录集加共享锁或排他锁：

1、共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

2、排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示： 
创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;
1
Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 
Warnings: 0
mysql> insert into tab_with_index  values(1,'4');
1
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;

InnoDB存储引擎使用相同索引键的阻塞例子 ：

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);
1Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 
Warnings: 0

InnoDB存储引擎的表使用不同索引的阻塞例子 ：

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 
比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

mysql> explain select * from tab_with_index where name = 1 \G
mysql> explain select * from tab_with_index where name = '1' \G

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。 
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！下面这个例子假设emp表中只有101条记录，其empid的值分别是1,2,……,100,101。 
InnoDB存储引擎的间隙锁阻塞例子 

小结

本文重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。

MyISAM テーブル ロックに関しては、主に次の点が説明されています。
(1) 共有読み取りロック (S) は互換性がありますが、共有読み取りロック (S) と排他的書き込みロック (X) は互換性があり、排他的ロックは互換性があります。書き込みロック (X) は相互に排他的です。つまり、読み取りと書き込みはシリアルです。
(2) 特定の条件下では、MyISAM ではクエリと挿入を同時に実行できます。これを使用して、アプリケーションの同じテーブルに対するクエリと挿入のロック競合の問題を解決できます。
(3) MyISAM のデフォルトのロック スケジューリング メカニズムは書き込み優先ですが、必ずしもすべてのアプリケーションに適しているわけではありません。ユーザーは、LOW_PRIORITY_UPDATES パラメータを設定するか、INSERT、UPDATE、およびDELETE ステートメントを使用します。
(4) テーブルロックのロック粒度が大きく、読み取りと書き込みがシリアルであるため、更新操作が多い場合、MyISAM テーブルで重大なロック待機が発生する可能性があります。ロックを減らすために InnoDB テーブルの使用を検討できます。 。 対立。

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

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

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

関連コンテンツをさらに知りたい場合は、PHP 中国語 Web サイトにアクセスしてください: mysql ビデオ チュートリアル

以上がmysqlのロック機構の原理を詳しく解説(2)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。