検索

ホームページ  >  記事  >  データベース  >  データベーストランザクションとMySQLトランザクションの概要

データベーストランザクションとMySQLトランザクションの概要

coldplay.xixi
coldplay.xixi転載
2020-12-30 16:33:522499ブラウズ

mysql チュートリアルデータベース トランザクションと MySQL トランザクションの概要

データベーストランザクションとMySQLトランザクションの概要

推奨 (無料): mysql チュートリアル

トランザクション機能: ACID

ビジネスの観点から見ると、一連のデータベース操作は、4 つの特性を維持するために必要です:

  • 原子性: トランザクションは分割できない最小の作業単位とみなされ、トランザクション全体のすべての操作が正常に送信される必要があります。トランザクションの場合、一部の操作のみを実行することはできません。
  • 整合性: データベースは常に、ある整合性状態から別の整合性状態に遷移します。以下にバンクの例を示します。
  • 分離: 一般に、あるトランザクションによって行われた変更は、最終的にコミットされるまで他のトランザクションには表示されません。ここで「一般的に言えば」ということに注意してください。これについては、トランザクション分離レベルで後述します。
  • 耐久性: トランザクションがコミットされると、その変更はデータベースに永続的に保存されます。この時点でシステムがクラッシュしても、変更されたデータは失われません。 (永続性セキュリティとリフレッシュ ログ レベルの間には一定の関係もあります。異なるレベルは異なるデータ セキュリティ レベルに対応します。)

ACID をより深く理解するために、例として銀行口座振替を取り上げます。

START TRANSACTION;SELECT balance FROM checking WHERE customer_id = 10233276;UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;COMMIT;
  • 原子性: 完全にコミットする (10233276 の当座預金残高が 200 減り、貯蓄残高が 200 増える) か、完全にロールバックする (両方のテーブルの残高は変更されません)。
  • 一貫性: この例の一貫性は、トランザクションがまだコミットされていないため、3 行目まで実行した後、4 行目より前でデータベース システムがクラッシュするため、200 元が消えないという事実に反映されています。 。
  • 分離: 1 つのトランザクションの操作ステートメントを他のトランザクションのステートメントから分離できるようにします。たとえば、トランザクション A は行 3 の後と行 4 の前で実行され、トランザクション B はこの時点で当座預金残高を照会します。トランザクション A と B は互いに分離されているため、トランザクション A で差し引かれた 200 元がまだ表示されます (口座の金額は変更されません)。トランザクション A がコミットされるまで、トランザクション B はデータの変更を監視できません。
  • 永続性: これは理解するのが簡単です。
  • トランザクションの分離は、ロック、MVCC などによって実現されます (MySQL ロックの概要)
  • トランザクションのアトミック性、一貫性、耐久性は、トランザクション ログによって実現されます (以下を参照)

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

同時トランザクションによって引き起こされる問題

    更新の喪失: 2 つ以上のトランザクションが同じ行を選択し、最初に選択された値に基づいて行を更新すると、各トランザクションは他のトランザクションの存在を認識していないため、エラーが発生します。更新の喪失の問題が発生します。 - 最後の更新は、他のトランザクションによって行われた更新を上書きします。たとえば、2 人の編集者が同じ文書の電子コピーを作成するとします。各編集者は独自にコピーを変更し、変更されたコピーを保存して元の文書を上書きします。最後に自分の変更のコピーを保存した編集者は、別の編集者によって加えられた変更を上書きします。この問題は、あるエディターがトランザクションを完了してコミットするまで、あるエディターが同じファイルにアクセスできない場合に回避できます。
  • ダーティ読み取り: トランザクションがレコードを変更しています。トランザクションが完了してコミットされる前に、このレコードのデータは不整合な状態にあります。この時点で、別のトランザクションもそれを読み取ります。同じレコードが制御されていない場合、2 番目のトランザクションが「ダーティ」データを読み取り、それに基づいてさらなる処理を実行すると、コミットされていないデータの依存関係が発生します。この現象は明確に「ダーティ リーディング」と呼ばれています。
  • Non-Repeatable Reads: トランザクションは、一部のデータを読み取った後、以前に読み取ったデータを再度読み取りますが、読み取ったデータが変更されているか、一部のレコードが削除されていることがわかります。この現象を「非反復読み取り」と呼びます。
  • ファントム リード: トランザクションが同じクエリ条件に従って以前に取得したデータを再度読み取ると、他のトランザクションによってクエリ条件を満たす新しいデータが挿入されていることがわかります。この現象は「ファントム リード」と呼ばれます。

ファントム リーディングと反復不可能なリーディングの違い:

  • 非反復読み取りの焦点は変更です。同じトランザクション、同じ条件下で、初めて読み取られたデータは 2 回目に読み取られたデータとは異なります。 (他のトランザクションが途中で変更を送信したため)
  • ファントム読み取りの焦点は追加または削除です。同じトランザクション、同じ条件で、1 回目と 2 回目に読み取られるレコードの数は違う。 。 (途中で挿入/削除をコミットしている他のトランザクションがあるため)

トランザクションの同時処理によって引き起こされる問題の解決策:

  • 「更新の喪失」は一般に、完全に避けるべきものです。ただし、更新消失の防止はデータベーストランザクションコントローラだけで解決できるものではなく、アプリケーション側で更新対象のデータに必要なロックを付与する必要があるため、更新消失の防止はアプリケーション側で行う必要があります。

  • 「ダーティ リード」、「反復不可能な読み取り」、および「ファントム リード」は、実際にはデータベースの読み取り一貫性の問題であり、データベースが特定のトランザクション分離メカニズムを提供することで解決する必要があります。

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

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

SQL 標準では、4 種類の分離レベルが定義されています。各レベルは、トランザクション内で行われた変更、つまりトランザクション内およびトランザクション間で表示される変更と表示されない変更を指定します。一般に、分離レベルが低いほど高い同時実行性がサポートされ、システムのオーバーヘッドが低くなります。

#レベル 1: コミットされていない読み取り (コミットされていないコンテンツの読み取り)

すべてのトランザクションは他のコミットされていないトランザクションの実行結果を確認できます
  • この分離レベルは他のレベルと比べてパフォーマンスがあまり優れていないため、実際のアプリケーションではほとんど使用されません
    • ##このレベルによって引き起こされる問題は - ダーティ リード: コミットされていないデータが読み取られた
#レベル 2: コミットされた読み取り

これは、ほとんどのデータベース システムのデフォルトの分離レベルです (ただし、MySQL のデフォルトではありません)

  • これは分離の単純な定義を満たしています。トランザクションはトランザクションによってコミットされたもののみを確認できます。

  • この分離レベルの変更に関する問題は、次のとおりです。反復不可能な読み取り: 反復不可能な読み取りとは、同じトランザクションでまったく同じ選択ステートメントを実行すると、異なる結果が表示される可能性があることを意味します。この状況の考えられる理由は次のとおりです:

  • 新しいコミットによるクロス トランザクションがあり、データ変更が発生します;

  • aデータベースは複数のインスタンスによって操作されており、同じトランザクションの他のインスタンスがインスタンスの処理中に新しいコミットを行う可能性があります

    • ##レベル 3: 反復読み取り (再読み取り可能)

これは MySQL のデフォルトのトランザクション分離レベルです。データを同時に読み取るときに、同じトランザクションの複数のインスタンスに同じメッセージが表示されるようにします。データ行

    このレベルで考えられる問題 - ファントム読み取り: ユーザーが特定の範囲のデータ行を読み取ると、別のトランザクションがその範囲に新しい行を挿入します。ユーザーがこの範囲のデータ行を読み取ると、新しい「ファントム」が見つかります。 " rows
  • #InnoDB と Falcon ストレージ エンジンは、マルチバージョン同時実行制御 (MVCC、Multiversion Concurrency Control) メカニズムを通じてファントム読み取りの問題を解決します。InnoDB ファントム読み取りの問題も、ギャップ ロックを通じて解決されます
  • ##マルチバージョン同時実行制御:
Mysql のほとんどのトランザクション ストレージ エンジン実装 単純な行レベルのロックではありません。同時実行性の向上を考慮して、Oracle と PostgreSQL を含むマルチバージョン同時実行制御 (MVCC) が同時に実装されるのが一般的です。ただし、実装は異なります。

MVCC は、特定の時点でのデータのスナップショットを保存することによって実装されます。言い換えれば、実装にどれだけ時間がかかっても、それぞれのモノから得られるデータには一貫性があるということです。 オプティミスティック同時実行制御とペシミスティック同時実行制御に分かれます。

MVCC の仕組み:

InnoDB の MVCC は、レコードの各行の背後に 2 つの非表示列を保存することによって実装されます。これら 2 つの列の 1 つは行の作成時刻を格納し、もう 1 つは行の有効期限 (削除時刻) を格納します。もちろん、保存されるのはリアルタイムではなく、システムのバージョン番号です。新しいトランザクションが開始されるたびに、システムのバージョン番号が自動的に追加されます。トランザクション開始時のシステム バージョン番号は、トランザクションのバージョン番号として使用され、比較のためにレコードの各行のバージョン番号をクエリするために使用されます。

REPEATABLE READ 分離レベルでの MVCC の動作:

  • SELECT

#InnoDB は、次の条件に従ってレコードの各行をチェックします。

  1. InnoDB は、バージョンが古いデータ行のみを検索します。現在のトランザクション バージョン。そのため、トランザクションによって読み取られた行が、トランザクションの開始前に存在するか、トランザクション自体によって挿入または変更されていることを確認してください。
  2. 行の削除されたバージョン番号は、未定義であるか、それより大きいです。現在のトランザクションのバージョン番号。これにより、トランザクションの読み取りが保証されます。フェッチされた行は、トランザクションの開始前に削除されていません。
#上記 2 つの条件を満たす行のみがクエリされます

#INSERT
  • InnoDB は、新しく挿入された各行の行バージョン番号として現在のシステム バージョン番号を保存します。

DELETE
  • InnoDB は、削除された行ごとに現在のシステムのバージョン番号を行削除識別

UPDATE
  • InnoDB は、挿入された新しい行の行バージョン番号として現在のシステム バージョン番号を保存します。また、現在のシステムのバージョン番号を削除識別子として元の行に保存します。

これら 2 つのバージョン番号を保存すると、ほとんどの操作でロックが必要なくなります。これにより、データ操作がシンプルになり、パフォーマンスが向上し、複雑な要件に必要な行のみが確実に読み取られるようになります。欠点は、レコードの各行に追加の記憶域スペース、より多くの行チェック、および追加のメンテナンス作業が必要になることです。

MVCC は、COMMITTED READ (読み取り送信) と REPEATABLE READ (反復読み取り) の 2 つの分離レベルでのみ機能します。

MVCC は行レベルのロックの一種であると考えられますが、多くの場合ロック操作が回避され、オーバーヘッドが低くなります。データベースごとに実装メカニズムは異なりますが、ほとんどのデータベースではノンブロッキング読み取り操作 (読み取りにロックは必要なく、非反復読み取りとファントム読み取りを回避できます) が実装され、書き込み操作は必要な行のみをロックします (書き込みは必ずロックする必要があります)。ロックされている場合）、そうでない場合は、異なるトランザクションによる同時書き込みによりデータの不整合が発生します）。

レベル 4: シリアル化可能

これは最高の分離レベルです
  • これは、次の方法でファントム読み取りの問題を解決します。トランザクションが互いに競合しないように強制的に順序付けします。つまり、読み取られた各データ行に共有ロックが追加されます。 MySQL ロックの概要
  • このレベルでは、多数のタイムアウトとロック競合が発生する可能性があります

分離レベルの比較

各データベースでは、上記の 4 つの分離レベルが完全に実装されていない可能性があります。例:

    Oracle では、次の 2 つの標準のみを提供しています。独自定義の読み取り専用分離レベルの提供に加えて、シリアル化可能な分離レベル;

    • ISO/ANSI SQL92 で定義されている上記 4 つの分離レベルのサポートに加えて、SQL Server は 1 つの分離レベルもサポートします。 「スナップショット」分離レベルと呼ばれますが、厳密には、MVCC を使用して実装されたシリアル化可能な分離レベルです。

    • MySQL は 4 つの分離レベルすべてをサポートしていますが、特定の実装ではいくつかの特性があります。たとえば、一部の分離レベルでは、MVCC の一貫した読み取りが使用されますが、場合によっては使用されません。 . .

    • Mysql は、setトランザクション分離レベル コマンドを実行することで分離レベルを設定できます。新しい分離レベルは、次のトランザクションの開始時に有効になります。例: セッション トランザクション分離レベルの読み取りコミットを設定します。

#トランザクション ログトランザクション ログは、トランザクション効率の向上に役立ちます。 :

トランザクション ログを使用すると、ストレージ エンジンはテーブル データを変更するときにメモリ コピーを変更するだけで済み、その代わりに、ハード ディスクに保存されているトランザクション ログに変更動作を記録します。変更されたデータ自体はディスクに永続化されます。

    トランザクション ログは追加メソッドを使用するため、ログの書き込み操作は、ディスク上の複数の場所でヘッドを移動する必要があるランダム I/O とは異なり、ディスク上の小さな領域内での順次 I/O になります。したがって、トランザクション ログ方式の方が比較的高速です。
  • トランザクション ログが永続化された後、メモリ内の変更されたデータをバックグラウンドでゆっくりとディスクにフラッシュして戻すことができます。
  • データの変更がトランザクション ログに記録されて永続化されているが、データ自体がディスクに書き戻されておらず、システムがクラッシュした場合、ストレージ エンジンは変更されたデータのこの部分を自動的に復元できます。再起動するとき。
  • 現在、ほとんどのストレージ エンジンはこの方法で実装されています。通常、これを先行書き込みログ (Write-Ahead Logging) と呼びます。データを変更するには、ディスクに 2 回書き込む必要があります。

#Mysql におけるトランザクション実装の原則

トランザクションの実装は、データベースのストレージ エンジンに基づいています。ストレージ エンジンが異なれば、トランザクションのサポート レベルも異なります。 mysql のトランザクションをサポートするストレージ エンジンには、innoDB と NDB が含まれます。 innoDB は、mysql のデフォルトのストレージ エンジンです。デフォルトの分離レベルは RR (Repeatable Read) であり、マルチバージョン

同時実行制御を通じて RR の分離レベルをさらに一歩進めています

(MVCC、Multiversion Concurrency Control) は非反復読み取り問題を解決し、ギャップ ロック (つまり、同時実行制御) はファントム読み取りの問題を解決します。したがって、innoDB の RR 分離レベルは実際にはシリアル化レベルの効果を実現し、比較的良好な同時実行パフォーマンスを維持します。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。说到事务日志，不得不说的就是redo和undo。

1.redo log

在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

在系统启动的时候，就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。如下一个简单示例：

记录1：

记录2：

记录3：

记录4：

记录5：

2.undo log

undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

以下是undo+redo事务的简化过程

假设有2个数值，分别为A和B,值为1，2

1. start transaction;

2. 记录 A=1 到undo log;

3. update A = 3；

4. 记录 A=3 到redo log；

5. 记录 B=2 到undo log；

6. update B = 4；

7. 记录B = 4 到redo log；

8. 将redo log刷新到磁盘

9. commit

在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化。若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。

所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。

Mysql中的事务使用

MySQL的服务层不管理事务，而是由下层的存储引擎实现。比如InnoDB。

MySQL支持本地事务的语句：

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}
  • START TRANSACTION 或 BEGIN 语句：开始一项新的事务。
  • COMMIT 和 ROLLBACK：用来提交或者回滚事务。
  • CHAIN 和 RELEASE 子句：分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
  • SET AUTOCOMMIT 可以修改当前连接的提交方式， 如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

事务使用注意点：

  • 如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock
    tables 被执行。
  • 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事
    务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
  • 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。
  • 在事务中可以通过定义 SAVEPOINT（例如：mysql> savepoint test; 定义 savepoint，名称为 test），指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚
    不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的 SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT， 删除后的 SAVEPOINT， 不能再执行 ROLLBACK TO SAVEPOINT命令。

自动提交（autocommit）：
Mysql默认采用自动提交模式，可以通过设置autocommit变量来启用或禁用自动提交模式

  • 隐式锁定

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

  • 显式锁定

InnoDB也支持通过特定的语句进行显示锁定（存储引擎层）：

select ... lock in share mode //共享锁 select ... for update //排他锁

MySQL Server层的显示锁定：

lock table和unlock table

（更多阅读：MySQL锁总结）

MySQL对分布式事务的支持

分布式事务的实现方式有很多，既可以采用innoDB提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下innoDB对分布式事务的支持。

MySQL 从 5.0.3 开始支持分布式事务，当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

如图，mysql的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

  • 应用程序：定义了事务的边界，指定需要做哪些事务；
  • 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
  • 事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

  • 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
  • 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

分布式事务（XA 事务）的 SQL 语法主要包括：

XA {START|BEGIN} xid [JOIN|RESUME]

虽然 MySQL 支持分布式事务，但是在测试过程中，还是发现存在一些问题：
如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。

如果分支事务在执行到 prepare 状态时，数据库异常，且不能再正常启动，需要使用备份和 binlog 来恢复数据，那么那些在 prepare 状态的分支事务因为并没有记录到 binlog，所以不能通过 binlog 进行恢复，在数据库恢复后，将丢失这部分的数据。

如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果此时分支事务已经执行到 prepare 状态， 那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。
总之， MySQL 的分布式事务还存在比较严重的缺陷， 在数据库或者应用异常的情况下，
可能会导致分布式事务的不完整。如果应用对于数据的完整性要求不是很高，则可以考虑使
用。如果应用对事务的完整性有比较高的要求，那么对于当前的版本，则不推荐使用分布式
事务。                                                         

以上がデータベーストランザクションとMySQLトランザクションの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：
この記事はlearnku.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。
前の記事：SQL文でテーブルを作成する文とは何ですか？次の記事：SQL文でテーブルを作成する文とは何ですか？

関連記事

続きを見る