MySQL ロックとトランザクション分離レベル (概要)-mysql チュートリアル-php.cn

今日のインターネットでは、データベースなしでは大規模なマルチプレイヤー APP を開発できません。誰もが高い同時実行性で読み書きできることを保証する方法は、常に難しいアーキテクチャ上の問題でした。まず第一に、高い同時実行性が排除され、一貫した読み書きを保証するために最も一般的に使用される方法はトランザクションであり、実装の重要なポイントです。トランザクションがロックされているメカニズム。

MySQL ロックとトランザクション分離レベル (概要)

今日は、一貫した読み取りと書き込みを実現するために、InnoDB ストレージエンジンが高い同時実行性の下でロックメカニズムを実装する方法の原理と実装を紹介します。

ロック

データベースのロックメカニズムは、データベースをファイルシステムと区別する重要な機能です。共有リソースへの同時アクセスを管理するために使用されます。 InnoDB は、操作データテーブル、LRU ページリスト、バッファプール内のデータ行など、多くの場所でロックメカニズムを使用しており、一貫性と整合性を確保するには、ロックメカニズムが必要です。

データベースが異なると、ロックメカニズムの設計と実装は完全に異なります。

##MyISAM エンジン: テーブルロックの設計、同時読み取りは問題ありませんが、同時書き込みのパフォーマンスは劣ります。

#●Microsoft SQL Server: オプティミスティック同時実行とペシミスティック同時実行をサポートします。オプティミスティック同時実行は行レベルのロックをサポートします。ロックの維持にはコストがかかります。行ロックの数がしきい値を超えると、テーブルロックにアップグレードされます。

#●InnoDB エンジン: 行ロックをサポートし、一貫した非ロック読み取りを提供します。行ロックには追加のオーバーヘッドはなく、パフォーマンスも低下しません。

#●Oracle: InnoDB エンジンに非常に似ています。

2 種類のロック: ロックとラッチ

データベースでは、ロックとラッチの両方をロックと呼ぶことができますが、大きな違いがあります。違い。 ラッチは一般にラッチと呼ばれ、同時スレッドによる重要なリソース操作の正確性を保証するために使用されます。オブジェクトはメモリデータ構造です。ロック時間は非常に短くする必要があり、デッドロックは発生しません。検出されました。 InnoDB エンジンでは、mutex (ミューテックス) と rwlock (読み取り/書き込みロック) に分けられます。

Lock は、データベース内のテーブル、ページ、行などのオブジェクトをロックするために使用されます。対象はトランザクションです。コミット/ロールバック後に解放され、デッドロックが検出されます。行ロック、テーブルロック、インテントロックに分かれています。

下記の錠前は錠前タイプの錠前を指します。

4 つのロックタイプ

InnoDB は 4 つのロックをサポートします: # 共有ロック (S ロック): トランザクションを許可します。データ行

##●排他的ロック (X ロック): トランザクションによるデータ行の削除または更新を許可します

##●意図 S ロック: トランザクションはテーブル内の特定の行を取得しようとします。ロック

#意図 X ロック: トランザクションはテーブル内の特定の行の排他ロックを取得しようとしています

#トランザクション T1 が行の共有ロックを取得すると、行データは変更されないため、トランザクション T2 は、行 r の共有ロックを直接取得することもできます。これをロック互換と呼びます。

トランザクション T3 がデータを変更するために行 r の排他ロックを取得したい場合、T1/T2 が行共有ロックを解放するまで待つ必要があります。これはロックの非互換性と呼ばれます。

S ロックと X ロックはどちらも行ロックですが、IS ロックと IX ロックは意図的なロックであり、テーブルロックに属します。インテンションロックは、トランザクション内の次の行に要求されるロックのタイプを明らかにするように設計されています。つまり、テーブルロックのより細かい粒度でロックします。 InnoDB はテーブルロックをサポートしているため、インテントロックはテーブル全体のスキャン以外のリクエストをブロックしません。

ロックの互換性:

#ISIX#IS互換性あり互換性あり互換性あり互換性なしIX互換性あり互換性あり互換性なし互換性なしS互換性あり互換性なし#互換性あり#互換性なし非互換ストレージトランザクションおよびロック情報の 3 つのテーブルshow Engine innodb status コマンドを使用して、トランザクションセクションで現在のロックリクエストの情報を表示できます。

S


	##XX	互換性なし	非互換	非互換

InnoDB1.0 以降、INNODB_TRX (トランザクションテーブル)、INNODB_LOCKS (ロックテーブル)、INNODB_LOCK_WAITS (ロック待機テーブル) が INFORMATION_SCHEMA アーキテクチャの下に追加され、これら 3 つのテーブルを通じて、現在の状況を監視できます。リアルタイムでトランザクションを分析し、考えられるテーブルの問題を分析します。 3 つのテーブルの定義は次のとおりです:

INNODB_TRX

##trx_id

InnoDB ストレージエンジン内の一意のトランザクション ID現在のトランザクションのステータス trx_startedトランザクションの開始時刻trx_requested_lock_idトランザクションを待機しているロック IDC。ステータスが LOCK WAIT でない場合は、 NULLtrx_wait_startedトランザクションの開始を待機する時間trx_weightトランザクションの重みトランザクションは、トランザクションによって変更およびロックされた行の数を反映します。ロールバックが必要な場合は、ロールバックの値が最も小さいトランザクションを選択します。trx_mysql_thread_idMySQL のスレッド ID、show processlist で表示される結果 trx_queryトランザクション操作の SQL ステートメント

`trx_state`

#lock_idlock_trx_idトランザクション IDlock_modeロックモードlock_typeロックのタイプ、テーブルロックまたは行ロックlock_tableロック対象のテーブルlock_indexロックされたインデックス#ロックオブジェクトのスペース ID##lock_page#lock_recトランザクションロックされた行の数、テーブルがロックされている場合は NULL トランザクションロックレコードの主キー値。テーブルがロックされている場合は NULL になります。 #INNODB_LOCK_WAITS##requesting_trx_id

#INNODB_LOCKS
lockID




#lock_space
ロックオブジェクトの数トランザクションロックされたページ、テーブルがロックされている場合は NULL
	#lock_data

ロックリソースを申請するためのトランザクション ID適用ロックの IDblocking_trx_idブロックするトランザクション IDblocking_lock_idブロッキングロック IDINNODB_TRX を通じて、すべてのトランザクション、トランザクションがブロックされているかどうか、およびブロックされたロック ID を確認できます。その後、を通じてすべてのロック情報を表示します。 INNODB_LOCK_WAITS を通じてロックの待機情報とブロック関係を確認できます。

`requesting_lock_id`



INNODB_LOCKS	その後、

これら 3 つのテーブルを使用すると、トランザクションとロックのステータスをより明確に表示でき、結合クエリを実行することもできます。次のシナリオでは、これら 3 つのテーブルの内容を示します。

#分離レベル

まず、データベースの 4 つのトランザクション分離レベルについて説明します。 UNCOMMITTED (0 ): ブラウズアクセスレベル、ダーティ読み取り、非反復読み取り、およびファントム読み取りがあります。#●READ COMMITTED(1): カーソル安定性レベル、非反復読み取り、ファントム読み取りがあります

● REPEATABLE READ（ 2): ファントム読み取りあり

# SERIALIZABLE (3): 分離レベル、トランザクションのセキュリティは確保されていますが、完全にシリアルでパフォーマンスが低いです

これら 4 つのトランザクション分離InnoDB のデフォルトの分離レベルは REAPEATABLE READ ですが、他のデータベースとは異なり、ファントムリードの発生を回避できる Next-Key-Lock ロックアルゴリズムも使用するため、SQL 標準のレベルを完全に満たすことができます。トランザクション分離要件、つまり、SERIALIZABLE 分離レベルを達成できます。分離レベルが低いほど、トランザクションによって要求されるロックが少なくなるか、ロックの保持時間が短くなるため、ほとんどのデータベースのデフォルトの分離レベルは READ COMMITED になります。ただし、関連する分析では、分離レベルのパフォーマンスのオーバーヘッドはほぼ同じであるため、ユーザーがパフォーマンスを向上させるために分離レベルを調整する必要がないことも指摘しています。

トランザクション分離レベルを表示および変更するコマンド:

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

ログイン後にコピー

この例では、このセッションのトランザクション分離レベルが変更されます。グローバルパラメーターを変更する必要がある場合は、セッションを置き換えることができますグローバルで。永続的な変更を加えたい場合は、設定ファイルを変更する必要があります。

[mysqld]
transaction-isolation = READ-COMMITED

ログイン後にコピー

SERIALIZABLE のトランザクション分離レベルでは、InnoDB は各 SELECT ステートメントの後に LOCK IN SHARE MODE を自動的に追加し、共有ロックを読み取り操作。そのため、一貫した非ロック読み取りはサポートされなくなりました。

InnoDB は REPEATABLE READ 分離レベルで SERIALIZABLE に到達できるため、通常は最高の分離レベルを使用する必要はありません。

一貫した非ロック読み取りと複数バージョンの同時実行制御

一貫した非ロック読み取り (一貫した非ロック読み取り) とは、InnoDB のことを指します。複数行現在の実行時にデータベース内の行データを読み取るバージョン管理 (Multi Version Concurrency Control、MVCC) メソッド。

つまり、読み取り行が変更操作を受けている場合、読み取りは行ロックの解放を待たずに、行のスナップショットデータを読み取ります。スナップショットは行の履歴データを指し、元に戻す操作によって完了します。この方法により、データベースの同時実行性が大幅に向上します。これは InnoDB のデフォルト設定でもあります。

スナップショットは現在の行の履歴バージョンですが、複数のバージョンが存在する場合があります。行データには複数のスナップショットデータがあります。このテクノロジは行マルチバージョンテクノロジとなり、その結果生じる同時実行制御はマルチ-スナップショット。バージョン同時実行制御 (MVCC)。 InnoDB は、READ COMMITED および REPEATABLE READ 分離レベルで非ロックの一貫した読み取りを使用しますが、これら 2 つの分離レベルで使用されるクイックデータ定義は異なります。

#● READ COMMITED: 常に最新のスナップショットを読み取ります

● REPEATABLE READ: トランザクションの開始時に常に行データのバージョンを読み取ります。

例を実行します。

##一貫した非ロック読み取り

Time

セッション A

セッション B

12select * from z where a = 3;##開始4update z set b=2 where a=3;5##コミット;7選択* z から、a = 3;##8

BEGIN

	3

	select * from z where a = 3 ;
6

COMMIT;		## 在这个例子中我们可以清晰的看到0、1、2三种隔离级别的区别： #在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出 mysql> set session transaction isolation level READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> select @@tx_isolation; +------------------+ \| @@tx_isolation \| +------------------+ \| READ-UNCOMMITTED \| +------------------+ 1 row in set (0.00 sec) # A会话：T1事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from z where a = 3; +---+------+ \| a \| b \| +---+------+ \| 3 \| 1 \| +---+------+ 1 row in set (0.00 sec) # B会话：T2事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update z set b=2 where a=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读 mysql> select * from z where a=3; +---+------+ \| a \| b \| +---+------+ \| 3 \| 2 \| +---+------+ 1 row in set (0.00 sec) # A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别 mysql> select * from z where a=3; +---+------+ \| a \| b \| +---+------+ \| 3 \| 1 \| +---+------+ 1 row in set (0.00 sec) # B会话：T2事务 mysql> commit; Query OK, 0 rows affected (0.00 sec) # A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性 mysql> select * from z where a=3; +---+------+ \| a \| b \| +---+------+ \| 3 \| 2 \| +---+------+ 1 row in set (0.00 sec) # A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别 mysql> select * from z where a=3; +---+------+ \| a \| b \| +---+------+ \| 3 \| 1 \| +---+------+ 1 row in set (0.00 sec) # A会话：T1事务 mysql> commit; Query OK, 0 rows affected (0.00 sec) ログイン後にコピー一致性锁定读和SERIALIZABLE隔离在默认的REPEATABLE READ隔离级别时，InnoDB使用的是一致性非锁定读。但有时我们也需要显示的指定使用一致性锁定读来保证读取操作时对数据进行加锁达到一致性。这要求数据库支持锁定读加锁语句：　●　select ... for update: 读取时对行记录加X锁　●　select ... lock in share mode：读取时对行记录加一个S锁这两种锁必须在一个事务中，当事务提交后锁也就释放了，因此务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。我们在前面隔离级别时也说过SERIALIZABLE隔离级别会对读操作自动加上LOCK IN SHARE MODE指令来加上一个共享锁，因此不再支持一致性的非锁定读。这也是隔离级别3的一大特性。总结由于锁的概念非常重要，这里先讲了锁的概念、锁的类型、锁的信息查看、事务的隔离级别和区别，后面我们会继续说锁的算法、锁的三种问题和幻读、死锁和锁升级。推荐学习：MySQL教程