🍣

[SQL] トランザクションまとめ

に公開
SQL
初心者
idea

はじめに

こんにちは。
プログラミング初心者wakinozaと申します。
Java勉強中に調べたことを記事にまとめています。

十分気をつけて執筆していますが、なにぶん初心者が書いた記事なので、理解が浅い点などあるかと思います。
間違い等あれば、指摘いただけると助かります。

記事を参考にされる方は、初心者の記事であることを念頭において、お読みいただけると幸いです。

記事のテーマ

  • SQLを勉強中なので、学んだことをまとめています

目次

1. トランザクション
2. ACID特性
3. トランザクションを実施
4. 同時実行の副作用
5. トランザクション分離レベル

1. トランザクション

開発者は、DBMS（データベース管理システム：Database Management System）を利用することで、安全で確実なデータ操作と一貫性のあるデータ管理を実現します。
しかし、常に正しいSQL文を送っても、必ずデータが正しく操作されるとは限りません。
もし、停電など予期せぬトラブルで処理が中断した場合や、同時に複数の処理が実行された場合にデータに矛盾が生じる可能性があります。

DBMSには、このような事態を防ぐための仕組みが用意されています。
その1つが「トランザクション」です。

DBMSに対して複数のSQL文を送る際、指定の複数のSQL文をひとかたまりとして扱うように指示することができます。
このかたまりを「トランザクション」といいます。
トランザクションを指定することで、予期せぬトラブルで処理が中断した場合にデータに矛盾が生じることを防ぐことができます。
また、トランザクションの分離レベルを設定することで、同時に複数の処理が実行された場合の矛盾を制御することができます。

DBMSは、トランザクションに含まれているすべてのSQL文について、必ず「すべての実行が完了している」か「1つも実行されていない」か、どちらかの状態になるように制御します。
具体的には、トランザクション中のSQLの実行は、トランザクションのSQLが全て完了するまでは、 未確定として処理結果を保留します。 そして、トランザクション中のSQL文が全て終了した段階で、処理を確定します。
もしトランザクションの処理中に異常が発生した場合は、すでに実行済みの未確定の処理の実行が取り消されます。
これにより、処理が途中で中断し、データが不正確になるという問題を解決することができるのです。

2. ACID特性

DBMSがトランザクションとして複数のSQL文を扱う際、データの一貫性と信頼性を保証するために満たすべき性質が４つあります。
この4つの性質をまとめて、「ACID特性」と呼びます。

特性 内容
Atomicity（原子性） トランザクション内の処理は、全て実行されるか、全く実行されないかのどちらかであること
Consistency（一貫性） トランザクションが完了した際、データベースの制約（整合性）が保たれていること。
Isolation（隔離性） 複数のトランザクションが同時に実行されても、それぞれが独立して実行されているように見えること
Durability（永続性） コミットされたトランザクションの結果は、システム障害が発生しても失われないこと

1章で紹介した、「トランザクションのすべてのSQLが必ず「すべての実行が完了している」か「1つも実行されていない」か、どちらかの状態になるように制御する」という性質は、「原子性」の保証を意味しています。

3. トランザクションを実施

トランザクションを使うための指示は、以下の通りです。

指示 内容
BEGIN この指示以降のSQL文を１つのトランザクションとして扱うことを宣言する。
COMMIT この指示までのSQL文を１つのトランザクションとして扱い、処理を確定する。
ROLLBACK この指示までのSQL文を１つのトランザクションとして扱い、処理を取り消す

トランザクションを指定する場合は、SQL文に、トランザクションの開始（BEGIN）と終了（COMMIT）を記述します。

実は、多くのDBMSではSQL文が実行するたびに自動的に処理を確定する「自動コミットモード」がオンになっています。
そのため、トランザクションを明示的に指示しなければ、処理を未確定のまま保留にしたり、未確定の処理を取り消すことができません。
DBMSの設定によっては自動コミットモードを解除することも可能ですが、SQL文でBEGINを使用する方式も一般的です。
「BEGIN」を実行することで、次に「COMMIT」か「ROLLBACK」までの間、一時的に自動コミットが解除されます。
処理が完了しCOMMITまたはROLLBACKを実行すると、再び自動コミットモードに戻ります。

トランザクションの例として、銀行口座間の送金処理などが挙げられます。
銀行口座の送金を実行する場合、送金元口座のお金を減らすSQL文と、送金先口座のお金を増やすSQL文の2つが必要となります。
もし、前半のSQL文を実行後に後半のSQL文で異常中断が発生した場合、送金先口座にお金が増えません。
その結果、送金したはずのお金が消えてしまいます。

このような事態が発生しないように、送金元口座のSQL文と送金先口座のSQL文をひとかたまりのトランザクションとして指定します。
トランザクションの指定をすると、「送金が完了した」か、「送金が実施されていない」かどちらかの状態であることが保証されます。
これにより、処理が途中で中断され、金額が異常になってしまう事態を避けられます。

銀行送金のコード例は以下の通りです。

BEGIN; -- トランザクションの開始

-- 1. A口座（ID=1）から1000円減らす
UPDATE 
  accounts 
SET 
  balance = balance - 1000 
WHERE 
  account_id = 1; 

-- 2. B口座（ID=2）に1000円増やす
UPDATE 
  accounts 
SET 
  balance = balance + 1000 
WHERE 
  account_id = 2; 

COMMIT; -- トランザクションの終了 処理の確定

もし、 明示的に処理を中断したい場合は、「COMMIT」を「ROLLBACK」に変更します。

4. 同時実行の副作用

トランザクションを指定することで、 処理が途中で中断されても、データが異常な状態になる事態を避けられます。
しかし、これだけでは、複数の処理が同時に実行された場合の矛盾を解消することはできません。

世の中で利用されている情報システムにおいては、1つのDBMSに対して多数のSQL文が送られています。
場合によっては、 同じ行を複数の利用者が同時に読み書きする状況も発生します。
同時実行の処理の順番によっては、データの一貫性が失われてしまう可能性があるのです。

処理の同時実行によって起こるデータの一貫性が失われてしまう副作用には、主に3つのケースがあります。

4-1. ダーティリード (Dirty Read)

コミットされていない未確定のデータを、他の処理が読み取ってしまう副作用です。
その後キャンセルされる可能性がある情報をもとに別の処理を行ってしまうため、データの一貫性が保てない可能性があります。

4-2. ノンリピータブルリード (Non-Repeatable Read)

あるテーブルに対して処理Aがデータを読み取った後、処理Bがデータを更新し、コミットしたとします。
処理Aがトランザクション中に、再度同じデータを読み取る必要があった場合、同じデータなのに１回目の読み取りと２回目の読み取りではデータが違うという状態になり、トランザクション内の処理の一貫性が損なわれます。

4-3. ファントムリード (Phantom Read)

ファントムリードは、ノンリピータブルリードと似ています。
あるテーブルに対して処理Aがデータを読み取った後、処理Bが新しい行を挿入したとします。
処理Aがトランザクション中に、 1回目の検索結果の行数に依存する処理を行う場合、行数が変わってしまうと問題となる可能性があります。

5.トランザクションの分離

では、 上のような同時実行の副作用を防ぐためにはどうすれば良いのでしょうか。
実はDBMSには、 あるトランザクションを実行する際に、他のトランザクションから影響を受けないようにそれぞれを分離することが可能です。
「トランザクションの分離」によって、複数のトランザクションを同時に実行していても、それぞれが独立して実行している場合と同じ結果になるように制御することができます。
この「それぞれが独立して実行しているように見える」という性質こそが、ACID特性の「隔離性」です。

DBMSは、「トランザクションの分離」を行うために、内部で「ロック」と呼ばれる仕組みを使います。
「ロック」とは、あるトランザクションが読み書きしている行に鍵をかけ、他のトランザクションから読み書きできないようする状態のことです。
トランザクションがコミットまたはロールバックをするとロックは解除され、他のトランザクションから読み書きが可能になります。

「ロック」は、データの一貫性を保つための重要な仕組みですが、 ロックが発生するとトランザクションに待ちの時間が生まれ、DBMSの動作が遅くなってしまうという欠点があります。

そのため、データの一貫性とパフォーマンス、双方のバランスを検討し、適切な分離レベルを設定することが求められます。
多くのDBMSには、トランザクションの隔離の度合いを「トランザクション分離レベル」として指定することができます。

一般的なトランザクション分離レベルは以下の通りです。

分離レベル ダーティリード ノンリピータブルリード ファントムリード
READ UNCOMMITTED 可能性あり 可能性あり 可能性あり
READ COMMITTED 発生しない 可能性あり 可能性あり
REPEATABLE READ 発生しない 発生しない 可能性あり
SERIALIZABLE 発生しない 発生しない 発生しない

デフォルトの分離レベルは、DBMSによって異なります。
Oracle、PostgreSQL、SQL Serverなどでは「READ COMMITTED」が、MySQLなどでは「REPEATABLE READ」がデフォルトとして設定されています。
求める性能に応じて、適切な分離レベルと設定することが必要になります。

まとめ

  • トランザクションは、複数のSQL文をひとかたまりの処理として扱い、全ての処理が完了するか、一つも実行されないかの状態を保証します

  • 明示的にトランザクションを開始するにはBEGIN、処理を確定するにはCOMMIT、取り消すにはROLLBACKを使用します

  • 複数の処理が同時に実行されることによるデータの不整合（ダーティリードなど）を防ぐため、トランザクション分離レベルを設定します

記事は以上です。
最後までお読みいただき、ありがとうございました。

参考情報一覧

この記事は以下の情報を参考にして執筆しました。

GitHubで編集を提案

Discussion