Mysqlトランザクション処理の詳細な説明

1. MySQLのトランザクションの概念

MySQL トランザクションは主に、操作量が多く複雑度の高いデータを処理するために使用されます。論理実行単位は、1 つまたは複数のデータベース操作のシーケンスで構成されます。この一連の操作は、完全に実行されるか、または中止されます。 MySQL では、Innodb データベースエンジンを使用するデータベースまたはテーブルのみがトランザクションをサポートします。トランザクションは、挿入、更新、および削除ステートメントを管理するために使用されます。

2. トランザクション特性:原子性、一貫性、独立性、永続性。これら 4 つの特性は、ACID プロパティとも呼ばれます。

1. 原子性: トランザクションは、アプリケーション内の最小の実行単位です。これは、原子が自然界の最小の粒子であり、分割できないという特性を持っているのと同じです。トランザクションは、アプリケーション内の最小の分割不可能な論理実行単位です。トランザクションのグループは、成功するか取り消されるかのいずれかになります。

2. 安定性と一貫性: トランザクション実行の結果、データベースは 1 つの一貫した状態から別の一貫した状態に変更される必要があります。正常にコミットされたトランザクションの結果のみが含まれている場合、データベースは一貫した状態にあります。一貫性は原子性によって保証されます。不正なデータ（外部キー制約など）がある場合、トランザクションは取り消されます。

3. 分離: 各トランザクションの実行は互いに干渉せず、トランザクションの内部操作は他の同時実行トランザクションから分離されます。つまり、同時に実行されたトランザクションは互いの中間状態を認識できず、同時に実行されたトランザクションは互いに影響を与えることができません。トランザクションは独立して実行されます。トランザクションの結果が他のトランザクションに影響を与える場合、他のトランザクションは取り消されます。トランザクションを 100% 分離するには、速度を犠牲にする必要があります。

4. 永続性と信頼性: 永続性は永続性とも呼ばれ、トランザクションがコミットされると、データに加えられた変更は永続的なストレージに記録され、通常は物理データベースに保存されることを意味します。ソフトウェアまたはハードウェアがクラッシュすると、InnoDB データテーブルドライバーはログファイルを使用してファイルを再構築および変更します。信頼性と高速性を同時に実現することはできません。innodb_flush_log_at_trx_commit オプションは、トランザクションがログに保存されるタイミングを決定します。

注意: ストレージエンジン MyISAM はトランザクションをサポートしていませんが、ストレージエンジン InnoDB はトランザクションをサポートしています。トランザクションは、データに影響を与えるステートメントに対してのみ有効です。 show engines MySQL ロックでサポートされているデータエンジンを表示します。

3. データの概念の読み取り

1. ダーティリード: ダーティリードはダーティデータの読み取りであり、ダーティデータとはコミットされていないデータのことです。トランザクションはレコードを変更しています。トランザクションが完了してコミットされるまで、データは保留状態にあります (コミットまたはロールバックされる可能性があります)。この時点で、2 番目のトランザクションはコミットされていないデータを読み取り、それに基づいてさらに処理を実行し、コミットされていないデータの依存関係を生成します。この現象はダーティリードと呼ばれます。

2. 非反復読み取り: トランザクションは同じレコードを 2 回読み取りますが、2 回読み取られるデータは異なります。これを非反復読み取りと呼びます。つまり、このトランザクションの 2 回の読み取りの間に、他のトランザクションによってデータが変更されます。

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

4. トランザクション分離レベル

トランザクション分離レベルの構文を変更します。
[セッション | グローバル] トランザクション分離レベルの設定 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

1. コミットされていないデータの読み取り (無許可の読み取り、コミットされていないデータの読み取り): これは最も低い分離レベルであり、他のトランザクションがコミットされていないデータを参照できるようにします。このレベルではダーティリードが発生する可能性があります。トランザクションがデータの書き込みを開始した場合、別のトランザクションは同時に書き込むことはできませんが、他のトランザクションはこのデータ行を読み取ることができます。この分離レベルは、排他的書き込みロックを通じて実現できます。これにより更新の損失は回避されますが、ダーティリードが発生する可能性があります。つまり、トランザクション B はトランザクション A がコミットしていないデータを読み取ります。 SELECT 文は非ロック方式で実行されるため、ダーティデータを読み取ることが可能です。分離レベルは最低です。

SET セッション トランザクション分離レベルはコミットされていない読み取りです。
SET グローバル トランザクション分離レベル 読み取り未コミット;/* グローバルでは使用しないことを推奨 */
@@global.tx_isolation を選択します。
@@session.tx_isolation を選択します。
@@tx_isolation を選択します。

シンプルな学生テーブルを作成し、ID、名前、および数値フィールドを設定し、トランザクション 1 を開始し、テーブルにストアドプロシージャを追加し、トランザクションをコミットしません。現在のデータベーストランザクションステータスを確認すると、トランザクションレベルが READ UNCOMMITTED のデータトランザクションを確認できます。

学生が存在する場合はテーブルを削除します。
テーブル学生を作成(
id int 主キー 自動増分 コメント 'id',
名前 varchar(100) コメント '名前',
数値整数
);
proc_on_sw が存在する場合はプロシージャを削除します。
デリミタ;;
プロシージャ proc_on_sw() を作成する
始める
トランザクションを開始します。
学生(名前、番号)に値('aaa'、1)を挿入します。
information_schema.INNODB_TRX から * を選択します。
終わり
;;
デリミタ;;
proc_on_sw() を呼び出す;

新しいトランザクション 2 を作成し、学生テーブルをクエリします。READ UNCOMMITTED レベルでは、他のトランザクションのコミットされていないデータを確認できます。データベーストランザクションのステータスを再度確認すると、ステータスが正常であることがわかります。

トランザクションを開始します。
学生から*を選択します。
専念;
information_schema.INNODB_TRX から * を選択します。

2. 読み取りコミット (承認済み読み取り、読み取りコミット): データを読み取るトランザクションは、他のトランザクションが引き続きデータ行にアクセスできるようにしますが、コミットされていない書き込みトランザクションは、他のトランザクションが行にアクセスすることを禁止します。この分離レベルではダーティリードは回避されますが、繰り返し不可能なリードが発生する可能性があります。トランザクション A が事前にデータを読み取り、トランザクション B がすぐにデータを更新してトランザクションをコミットし、トランザクション A が再度データを読み取ると、データが変更されていました。

SET セッション トランザクション分離レベルの読み取りコミット;
SET グローバル トランザクション分離レベル read committed; /* グローバルでは使用しないことを推奨 */

proc_on_up が存在する場合はプロシージャを削除します。
デリミタ;;
プロシージャ proc_on_up() を作成する
始める
自動コミットを0に設定します。
学生セット名を 'cc' に設定し、ID を 1 にして更新します。
専念;
自動コミットを1に設定します。
終わり
;;
デリミタ;;
proc_on_up() を呼び出す;
学生から*を選択します。

3. 繰り返し読み取り: データが読み取られると (トランザクションが開始されると)、変更操作は許可されなくなります。トランザクションが開始されると、他のトランザクションの UPDATE 変更操作は許可されません。繰り返し不可能な読み取りは、変更、つまり UPDATE 操作に対応します。しかし、ファントムリードの問題がまだ発生する可能性があります。ファントムリード問題は UPDATE 操作ではなく INSERT 操作に対応するためです。反復不可能な読み取りとダーティ読み取りは回避されますが、ファントム読み取りが発生する場合があります。これは、「共有読み取りロック」と「排他的書き込みロック」によって実現できます。

set session transaction isolation level repeatable read;

4. シリアル化とシリアライゼーション: 厳密なトランザクション分離を提供します。トランザクションはシリアルに実行する必要があります。トランザクションは 1 つずつしか実行できず、同時に実行することはできません。「行レベルのロック」だけではトランザクションのシリアル化を実現できない場合は、クエリ操作を実行したばかりのトランザクションによって新しく挿入されたデータにアクセスされないようにするために、他のメカニズムを使用する必要があります。シリアル化は最も高いトランザクション分離レベルですが、コストが最も高く、パフォーマンスが非常に低いため、ほとんど使用されません。このレベルでは、トランザクションは順番に実行されるため、ダーティリードや反復不可能なリードだけでなく、ファントムリードも回避されます。

set session transaction isolation level serializable;

分離レベルダーティリード非反復リードファントムリードコミットされていないリードはいはいはい
コミットされた読み取り NO YES YES
繰り返し読み取りいいえいいえはい
シリアル化 NO NO NO

5. コミットとロールバックを含む完全な例

pro_new が存在する場合はプロシージャを削除します。
デリミタ;;
プロシージャ pro_new(out rtn int) を作成します。
始める
err INT デフォルト 0 を宣言します。
-- 例外が発生した場合は自動的に処理され、ロールバックされます
sqlexception ROLLBACK の終了ハンドラを宣言します。 
-- トランザクションを開始します。autocommit=0 を設定します。
トランザクションを開始します。
学生(名前、数値)に値(NULL、2.3)を挿入します。
-- set err = @@IDENTITY; -- = 最後の挿入の自動増分 ID を取得します。
set err =last_insert_id(); -- 最後の挿入の自動増分 ID を取得します
学生(名前、番号)にVALUEs('ccc'、err)を挿入します。
-- 操作に異常がなければ、トランザクションをコミットします。
-- 戻り値を1に設定する
rtn=1 を設定します。
自動コミットを1に設定します。
終わり
;;
デリミタ;;
@n=1 を設定します。
pro_new(@n); を呼び出します。
@n を選択します。

以下もご興味があるかもしれません: