mysqlデータベースのロックメカニズムの詳細な紹介

この記事では、MySQL データベースのロック メカニズムについて詳しく説明します。必要な方は参考にしていただければ幸いです。

悲観的ロックと楽観的ロック:
悲観的ロック: 名前が示すように、データを取得するたびに、他の人がデータを変更すると考えて、データを取得するたびにロックします。 、他の人が必要に応じてデータを取得できるようにするため、ロックを取得するまでブロックされます。このようなロック メカニズムの多くは、行ロック、テーブル ロック、読み取りロック、書き込みロックなど、従来のリレーショナル データベースで使用されており、操作前にすべてロックされます。

楽観的ロック: 名前が示すように、データを取得するたびに、他の人が変更しないと考えてロックしません。ただし、更新するときに、それを判断します。この期間中に他のユーザーがデータを更新した場合は、バージョン番号などのメカニズムを使用できます。オプティミスティック ロックはマルチ読み取りアプリケーション タイプに適しており、データベースが write_condition と同様のメカニズムを提供している場合、実際にはオプティミスティック ロックが提供されます。

テーブル レベル: エンジン MyISAM は、ロック期間中、テーブル全体を直接ロックします。他のプロセスはテーブルに書き込むことができません。書き込みロックがある場合、他のプロセスは読み取りを許可されません。テーブル レベルのロックは高速ですが、行レベルのロックは競合がほとんどありませんが、低速です。したがって、隣接するレコードのグループを一度にロックする妥協策がページ レベルで採用されました。

行レベル: エンジン INNODB は、指定されたレコードのみをロックするため、他のプロセスは同じテーブル内の他のレコードを引き続き操作できます。

上記の 3 種類のロックの特徴は、次のように大まかに要約できます:

1) テーブルレベルのロック: オーバーヘッドが低く、ロックが高速で、ロックの粒度が高く、ロックの競合の可能性が最も高くなります。同時並行性。

2) ページ ロック: コストとロック時間はテーブル ロックと行ロックの間であり、デッドロックが発生します。ロックの粒度はテーブル ロックと行ロックの間であり、同時実行性は平均的です。
3) 行レベルのロック: オーバーヘッドが高く、ロックが遅い。ロックの粒度が最も小さく、ロックの競合の可能性が最も低く、同時実行性が最も高い。


ロックの観点から見ると、テーブルレベルのロックは、インデックス条件に従って少量のデータのみが更新されるクエリベースのアプリケーション (WEB アプリケーションなど) に適しています。行レベルのロックは、一部のオンライン トランザクション処理 (OLTP) システムなど、インデックス条件や同時クエリに基づいて少量の異なるデータを多数同時に更新するアプリケーションに適しています。

MySQL テーブルレベルのロックには 2 つのモードがあります:

1. テーブル共有読み取りロック (テーブル読み取りロック)。 MyISAM テーブルで読み取り操作を実行する場合、同じテーブルに対する他のユーザーの読み取りリクエストはブロックされませんが、同じテーブルに対する書き込み操作はブロックされます。

2. MyISAM テーブルに対する書き込み操作は、同じテーブルに対する他のユーザーの読み取りおよび書き込み操作をブロックします。


MyISAM テーブルの読み取りと書き込みはシリアルです。つまり、読み取り操作中に書き込み操作を実行することはできず、その逆も同様です。ただし、特定の条件下では、MyISAM テーブルは同時クエリと挿入操作もサポートします。このメカニズムは、値が 0 に設定されている場合、システム変数 (concurrent_insert) を制御することによって実行されます。これが 1 の場合、MyISAM テーブルにホールがない (つまり、テーブルに削除された行がない) 場合、MyISAM は、その値が次の場合、あるプロセスがテーブルの末尾からレコードを挿入している間、1 つのプロセスがテーブルを読み取ることを許可します。 MyISAM テーブルにホールがあるかどうかに関係なく、テーブルの末尾へのレコードの同時挿入が許可されます。

MyISAM ロック スケジュールをどのように実装するかも非常に重要な問題です。たとえば、プロセスが MyISAM テーブルの読み取りロックをリクエストし、同時に別のプロセスも同じテーブルの書き込みロックをリクエストした場合、mysql はどのようにプロセスに優先順位を付けるのでしょうか?調査によると、書き込みプロセスが最初にロックを取得します (つまり、読み取り要求が最初にロック待機キューに到着します)。しかし、これには大きな欠陥も生じます。つまり、書き込み操作が多数になると、クエリ操作で読み取りロックを取得することが困難になり、永続的なブロックが発生する可能性があります。幸いなことに、いくつかの設定を通じて MyISAM のスケジュール動作を調整できます。パラメータ low-priority-updates を指定して、MyISAM のデフォルト エンジンに読み取りリクエストを優先させ、その値を 1 (low_priority_updates=1 に設定) に設定して優先度を下げることができます。

InnoDB ロックと MyISAM ロックの最大の違いは次のとおりです:

1. トランザクション (TRANCSACTION) をサポートします。

2. 行レベルのロックが使用されます。


トランザクションは、一連の SQL ステートメントで構成される論理処理単位であることがわかっています。それには、次の 4 つの属性 (略して ACID 属性) があります。

アトミック性: トランザクションは、データを操作するアトミックな操作単位です。変更は完全に実行されるか、まったく実行されません。

一貫性: トランザクションの開始時と完了時にデータの一貫性が維持される必要があります。
分離: トランザクションが「独立した」状態で実行されることを保証するために、データベース システムは特定の分離メカニズムを提供します。外部の同時操作の影響を受けない環境;
耐久性: トランザクション完了後のデータへの変更は永続的であり、システム障害が発生した場合でも維持できます。

同時トランザクション処理によって引き起こされる問題
逐次処理と比較して、同時トランザクション処理はデータベース リソースの使用率を大幅に向上させ、データベース システムのトランザクション スループットを向上させることができるため、より多くのユーザーをサポートできます。ただし、トランザクションを同時に処理すると、主に次のような問題が発生します。
1. 更新の喪失: 2 つ以上のトランザクションが同じ行を選択し、最初に選択された値に基づいて行を更新すると、各トランザクションが他のトランザクションの存在を認識しないため、更新の喪失が発生します。 問題 - 最後の更新が上書きされました。他のトランザクションによって行われた更新。たとえば、2 人の編集者が同じ文書の電子コピーを作成するとします。各編集者は独自にコピーを変更し、変更されたコピーを保存して元の文書を上書きします。最後に自分の変更のコピーを保存した編集者は、別の編集者が行った変更を上書きします。この問題は、あるエディターがトランザクションを完了してコミットするまで、あるエディターが同じファイルにアクセスできない場合に回避できます。
2. ダーティ読み取り: トランザクションが完了して送信される前に、このレコードのデータは矛盾した状態にあり、制御がない場合は、別のトランザクションも同じレコードを読み取ります。 2 番目のトランザクションは、これらの「ダーティ」データを読み取り、それに応じてさらなる処理を実行します。これにより、コミットされていないデータの依存関係が生成されます。この現象は明確に「ダーティ リーディング」と呼ばれています。
3. 非反復読み取り: トランザクションは、特定のデータを読み取った後、以前に読み取ったデータを再度読み取りますが、読み取ったデータが変更されているか、またはこれらのレコードが削除されていることがわかります。この現象は「非反復読み取り」と呼ばれます。
4. ファントム読み取り: トランザクションは同じクエリ条件に従って以前に取得したデータを再読み取りしますが、他のトランザクションがそのクエリ条件を満たす新しいデータを挿入したことがわかります。この現象は「ファントム読み取り」と呼ばれます。

トランザクション分離レベル
上記の同時トランザクション処理によって引き起こされる問題のうち、「更新損失」は通常完全に回避する必要があります。ただし、更新損失の防止はデータベース トランザクション コントローラーだけでは解決できません。そのため、更新されるデータに必要なロックをアプリケーションが追加する必要があります。
「ダーティ リード」、「非反復読み取り」、および「ファントム リード」は、実際にはデータベースの読み取り一貫性の問題であり、データベースが特定のトランザクション分離メカニズムを提供することで解決する必要があります。データベースがトランザクション分離を実装する方法は、基本的に次の 2 種類に分類できます。
1. 1 つは、他のトランザクションがデータを変更するのを防ぐために、データを読み取る前にデータをロックすることです。
2. もう 1 つは、ロックを追加せずに特定のメカニズムを通じてデータ要求時点の一貫したデータ スナップショット (スナップショット) を生成し、このスナップショットを使用して特定のレベル (ステートメント レベルまたはトランザクション レベル) の一貫した読み取りを提供することです。ユーザーの観点から見ると、データベースは同じデータの複数のバージョンを提供できるように見えるため、このテクノロジはデータ マルチバージョン同時実行制御 (略して MVCC または MCC) と呼ばれ、マルチバージョン データベースとも呼ばれます。

データベースのトランザクション分離が厳格であればあるほど、同時実行の副作用は小さくなりますが、支払う代償は大きくなります。これは、トランザクションの分離により本質的にトランザクションがある程度まで「シリアル化」されるためであり、これは明らかに「同時実行」が矛盾していることと同じです。 。同時に、アプリケーションごとに読み取りの一貫性とトランザクションの分離に対する要件も異なります。たとえば、多くのアプリケーションは「非反復読み取り」や「ファントム読み取り」に敏感ではなく、同時にデータにアクセスする機能を重視する場合があります。
「分離」と「同時実行」の間の矛盾を解決するために、ISO/ANSI SQL92 では 4 つのトランザクション分離レベルが定義されており、各レベルは異なる分離度を持ち、アプリケーションは独自のビジネス ロジックに従って異なる副作用を通過できます。 「分離」と「同時実行」の間の矛盾のバランスを取るために、さまざまな分離レベルを選択してください。表 20-5 は、これら 4 つの分離レベルの特性をわかりやすくまとめたものです。

読み取りデータの一貫性と許容される同時実行性の副作用
分離レベル 読み取りデータの一貫性 ダーティリード 反復不可能な読み取り ファントムリード
非コミット読み取り (コミットされていない読み取り) 物理的に損傷したデータが読み取られないことのみを保証できる最低レベル はい はいYes
Read commit ステートメントレベル No Yes Yes
Repeatable read (Repeatable read) トランザクションレベル No No Yes
Serializable (直列化可能) 最上位レベル、トランザクションレベル No No No

最後に注意すべきことは、特定のデータベースが上記の 4 つの分離レベルを必ずしも完全に実装しているわけではないということです。たとえば、Oracle は、読み取りコミットとシリアル化可能という 2 つの標準分離レベルのみを提供し、独自に定義された読み取り専用 SQL も提供します。サーバー 上記 ISO/ANSI SQL92 で定義されている 4 つの分離レベルをサポートしていることに加えて、「スナップショット」と呼ばれる分離レベルもサポートしていますが、厳密には MVCC を使用して実装された Serializable 分離​​レベルです。 MySQL は 4 つの分離レベルをすべてサポートしていますが、特定の実装にはいくつかの特徴があります。たとえば、一部の分離レベルでは MVCC の一貫した読み取りが使用されますが、InnoDB には 2 つの行ロック モードがあります:
1)。共有ロック (S): 1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが同じデータ セットに対して排他ロックを取得できないようにします。
( Select * from table_name where ……共有モードでロック)
2) 排他的ロック (X): 排他的ロックを取得するトランザクションがデータを更新できるようにし、他のトランザクションが同じデータに対して共有読み取りロックと排他的書き込みロックを取得できないようにします。セット。 (select * from table_name where…..for update)
行ロックとテーブル ロックの共存を可能にするために、内部で使用される 2 つのインテンション ロック (両方のテーブル ロック) も実装されています。共有ロックとインテント排他ロック。
1) 意図された共有ロック (IS): トランザクションは、データ行に行共有ロックを追加することを目的としており、データ行に共有ロックを追加する前に、まずテーブルの IS ロックを取得する必要があります。
2) 意図された排他ロック (IX): トランザクションは、データ行に行排他ロックを追加することを目的としており、データ行に排他ロックを追加する前に、まずテーブルの IX ロックを取得する必要があります。
InnoDB 行ロック モード互換性リスト
リクエスト ロック モード
互換性
現在のロック モード トランザクションによってリクエストされたロック モードが現在のロックと互換性がある場合、InnoDB はリクエストされたロックをトランザクションに許可します。トランザクションはロックが解放されるまで待機します。
インテンション ロックは InnoDB によって自動的に追加され、ユーザーの介入は必要ありません。 UPDATE、DELETE、および INSERT ステートメントの場合、InnoDB は関連するデータ セットに排他ロック (X) を自動的に追加します。通常の SELECT ステートメントの場合、InnoDB は次の方法でレコード セットに共有ロックまたは排他ロックを追加できます。発言。
1. 共有ロック (S): SELECT * FROM table_name WHERE ... 共有モードでロックします。
2. 排他的ロック (X): SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB の行ロックは、インデックスのインデックス エントリをロックすることによって実装されます。これは、データ ブロック内の対応するデータ行をロックすることによって実装される MySQL や Oracle とは異なります。 InnoDB の行ロック実装機能は、InnoDB がインデックス条件を通じてデータを取得する場合にのみ行レベルのロックを使用することを意味します。それ以外の場合、InnoDB はテーブル ロックを使用します。実際のアプリケーションでは、InnoDB 行ロックのこの機能に特別な注意を払う必要があります。そうしないと、多数のロック競合が発生し、同時実行パフォーマンスに影響を与える可能性があります。


テーブルレベルのロック競合のクエリ
テーブル ロック競合:
table_locks_waited および table_locks_immediate ステータス変数をチェックすることで、システム上のテーブル ロック競合を分析できます:

mysql> show status like ‘table%’; 
+———————–+——-+ 
| Variable_name         | Value | 
+———————–+——-+ 
| Table_locks_immediate | 2979  | 
| Table_locks_waited    | 0     | 
+———————–+——-+ 
2 rows in set (0.00 sec))

Table_locks_waited の値が比較的高い場合は、テーブル ロック競合があることを示します。テーブルレベルのロック競合という重大な問題が発生します。

InnoDB 行ロックの競合:

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

mysql> show status like ‘innodb_row_lock%’; 
+——————————-+——-+ 
| Variable_name                 | Value | 
+——————————-+——-+ 
| InnoDB_row_lock_current_waits | 0     | 
| InnoDB_row_lock_time          | 0     | 
| InnoDB_row_lock_time_avg      | 0     | 
| InnoDB_row_lock_time_max      | 0     | 
| InnoDB_row_lock_waits         | 0     | 
+——————————-+——-+ 
5 rows in set (0.01 sec)

MyISAM 書き込みロックの実験:

MyISAM テーブルの読み取り操作は、他のユーザーが同じテーブルにアクセスすることをブロックしません読み取りリクエストはブロックされますが、MyISAM テーブルへの書き込み操作は、MyISAM テーブルの読み取り操作と書き込み操作の間、および書き込み操作の間の同じテーブルに対する他のユーザーの読み取りおよび書き込み操作をブロックします。 ！表20-2に示す例によれば、スレッドがテーブルの書き込みロックを取得すると、ロックを保持しているスレッドのみがテーブルを更新できることがわかります。他のスレッドからの読み取りおよび書き込み操作は、ロックが解放されるまで待機します。

User1: re

mysql> lock table film_text write;

ロックテーブルの現在のセッションの問い合わせ、更新、および挿入を実行できます:

mysql> select film_id,title from film_text where film_id = 1001;

E

USER2:

mysql> select film_id,title from film_text where film_id = 1001;

待機中

User1:

ロックの解放:

Rreeeee

user2:ロックロック、クエリは次を返します:
InnoDB ストレージ エンジンの共有ロック実験

mysql> unlock tables;

USER1:
現在のセッションは、actor_id=178 のレコードに共有モード共有ロックを追加します:

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER2:
他のセッションは引き続きレコードをクエリできますまた、このレコードに共有モードの共有ロックを追加することもできます:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER1:

現在のセッションはロックされたレコードを更新し、ロックを待機しています:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

Waiting

USER2:
他のセッションもレコードを更新します、デッドロック終了が発生します:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

ERROR 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました; トランザクションを再起動してください
USER1:
ロックを取得した後、正常に更新できます:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

排他的ロックInnoDB ストレージ エンジンの例

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER1：
当前session对actor_id=178的记录加for update的排它锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2：
其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;

USER1：
当前session可以对锁定的记录进行更新操作，更新后释放锁：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2：
其他session获得锁，得到其他session提交的记录：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小,这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时,提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入,当表没有空洞(删除过记录),在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入, 1当表没有空洞时,执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1针对表的删除频率来设置.
delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘.值为 0不开启, 1开启.默认开启.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入,将数据先交给内存队列,然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持,目前来看,常用的InnoDB不支持, MyISAM支持.根据实际情况调大,一般默认够用了。

以上がmysqlデータベースのロックメカニズムの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。