メインコンテンツまでスキップ

DB Transaction

Overview​

トランザクションについてのセクション
更新は単独のクエリで行われることは少なく、複数のクエリで連続的に行われることがほぼ。
更新の元データとしてSELECTを利用する場合は、それを含めて複数のクエリを一貫性のある形でひとまとまりにして扱う必要がある。
トランザクションとは、このような複数のクエリをひとまとまりにしたもの。

実験した内容​

2つのウィンドウを立ち上げる。そして片方で insert し COMMIT
もう片方もトランザクションがはじめており、その場合は select してもコミットの内容が見えない。トランザクションが終了してから見える。

DDLによる暗黙のコミット​

MySQL や Oracle では CREATE TABLE のようなDDL発行時に、暗黙のコミットが発行される。
そのため、1つのコネクションで行われた CREATE TABLE が成功すると、それ以降は他のコネクションでも参照できるようになる。

自動コミットの設定​

トランザクションの開始（「BEGIN TRANSACTION」や「START TRANSACTION」、「SET TRANSACTION」など）が明示的に指定されない場合に、トランザクションの区切りはどうなるかというと、次の2つのパターンがあります。

1.「1つのSQL文で1つのトランザクション」という区切りになる 2. ユーザがCOMMITまたはROLLBACKを実行するまでが、1つのトランザクションとなる

一般的なDBMSでは、どちらのモードも選択可能になっています。またデフォルト設定が自動コミットモードになっているDBMSにはMySQL、PostgreSQL、SQL Serverなどがあります。 今回の実習では①かつ②のAであったため、他のコネクションでもINSERT後、すぐにテーブルとデータが確認できたというわけです。

トランザクションの特性(ACID特性)​

4つの特性により定義されACID特性と呼ばれる

  1. Atomicity（原子性）
  2. Consistency（一貫性）
  3. Isolation（分離性もしくは隔離性、独立性）
    1. ロック制限
      1. 表全体
      2. ブロック単位
      3. 行単位
    2. シリアライザブル（DBMSの仕様）
      1. ダーティリード（ANSIが定義）
      2. リピータブルリード（ANSIが定義）
      3. ファントムリード（ANSIが定義）
  4. Durability（持続性）

Atomicity（原子性）​

「原子性」とは、データの変更（INSERT/UPDATE/DELETE）を伴う一連のデータ操作が「全部成功」するか「全部失敗」するかを保証する仕組み
トランザクションではすべての処理の後に COMMIT を発行して処理を確定する。
その場合にそれぞれのデータ操作は永続的となり、結果が失われないようになる。
また、このようにシステムが正常動作していた上でのエラーではなくクライアントからサーバへの通信が途絶えてしまったり、サーバがダウンしてしまった場合でも、COMMITされたもの以外はROLLBACKする仕組みになっている

Consistency（一貫性）​

データベースには、データベースオブジェクト（テーブルをはじめとするデータベース内に定義できるオブジェクト）に対して、各種整合性制約を付加できる。
これは、一連のデータ操作の前後でそれらの状態を保つことを保証する、すなわち「一貫性」を保つための仕組み

たとえばシステムに利用ユーザを登録する場合、そのユーザを一意に識別するために、連番をユーザに振って（ユーザ番号）、それに対してユニーク制約（一意制約）を設定した場合、重複するようなユーザ番号を格納することはできない。
これは複数のユーザが同時にユーザ番号を取得しようとした場合も同じ。

Isolation（分離性もしくは隔離性、独立性）​

トランザクション分離レベルと言われているもの
「分離性（隔離性、独立性）」とは、一連のデータ操作が、複数のユーザから同時に行われる際に、「それぞれの処理が矛盾なく行えることを保証する」こと。

ロックの単位には「表全体」「ブロック単位」「行単位」などがありますが、MySQLでトランザクション処理を行う場合は、主に行単位のロック機能を利用する。
具体的には、処理で SELECT〜FOR UPDATE を実行することにより、SELECTした行にロックがかかる。　　 すると、後続の処理は、そのロックが解放される（COMMITまたはROLL BACK、今回COMMIT）まで待たされ、正しく処理を継続できるという仕組み

この仕組みに関してはDBのテーブル型が関わってくる
InnoDB 型のテーブルは「MVCC」という仕組みで動作しているため、今回の例でユーザBが単純に値を参照する、といった場合には、SELECTにFOR UPDATE は不要であり、その場合読み取りはブロックされない。
そのため、更新するユーザが少数で、参照するユーザが多い場合には、ユーザの同時実行・並列実行性が高くなる

Image from Gyazo

シリアライザブル（Serializable:直列化可能）​

これを分離性のレベルとしてDBMS側で実装・提供しているものが「シリアライザブル（Serializable:直列化可能）」という仕様。
しかしながら、「シリアライザブル」の分離度では、常に同時に動作しているトランザクションは1つのイメージとなってしまい、パフォーマンス的に実用に耐えられない。
そのため、シリアライザブルから分離の厳格性を緩めて、シリアライザブル以外に、自分以外のトランザクションの影響を受けることを許容する3つのレベルが「ANSI」という規格団体によって定義されている。
それらの呼称をトランザクション分離レベルという

Image from Gyazo

トランザクション分離レベルに至るまでの歴史​

その当時の多くのデータベースでは、ロックをある単位（テーブル、ブロック、行）で取得する方式でトランザクションの分離性を担保していました。この場合、読込のタイミングによっては読込がブロックされる場合があり、例えば「不正確でもいいので、その瞬間の値のスナップショットがほしい」という場合でも、待たされてしまうことがありました。この場合、非コミット読取を利用すると、読込はブロックされず、その瞬間の大まかな値が知りたい場合には大変便利です。しかしMVCCを利用する場合、読取がブロックされることはないため、先ほどの例の場合でも非コミット読取が必要とされません。そのため、Oracle、PostgreSQLやFirebirdでは、非コミット読取はサポートされていないわけです。 MySQLも、それらと同様にMVCCをサポートしているため、通常非コミット読取を利用する必要はないわけです。

トランザクション分離レベル（Transaction Isolation Level）​

※どの分離レベルをサポートするかはDBMSによって変わる場合もある。以下は MySQL の場合

データベース管理システム（DBMS）の機能であり、DBレイヤーで制御されます。
ORM（Object-Relational Mapping）ツールやフレームワーク（たとえば、ActiveRecordなど）は、このDBレイヤーの機能を利用して分離レベルを指定し、管理しています。
トランザクション分離レベルはデータベースのトランザクションが他の同時実行トランザクションの影響をどの程度受けるかを定義する設定
データベース管理システム（DBMS）では、トランザクションの一貫性を保ちながら同時実行性を管理するために、異なる分離レベルが提供されている。 SQL標準では、4つの主要なトランザクション分離レベルが定義されています。それぞれの分離レベルは、データの一貫性とパフォーマンスのバランスを取るために異なるトレードオフを提供します。 トランザクション分離レベルは、データベースのトランザクションが他の同時実行トランザクションの影響をどの程度受けるかを制御します。分離レベルを適切に選択することで、データの一貫性とパフォーマンスのバランスを最適化することが可能。

  • READ COMMITTED: ダーティリードを防ぎますが、ファジーリードは防ぎません。
  • REPEATABLE READ: ファジーリードを防ぎますが、ファントムリードを防ぎません。
  • SERIALIZABLE: すべてのリードの問題（ダーティリード、ファジーリード、ファントムリード）を防ぎますが、もっとも制約が厳しく、パフォーマンスに影響を与える可能性があります。
  1. READ UNCOMMITTED（非コミット読み取り）
    1. 説明: 他のトランザクションがコミットしていない変更（ダーティリード）を読み取ることができます。あまり使われることがない
    2. 利点: 高い同時実行性とパフォーマンス。
    3. 欠点: データの一貫性が保証されないため、もっとも低い分離レベル。
  2. READ COMMITTED（コミット済み読み取り）
    1. 説明: 他のトランザクションがコミットした変更のみを読み取ることができます。ダーティリードは発生しません。
    2. 利点: データの一貫性がある程度保証される。
    3. 欠点: ファジーリード（同じクエリ内で異なる結果が返されること）が発生する可能性がある。
  3. REPEATABLE READ（再読み込み可能読み取り）
    1. 説明: トランザクション内で読み取ったデータが、トランザクション終了まで変更されないことを保証します。ダーティリードやファジーリードは発生しませんが、ファントムリード（範囲クエリが新しい行を返すこと）は発生する可能性があります。
    2. 利点: 高いデータ一貫性。
    3. 欠点: パフォーマンスが低下する可能性。
  4. SERIALIZABLE（直列化可能）
    1. 説明: もっとも高い分離レベルで、すべてのトランザクションがシリアルに実行されたかのように扱われます。ファントムリードも防止されます。
    2. 利点: 完全なデータ一貫性。
    3. 欠点: 同時実行性がもっとも低く、パフォーマンスに大きな影響を与える可能性。

各分離レベルは、以下の現象を制御するために使用される。

  • ダーティリード（Dirty Read）: 他のトランザクションがコミットしていない変更を読み取る。
  • ファジーリード（Non-repeatable Read）: 同じクエリを複数回実行した際に、異なる結果が返される。
  • ファントムリード（Phantom Read）: トランザクション内で範囲クエリを実行した際に、新しい行が挿入されることで異なる結果が返される。

使用例​

たとえば、Railsでトランザクション分離レベルを指定する場合は、以下のように設定する。
この設定により、トランザクションは SERIALIZABLE 分離レベルで実行され、完全なデータ一貫性が保証されます。

ActiveRecord::Base.transaction(isolation: :serializable) do
  # トランザクション内の処理
end

PostgreSQLでは、以下のようにトランザクション分離レベルを設定します：

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- トランザクション内の処理
COMMIT;

MySQLでは、以下のようにトランザクション分離レベルを設定します：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- トランザクション内の処理
COMMIT;

ダーティリード、リピータブルリード、ファントムリードをちゃんと理解してからトランザクション分離レベルを理解しよう

ダーティリード(Dirty Read)​

あるトランザクションがコミットされる前に、別のトランザクションからデータを読み出せてしまう現象。
たとえばユーザAが値を変更し、まだコミットしていない場合でもユーザBが変更後の値を読み出してしまうことを指す。
ユーザAが部屋の残数「10」であるレコードを「9」に変更した場合、コミット前でもユーザBがSELECTした結果が「9」になる確定前の「汚れた（Dirty）」データを読み出し（Read）てしまうことから付いた名前

別のトランザクションでコミットされていないデータが読み取れる現象

  1. トランザクションAでレコードを①から②にUPDATE（未コミット）
  2. トランザクションBでレコードをSELECTする
  3. トランザクションAをロールバックする
  4. トランザクションBで取得したデータは②となっている。

一番低いレベルでないと発生しない。これを許容するシステムをまだ見たことがない・・

ファジーリード(非再現リード,ノンリピータブルリード)：Fuzzy Read​

あるトランザクションが以前読み込んだデータを再度読み込んだとき、2回目以降の結果が1回目と異なる現象。
たとえば最初にユーザAが部屋の残数「10」を読み出し、その後ユーザBが値を「9」に変更しコミットも行ったとする。
続いて、ユーザAがSELECTを再度実行すると、最初にSELECTできた「10」ではなく、変更後の「9」が読み出されてしまう。
ユーザAが最初に読み出し（Read）た値「10」が、2回目以降のSELECTで保証されず曖昧（Fuzzy）になることから付いた名前。
DBMSのマニュアルでは「繰り返し不可能な読み出し」(Non Repeatable Read) と紹介されることもある

トランザクション分離レベル知る前はそういうもんじゃんとか思っていたんだけど、2つのトランザクションが隔離されてないので使ってる側で気にしないといけない。 その時点で隔離性から離れてるんだよね。

ファジーリードの説明​

  1. トランザクションAがレコードを SELECT する。たとえば、レコードの値が ① であるとします。
  2. トランザクションBが同じレコードを ① から ② に UPDATE し、その後 COMMIT します。
  3. トランザクションAが再度同じレコードを SELECT すると、その値は ② になっています。

何が問題か？​

ファジーリードは、トランザクションの一貫性と隔離性を損なうため、問題が発生します。
これを防ぐためには、適切なトランザクション分離レベルを設定することが重要。
とくに、REPEATABLE READ 以上の分離レベルを設定することで、ファジーリードを防止し、トランザクションが予期せぬ結果を取得するのを避けることができる。

1. 一貫性の欠如​

ファジーリードが発生すると、トランザクションAは同じクエリを2回実行したにもかかわらず、異なる結果を取得します。これは、データの一貫性を損なう原因となります。

  • 例: 銀行の口座残高を確認するシステムでは、最初に取得した残高が $100 で、その後に確認した際に $50 に変わっていた場合、システムは正しく機能しない可能性があります。

2. トランザクションの隔離性の欠如​

トランザクションの隔離性（Isolation）は、トランザクションが他の同時実行トランザクションの影響を受けないことを保証するための特性です。ファジーリードが発生する場合、この隔離性が維持されていません。

  • 例: トランザクションAは、データがトランザクションBによって変更される影響を受けています。これにより、トランザクションAが意図した通りにデータを扱うことができなくなります。

ファントムリード（Phantom Read）​

あるトランザクションを読み込んだとき、選択できるデータが現れたり消えたりする現象。
最初にユーザAが範囲検索（たとえば部屋の残数が10以上のホテル）を行い、3行のレコードを読み出したとする。
続いてユーザBがちょうどその範囲に収まるデータを1行INSERTし、コミットも行った。
続いてユーザAが再度同じSELECT文を実行すると最初にSELECTできた3行ではなく、選択されるレコード数が4行になる。
このように現れたり、消えたり（DELETEやUPDATEで消える）するデータが「幽霊（Phantom）」に似ていることから付いた名前

Durability（持続性）​

「持続性」は、一連のデータ操作（トランザクション操作）を完了（COMMIT）し、完了通知をユーザが受けた時点で、その操作が永続的となり結果が失われないことを示す。
これはシステムの正常時だけにとどまらず、データベースサーバやOSの異常終了、つまりシステム障害に耐えるということ。
MySQLを含め、多くのデータベースの実装では、トランザクション操作をハードディスクの上に「ログ」として記録し、システムに異常が発生したらそのログを用いて異常発生前の状態まで復旧することで持続性を実現している。

MVCC（Multiversion Concurrency Control）​

トランザクションの管理方法のひとつであり、データベースシステムが複数のトランザクションを同時に処理するための技術
MySQL（InnoDB型テーブル）は、現在DBMSの主流となっている MVCC（Multi Versioning Concurrency Control） という技術を用いている。
MVCCの利用により、MySQLは以下のような特性を持ちます。

  1. 「更新」と「読込」は互いにブロックしない（「読込」と「読込」もお互いブロックしない）
  2. 「読込」内容は分離レベルにより内容が変わる場合がある
  3. 「更新」の際はロックを取得する。ロックは基本的に行単位で取得し、トランザクションが終了するまで保持する。分離レベルやInnoDBの設定により、実際にロックする行の範囲が異なる場合がある
  4. 「更新」と「更新」は、後から来たトランザクションがロックを取得しようとしてブロックされる。一定時間待ち、その間にロックが取得できない場合には、ロック待ちタイムアウト（ロックタイムアウト）となる 5.「更新」した場合、更新前のデータを、UNDOログとして「ロールバックセグメント」という領域に持つ。
    このUNDOログは用途が2つあり、1つは更新したトランザクションのロールバック時に更新前に戻す、もう1つは複数のトランザクションから分離レベルに応じて、対応する更新データを参照するために利用される（同じ行を更新するたびにUNDOログが作成され、同じ行に対して複数のバージョンが存在することにより1と2を実現している）

Image from Gyazo

ロックタイムアウトとは​

「更新」と「参照」は互いにブロックしませんが、「更新」と「更新」がぶつかった場合には、後から来た更新がロック待ちの状態になる。
ロック元がいつロックを解放するのかを知る手段は、ロック解放を待っている側にはわからないので、一般的なDBMSではロックを待つ・待たない、さらに待つ場合にはどの程度待つか（秒数指定or無限に待つ）を設定できるようになっている。
MySQLの場合は、「innodb_lock_wait_timeout」というシステム変数で以下のように設定できる。
ただし、「waitしない」という設定はできず、有効なのは「1」（秒）以上

なおロック待ちでタイムアウトが出た場合、DBMSによりロールバックされる単位が違うことがあるため注意が必要。

  • そのトランザクション全体をロールバックしてしまうもの
  • クエリだけをロールバックするもの

MySQLでは、ロック待ちでタイムアウトが出た場合、デフォルトの動作でロールバックされるのはエラーが出たクエリのみ。

デットロック​

襷掛けで起こる
ロックタイムアウトの場合、一定時間待つことにより状況が改善される（＝ロック元がロックを解放する）可能性がありますが、デッドロックの場合は状況が改善される可能性がない。
そのため、一般的なDBMSの場合、これを独自に検知してデッドロックを報告する。
MySQLの場合も、デッドロックが起きた場合にはすぐに検知し、システムに対して影響が少ないほうのトランザクションをトランザクション開始時点までロールバックする。
デッドロックは一般的なデータベースでは起きる可能性があり、すべてなくすことはできない。そのためアプリケーション側では常に、トランザクションがデッドロックを起こしてロールバックされた場合に、トランザクションを再実行できるようなつくりにしておく必要がある。

Image from Gyazo

DBMS デットロック対策​

  1. トランザクションを頻繁にコミットする。それによりトランザクションはより小さくなりデッドロックの可能性を下げる
  2. 決まった順番でテーブル（そして行に）アクセスするように心がける。たとえば先のデッドロックの例ではTransaction Aはテーブルa→テーブルbの順番にアクセスし、Transaction Bはテーブルb→テーブルaの順にアクセスしているが、それをどのトランザクションからもテーブルa→テーブルbのように同じ順番でアクセスする
  3. 必要がない場合にはロック読取（SELECT ... FOR UPDATEなど）の利用を避ける
  4. クエリによるロック範囲をより狭いものにしたり、ロックの粒度をより小さなものにする。たとえば行ロックが利用できる場合は利用する。MySQLの場合はトランザクションの分離レベルを可能であればREAD COMMITTEDにする（InnoDBのデフォルト分離レベルはRepeatable Read）
  5. 1つのテーブルの複数の行に対して、複数のコネクションから順不同に更新するとデッドロックが起こりやすくなる。同時に多くのコネクションから更新してデッドロックが頻繁に起こるようであれば、テーブル単位のロックを取得して更新を直列化するようにすると同時実行性は落ちるが、デッドロックは回避でき、処理のトータルではよいケースもある ●MySQL（InnoDB）の対策
  6. テーブルに適切なインデックスを追加して、クエリがそれを利用するようにする。インデックスが利用されない場合、必要最小限の行レベルロックではなく、スキャンした行全体にロックが取得されることになる

デットロックは起こるため、上限回数を決めたリトライ処理やトランザクションの再実行を行ったりする。

デットロック確認​

  • クライアント側ではロールバックされたトランザクションのエラーとして
  • サーバー側ではエラーログやコマンドで確認できる

やってはいけないトランザクション処理​

オートコミット​

MySQLのデフォルト値では、新しいコネクションはみなオートコミットが「オン」になっています。「オートコミット」とは、クエリごとにコミットする設定です。この設定はMySQLコマンドラインクライアントのように、対話型のツールを使って簡単なクエリの実行・テストをやるような場合には便利ですが、通常のアプリケーションのロジックを実行するには、コミットの負荷が高すぎます。 一定数以上の更新（UPDATE/INSERT/DELETE）を行う処理や、トランザクションの機能（複数のクエリをまとめてコミット・ロールバックする、複数回発行するSELECTで曖昧読取やファントムを防ぐ）ような場合は、適切な単位とトランザクション分離レベルでトランザクションを利用しオートコミットを利用しないようにしましょう。

ロングトランザクション​

長いトランザクション（ロングトランザクション）は、データベースのトランザクションの同時実行性や、リソースの有効利用性を低下させます。更新を含むトランザクションは、同じテーブル行を更新しようとする他のトランザクションをブロックし、それが長時間に及ぶと、ブロックされたトランザクションをタイムアウトさせます。また、このロックとブロックが「たすき掛け」で起こるとデッドロックが起こり、長時間をかけたトランザクションのどちらかが、すべてロールバックされることになります。

大量処理を1つのトランザクションで行う​

大量の更新処理を1つのトランザクションで行うと、トランザクションとしてその大量の更新処理をロールバックするために、大量のUNDOログをトランザクション終了まで保持する必要があります。 UNDOログは不要になった時点で領域は解放され、再利用されますが、OSのファイルシステムとしてのサイズは削減されません。そのため、見かけ上ムダに大きなサイズになる場合があります。このようなことを防ぐためには、大量処理は適当なサイズのトランザクションに分割して行うことをオススメ。
たとえば、「新規のテーブルにデータを初期ロードするような場合は、1万件ごとにコミットを入れる」などです。

「何もしないトランザクション」に留意する​

本当に何もしないトランザクションであれば問題ないですが、たとえば一度テーブルをSELECTしてから、何もせずトランザクションを開いたままにしておくとどうなるでしょうか。MVCCの項でも少し触れましたが、同じテーブルに対して更新を行った場合、このテーブルのリピータブルリードを保つために、UNDOログがずっと保持されたままになってしまいます。このようなことはなるべく避けてください。

処理能力以上のトランザクション数​

トランザクションでは、何らかのロックを伴って処理を行っています。そのため、このロックが他のトランザクションの処理を妨げないものであればよいのですが、トランザクションの実行が他のトランザクションのロックに妨げられると、ロックタイムアウトやデッドロックの確率が増えパフォーマンスの低下につながります。 うまく機能するコネクション数や同時実行数の上限をどの程度に設定すべきかは、システムの要件（更新が多いのか、検索が多いのか）やハードウェア性能にも左右されるため、一般的な閾値（しきいち）は負荷試験を行って測定するしかありません。 MySQLでは、データベースサーバのコネクション数上限を設定する「max_connections」というシステム変数がありますので、これで調整可能です。余談ながら、これは遊園地やテーマパークで行う入場制限に似たイメージです（お客さんが一定時間待って遊具やアトラクションに乗れる程度に入場者数を調整する）。

に最終更新