MySQL トランザクションの概念と使用法の詳細な説明

情事の概念

MySQL トランザクションは、すべて正常に実行されるか、すべて失敗してロールバックされる 1 つ以上のデータベース操作です。
トランザクションは、REDO ログと UNDO ログを含むトランザクションログを通じて実装されます。

取引の状態

アクティブ

トランザクションに対応するデータベース操作が進行中の場合、トランザクションはアクティブ状態にあると言います。

部分的にコミット

トランザクション内の最後の操作が実行されたが、操作がすべてメモリ内で実行されるため、その影響がディスクにフラッシュされない場合は、トランザクションは部分的にコミットされた状態にあると言われます。

失敗した

トランザクションがアクティブまたは部分的にコミットされた状態にある場合、何らかのエラー (データベースエラー、オペレーティングシステムエラー、直接的な停電など) が発生して実行を続行できないか、現在のトランザクションの実行が手動で停止されることがあります。トランザクションは失敗した状態にあると言います。

中止

トランザクションが実行の途中で失敗した場合、現在のデータベースにおける失敗したトランザクションの影響を元に戻します。この元に戻すプロセスをロールバックと呼びます。
ロールバック操作が完了すると、つまりデータベースがトランザクションが実行される前の状態に復元されると、トランザクションは中止された状態にあると言います。

コミットした

部分的にコミットされた状態のトランザクションが、変更されたすべてのデータをディスクに同期した場合、そのトランザクションはコミットされた状態にあると言えます。

図からわかるように、トランザクションのライフサイクルは、トランザクションがコミットまたは中止された状態にある場合にのみ終了したと見なされます。コミットされたトランザクションの場合、トランザクションによってデータベースに加えられた変更は永続的に有効になります。中止された状態のトランザクションの場合、トランザクションによってデータベースに加えられたすべての変更は、トランザクションが実行される前の状態にロールバックされます。

取引の役割

トランザクションの主な目的は、複雑なデータベース操作、特にデータに同時にアクセスする場合のデータの一貫性を確保することです。
MySQL トランザクションは主に、操作量が多く複雑度の高いデータを処理するために使用されます。

取引の特徴

原子性（不可分性とも呼ばれる）

トランザクションのデータ操作は、すべて正常に実行されるか、すべて失敗して、トランザクションが実行されなかったかのように実行前の状態にロールバックされます。

孤立（独立とも呼ばれる）

複数のトランザクションは互いに分離されており、互いに影響を及ぼしません。データベースでは、複数の同時トランザクションが同時にデータを読み取り、書き込み、変更することができます。分離により、複数のトランザクションが同時に実行されるときに、相互実行によるデータの不整合を防ぐことができます。

4 つの分離状態:
1. コミットされていないものを読む
2. コミットされたデータを読み取る
3. 繰り返し読み取り
4. シリアル化可能

一貫性

トランザクション操作の前後でデータは同じ状態のままであり、データベースの整合性は損なわれません。
原子性と独立性は一貫性に重大な影響を及ぼします。

耐久性

トランザクション操作が完了すると、データはディスクにフラッシュされて永続的に保存され、システム障害が発生しても失われることはありません。

トランザクション構文

データ

#データテーブルを作成します:
テーブルアカウントを作成(
  -> id int(10) 自動インクリメント、
  -> 名前varchar(30),
  -> バランス int(10)、
  ->主キー (id));
#データを挿入:
account(name,balance) に values('老王媳妇',100),('老王',10) を挿入します。

mysql> アカウントから * を選択します。
+----+--------------+---------+
| ID | 名前 | 残高 |
+----+--------------+---------+
| 1 | 老王の妻 | 100 |
| 2 | ラオ・ワン | 10 |
+----+--------------+---------+

老王の妻はWeChatアカウントに100元を貯めていて、毎月老王にお小遣いをあげている。成績が良ければ良いほど、もらえるお金も増える。老王には小さな貯金箱もある。すでにお小遣いとして10元貯めている、ハハハ。

始める

トランザクション開始モード1

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> トランザクション操作 SQL......

トランザクションを開始する [修飾子]

修飾子:
1. 読み取り専用 //読み取り専用 2. 読み取り書き込み //デフォルトで読み取りと書き込み 3. 一貫性のあるスナップショット //一貫性のある読み取り

トランザクション開始モード2

mysql> トランザクションを読み取り専用で開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> トランザクション操作 SQL......

#読み取り専用が設定されている場合、データが変更されるとエラーが報告されます。

mysql> トランザクションを読み取り専用で開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントを更新し、balance=banlance+30 を設定します。id = 2;
エラー 1792 (25006): 読み取り専用トランザクションではステートメントを実行できません。

専念

トランザクションはコミットされ、成功した場合はディスクにフラッシュされます。

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

ロールバック

トランザクションはロールバックされ、トランザクション操作前の状態に戻ります。

mysql> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

ここで強調しておきたいのは、ROLLBACK ステートメントは、プログラマーがトランザクションを手動でロールバックするためにのみ使用されるということです。トランザクションの実行中にエラーが発生し、実行を続行できない場合は、トランザクション自体が自動的にロールバックされます。

完全な提出例

1月、老王さんの成績は非常に良かったので、妻はご褒美として20元の小遣いをあげた。

実行手順:
1. ラオ・ワンの妻のアカウントからデータを読む
2. ラオ・ワンの妻の口座から20元を引き落とす
3. Lao Wangのアカウントからデータを読み取る
4. ラオ・ワンの口座に20元を追加する
5. 実行の送信が成功しました
6. 現時点では、王さんの妻の口座には80元しかありませんが、王さんの口座には30元しかありません。王さんはとても喜んでいます。

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> アカウントを更新し、balance=balance-20 に設定します。ここで、id は 1 です。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> アカウントを更新し、残高をbalance+20に設定します。IDは2です。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

#口座残高:
mysql> アカウントから * を選択します。
+----+--------------+---------+
| ID | 名前 | 残高 |
+----+--------------+---------+
| 1 | 老王の妻 | 80 |
| 2 | ラオ・ワン | 30 |
+----+--------------+---------+

完全なロールバックの例

2月、老王は家事や犬の散歩にこだわるなど、成績が非常に良かった。老王の妻は彼に25元の小遣いを渡そうとしたが、老王は褒められるのが耐えられなかった。老王の妻が老王に小遣いを振り込んでいるとき、突然、テーブルの上の老王の携帯電話に小さな女の子からのWeChatメッセージが届いているのに気づいた。「親愛なる王兄さん…」老王の妻は非常に怒り、激怒して振り込みを取り消し、今月の小遣いをキャンセルした。

実行手順:
1. ラオ・ワンの妻のアカウントからデータを読む
2. ラオ・ワンの妻の口座から25元を引き落とす
3. Lao Wangのアカウントからデータを読み取る
4. ラオ・ワンの口座に25元を追加する
5. この時、老王の妻は前回の手術を撤回した。
6. この時点で、王氏とその妻の口座残高は、手術前と同じままです。

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントを更新し、残高をbalance-25に設定します。ここで、id = 1です。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> アカウントを更新し、残高をbalance+25に設定します。ここで、id = 2です。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

#口座残高:
mysql> アカウントから * を選択します。
+----+--------------+---------+
| ID | 名前 | 残高 |
+----+--------------+---------+
| 1 | 老王の妻 | 80 |
| 2 | ラオ・ワン | 30 |
+----+--------------+---------+

トランザクション対応ストレージエンジン

1. バイナリ
2. NDB

MyISAM などのトランザクションをサポートしないストレージエンジンの場合、トランザクションは有効にならず、SQL ステートメントは自動的にコミットされます。したがって、トランザクションをサポートしないストレージエンジンではロールバックは無効です。

テーブルtb1を作成します(
  -> id int(10) 自動インクリメント、
  -> 名前varchar(30),
  -> 主キー (id)
  ->)エンジン=myisam 文字セット=utf8mb4;
  
mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> tb1(name) に値 ('Tom') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb1 から * を選択します。
+----+------+
| ID | 名前 |
+----+------+
| 1 | トム |
+----+------+
セット内の 1 行 (0.00 秒)

mysql> rollback; //ロールバックは無効です。クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)

mysql> tb1 から * を選択します。
+----+------+
| ID | 名前 |
+----+------+
| 1 | トム |
+----+------+
セット内の 1 行 (0.00 秒)

取引の設定と表示

# トランザクションの開始ステータスを確認します。
mysql> 'autocommit' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| 自動コミット | オン |
+---------------+-------+

デフォルトでは、トランザクションは自動的にコミットされ、各 SQL ステートメントは自動的にコミットされます。
このときトランザクションを操作する必要があるときは、明示的に開始（begin または start transaction）し、コミット（commit）またはロールバック（rollback）する必要があります。
OFF に設定すると、コミットまたはロールバック操作が実行されるまでトランザクションは実際には実行されません。

自動送信モードをオフにする

最初

START TRANSACTION または BEGIN ステートメントを使用してトランザクションを明示的に開始します。

2番目

システム変数 autocommit の値を OFF に設定します。

自動コミットを OFF に設定します。

暗黙の服従

START TRANSACTION または BEGIN ステートメントを使用してトランザクションを開始するか、システム変数 autocommit の値を OFF に設定すると、トランザクションは自動的にコミットされません。ただし、特定のステートメントを入力すると、COMMIT ステートメントを入力した場合と同様に、トランザクションは自動的にコミットされます。特別なステートメントによってトランザクションがコミットされるこの状況を、暗黙的なコミットと呼びます。

データ定義言語 (DDL) は、データベースオブジェクトを定義または変更するために使用されます。

いわゆるデータベースオブジェクトとは、データベース、テーブル、ビュー、ストアドプロシージャなどを指します。 CREATE、ALTER、DROP などのステートメントを使用して、これらのいわゆるデータベースオブジェクトを変更すると、前のステートメントが属するトランザクションが暗黙的にコミットされます。

始める;
SELECT ... # トランザクション内のステートメント UPDATE ... # トランザクション内のステートメント ... # トランザクション内のその他のステートメント CREATE TABLE ... # このステートメントは、前のステートメントが属するトランザクションを暗黙的にコミットします

MySQLデータベースのテーブルを暗黙的に使用または変更する

mysql データベース内のテーブルを暗黙的に使用または変更します。
ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD などのステートメントを使用すると、前のステートメントが属するトランザクションも暗黙的にコミットされます。

トランザクション制御またはロックステートメント

トランザクション制御またはロックステートメント。
トランザクションがコミットまたはロールバックされる前に、START TRANSACTION または BEGIN ステートメントを使用して別のトランザクションを開始すると、前のトランザクションが暗黙的にコミットされます。

始める;
SELECT ... # トランザクション内のステートメント UPDATE ... # トランザクション内のステートメント ... # トランザクション内のその他のステートメント BEGIN; # このステートメントは、前のステートメントが属するトランザクションを暗黙的にコミットします

または、自動コミットシステム変数の現在の値が OFF で、手動で ON に切り替えると、前のステートメントが属するトランザクションも暗黙的にコミットされます。
あるいは、LOCK TABLES や UNLOCK TABLES などのロックステートメントを使用すると、前のステートメントが属するトランザクションが暗黙的にコミットされます。

データをロードするステートメント

たとえば、LOAD DATA ステートメントを使用してデータをバッチでデータベースにインポートすると、前のステートメントが属するトランザクションも暗黙的にコミットされます。

MySQLレプリケーションに関するいくつかの記述

START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO などのステートメントを使用すると、前のステートメントが属するトランザクションも暗黙的にコミットされます。

その他の発言

ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET などのステートメントを使用すると、前のステートメントが属するトランザクションも暗黙的にコミットされます。

トランザクションセーブポイント

コンセプト

トランザクションに対応するデータベースステートメントにいくつかのドットを配置することで、ROLLBACK ステートメントを呼び出すときに元の場所に戻るのではなく、どのポイントにロールするかを指定できます。
トランザクションセーブポイントを使用すると、複雑なトランザクション操作を実行するときに、エラーが発生した場合に、一晩で解放前の時点に戻るのと同じように、元の状態にロールバックすることを心配する必要がありません。

構文を使用する

1. SAVEPOINT セーブポイント名; //セーブポイントをマーク
2. ROLLBACK TO [SAVEPOINT] セーブポイント名; //セーブポイントにロールバックする
3. RELEASE SAVEPOINT セーブポイント名; // 削除

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントを更新し、balance=balance-20 に設定します。ここで、id は 1 です。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> セーブポイントアクション1;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

mysql> アカウントから * を選択します。
+----+--------------+---------+
| ID | 名前 | 残高 |
+----+--------------+---------+
| 1 | 老王の妻 | 60 |
| 2 | ラオ・ワン | 30 |
+----+--------------+---------+

mysql> アカウントを更新し、残高をbalance+30に設定します。IDは2です。
クエリは正常、1 行が影響を受けました (0.01 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> rollback to action1; //action1 セーブポイントにロールバック クエリ OK、影響を受けた行数 0 (0.00 秒)

mysql> アカウントから * を選択します。
+----+--------------+---------+
| ID | 名前 | 残高 |
+----+--------------+---------+
| 1 | 老王の妻 | 60 |
| 2 | ラオ・ワン | 30 |
+----+--------------+---------+

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL トランザクション操作スキルのまとめ」、「MySQL インデックス操作スキルのまとめ」、「MySQL よく使用される関数のまとめ」、「MySQL ログ操作スキルのまとめ」、「MySQL ストアドプロシージャスキルのまとめ」、および「MySQL データベースロック関連スキルのまとめ」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

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