Relational Database Transaction

Overview

リレーショナルデータベースでのトランザクションに関する情報をまとめたセクション。
更新は単独のクエリのみで構成されることは少なく、複数のクエリを連続的に行うことがほとんど。
また更新元のデータとして 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などがある。

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

トランザクションは4つの特性により定義されACID特性と呼ばれる。

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

Atomicity（原子性）

「原子性」とは、データの変更（INSERT/UPDATE/DELETE）を伴う一連のデータ操作が「全部成功」するか「全部失敗」するかを保証する仕組み。
トランザクションではすべての処理の後に COMMIT を発行して処理を確定する。
その場合にそれぞれのデータ操作は永続的となり、結果が失われないようになる。

ヒント

また、このようにシステムが正常動作していた上でのエラーではなくクライアントからサーバへの通信が途絶えてしまったり、サーバがダウンしてしまった場合でもCOMMITされたもの以外はROLLBACKする仕組みになっている。

Consistency（一貫性）

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

注記

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

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

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

ヒント

複数のトランザクションが順次実行された場合と同じ結果が得られる状態。
例：並行でない状態で実行される場合の結果を考え、「それと同じなら保証できている」という考え方。
これを分離性のレベルとしてDBMS側で実装・提供しているのがシリアライザブル（直列化可能）という仕様。

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

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

Durability（持続性）

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

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

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

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

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

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

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

ヒント

MySQLデフォルトのトランザクション分離レベルは「リピータブルリード」（RR：Repeatable Read）
再読み込み可能読み取り

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

READ UNCOMMITTED（非コミット読み取り）
1. 説明
  他のトランザクションがコミットしていない変更（ダーティリード）を読み取ることができる。あまり使われることがない
2. 利点
  高い同時実行性とパフォーマンス。
3. 欠点
  データの一貫性が保証されないため、もっとも低い分離レベル。
READ COMMITTED（コミット済み読み取り）
1. 説明
  他のトランザクションがコミットした変更のみを読み取ることができる。ダーティリードは発生しません。
2. 利点
  データの一貫性がある程度保証される。
3. 欠点
  ファジーリード（同じクエリ内で異なる結果が返されること）が発生する可能性がある。
REPEATABLE READ（再読み込み可能読み取り）
1. 説明
  トランザクション内で読み取ったデータが、トランザクション終了まで変更されないことを保証する。
  ダーティリードやファジーリードは発生しないが、ファントムリード（範囲クエリが新しい行を返すこと）は発生する可能性がある。
2. 利点
  高いデータ一貫性。
3. 欠点
  パフォーマンスが低下する可能性。
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;

リードアンコミッテッド（非コミット読取）」

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

リードコミッテッド(RC: Read Committed)

リードコミッテッド（コミット済み読込）は、クエリを発行した時点でコミットされているデータを読み込む。
同じクエリを複数回実行すると、その間他トランザクションにコミットされる場合があり、その場合は、最新のクエリの実行開始時点でコミットされたデータを読むことになる。

リピータブルリード(RR: Repeatable Read)

リピータブルリード（リピート可能な読込）は、まず初回クエリを発行した時点でコミットされているデータを読み込む。
この時点では、リードコミッテッドと同じ。
同じクエリを複数回実行すると、初回の読込内容で結果セットが返される。
複数回のクエリの間に、他トランザクションがコミットしていても、その内容は反映されない。

ヒント

更新をするトランザクション自身は、トランザクション分離レベルやCOMMIT/ROLLBACKに関わらず自分が行った更新を即座に見ることができる。

分離レベルの緩和によって起こる3つの現象

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

分離レベルが1つ緩くなるにつれてシリアライザブルでは起こらなかった現象が起こり得る。

ダーティリード(Dirty Read)

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

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

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

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

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

トランザクションAがレコードを SELECT する。たとえば、レコードの値が ① であるとする。
トランザクションBが同じレコードを ① から ② に UPDATE し、その後 COMMIT する。
トランザクション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）」に似ていることから付いた名前。

MVCC（Multiversion Concurrency Control）

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

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

ロックタイムアウトとは

「更新」と「参照」は互いにブロックしないが、「更新」と「更新」がぶつかった場合には、後から来た更新がロック待ちの状態になる。
ロック元がいつロックを解放するのかを知る手段は、ロック解放を待っている側にはわからないので、一般的なDBMSではロックを待つ・待たない、さらに待つ場合にはどの程度待つか（秒数指定or無限に待つ）を設定できるようになっている。
MySQLでは、ロック待ちでタイムアウトが出た場合、デフォルトの動作でロールバックされるのはエラーが出たクエリのみ。

ヒント

MySQLの場合は、「innodb_lock_wait_timeout」というシステム変数で以下のように設定できる。
ただし、「waitしない」という設定はできず、有効なのは「1」（秒）以上。

注意

ロック待ちでタイムアウトが出た場合、DBMSによりロールバックされる単位が違うこともあるため注意。
トランザクション全体をロールバックしてしまうものと、クエリだけをロールバックするものがある。
※MySQLではロック待ちでタイムアウトが出た場合、デフォルトの動作でロールバックされるのはエラーが出たクエリのみなので注意！！

トランザクション全体をロールバックするためには以下の方法がある。

タイムアウトエラーの後、明示的にROLLBACKを発行する
innodb_rollback_on_timeoutシステム変数を設定する

デットロック

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

ヒント

そのためアプリケーション側では常に、トランザクションがデッドロックを起こしてロールバックされた場合に、トランザクションを再実行できるようなつくりにしておく必要がある。

DBMS デットロック対策(頻度を下げるため)

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

ヒント

MySQL（InnoDB）の対策
テーブルに適切なインデックスを追加して、クエリがそれを利用するようにする。インデックスが利用されない場合、必要最小限の行レベルロックではなく、スキャンした行全体にロックが取得されることになる

備考

「デッドロックが起こるのはDBMSのバグ」とか「アプリのつくり上、トランザクションの再実行はできない」と言ってはばからないプログラマは、驚くことに実在する。
しかし、クライアント・サーバ型のDBMSでは、「タイムアウト」「デッドロック」「コネクション・ネットワークエラー」「一時的な状態エラー」などは起こりうるものと自覚し、上限回数を決めたリトライなどで処理やトランザクションの再実行をしトラブルの解決に努めなくてはならない。

MySQL（InnoDB）のロックとインデックスの関係

InnoDBはMVCCと行レベルロックを採用
InnoDBはMVCC（Multi-Version Concurrency Control）を使用し、行レベルロックを基本としている。
そのため、特定のレコードに対して SELECT ... FOR UPDATE などのロックをかける場合、通常はインデックスを利用した行にロックがかかる。
インデックスがない場合はテーブルスキャン（全件走査）が発生
WHERE句の条件で適切なインデックスがないと、MySQLはテーブル全体をスキャンすることになります（フルテーブルスキャン）
その場合、必要な行だけでなく、スキャンされたすべての行に対してロックがかかる可能性。
フルテーブルスキャン時のロックの影響
SELECT ... FOR UPDATE や SELECT ... LOCK IN SHARE MODE の場合、インデックスが利用されないと、スキャンした行全体がロックされる。
つまり、不要な行にまでロックが波及し、並行実行の性能が大きく低下する可能性がある。

-- インデックスがある場合
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- `id` にPRIMARY KEYやUNIQUE INDEXがあると、該当する1行のみロック

-- インデックスがない場合（フルテーブルスキャン）
SELECT * FROM users WHERE name = 'Alice' FOR UPDATE;
-- `name` にインデックスがない場合、テーブル全体をスキャンし、全行ロックの可能性あり

「インデックスが適切に設定されていないと、クエリが不要な行までロックしてしまう可能性がある」という点は正しい。
ただし、すべてのクエリで必ず発生するわけではなく、特にFOR UPDATEやLOCK IN SHARE MODEなどのロックを伴うクエリで顕著になる。
適切なインデックスを設定することで不要なロックを回避し、並行処理の効率を向上させることができる。

デットロック確認

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

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

オートコミット

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

ロングトランザクション

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

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

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

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

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

注記

本当に何もしないトランザクションであれば問題ない（SELECTなどもしない）

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

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

排他制御（楽観ロック・悲観ロック）の基礎

排他制御の長さ

排他制御している時間が長ければ長いほど、システムの利便性が下がってしまう。そのため、不都合や不整合が発生しない範囲で可能な限り短くすることが鉄則。

排他制御の方式

楽観ロック（楽観的排他制御）
悲観ロック（悲観的排他制御）

楽観ロック（楽観的排他制御）

楽観ロックとは、めったなことでは他者との同時更新は起きないであろう、という楽観的な前提の排他制御。
データそのものに対してロックは行わずに、更新対象のデータがデータ取得時と同じ状態であることを確認してから更新することで、データの整合性を保証する方式。
楽観ロックを使用する場合は、更新対象のデータがデータ取得時と同じ状態であることを判断するために、Versionを管理するためのカラム（Versionカラム）を用意する。
更新時の条件として、データ取得時のVersionとデータ更新時のVersionを同じとすることで、データの整合性を保証できる。

レースコンディション(Race Condition)

レースコンティションとは、複数の処理が同時に実行されることで処理の順番によって予期しない不具合が発生する状態のことを指す。
データベースにおいては複数のトランザクションが同じデータを読み書きすることでデータの不整合が発生する。

特に、データの整合性を確保するためにACID特性（Atomicity, Consistency, Isolation, Durability）のIsolation（分離性）が重要となる。

レースコンディションを防ぐための解決策

レースコンディションはトランザクションの「Isolation（分離性）」の問題に関係する
適切なトランザクションの管理やロックを使うことで防げる
データの一貫性（Consistency）を保つために、ACIDの特性を意識することが重要

注意

データベースを扱う上ではよく出てくる問題、特に高トラフィックのアプリや金融系システムでは注意が必要

1. トランザクションの適切な分離レベルを設定

SERIALIZABLEやREPEATABLE READなどの高い分離レベルを設定する

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ここで安全に処理
COMMIT;

2. ロック（Locking）の活用

SELECT ... FOR UPDATE などでデータをロックして排他制御する。

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

3. 楽観的ロック（Optimistic Locking）

データにバージョン管理を加えて、他のトランザクションが変更していたらエラーにする。

UPDATE accounts
SET balance = balance - 500, version = version + 1
WHERE id = 1 AND version = 2;

4. 行レベルロック（Row-level Locking）

データベースによっては行単位でロックできるので、テーブル全体をロックせずにパフォーマンスを保つ

レースコンディションの例（銀行口座の送金処理）

例えば、あるユーザーの銀行口座に1000円があるとする。

2つのプロセスが同時に処理を実行プロセスA（別のユーザーが送金）：500円を引き出すプロセスB（ATMが引き出しを処理）：700円を引き出す
読み取りタイミングの問題プロセスAとBの両方が口座残高を取得（1000円）プロセスAは500円を引き出して更新（1000円 → 500円）プロセスBは700円を引き出して更新（1000円 → 300円になるはずが…）実際にはプロセスBが最初に読んだ1000円から700円を引いてしまい、残高は300円に更新される本来500円しか引き出せないはずの口座が「マイナス200円」になった状態。 不整合が発生する

行ロックを使っても解決しないケース（編集ページの問題）

行ロック (SELECT ... FOR UPDATE) では解決できない（DB内の処理に限られるため）
楽観的ロック（バージョン管理）を使うのが一般的
リアルタイム通知や悲観的ロックも有効（Google Docs方式）

ヒント

「最後に保存した人のデータが正しい」ではなく、「競合が起きたらどうするか？」を設計するのが重要。

例えば、Webアプリで記事の編集を考えた場合の例。

Aが編集ページを開く（データを読み込む）
Bも編集ページを開く（同じデータを読み込む）
Aが編集を終えて保存（データが更新される）
Bが編集を終えて保存（Aの編集内容が上書きされる）

このように、後から保存したBの編集がAの編集を消してしまうという問題が起きる。
これもレースコンディションの一種であり、行ロック SELECT ... FOR UPDATE を使っても、ユーザーが編集画面を開いている間にロックするわけではないので防げない。

この問題を防ぐ方法は以下となる。

1. 楽観的ロック（Optimistic Locking）

「誰が最後に編集したかをチェックして、競合したらエラーを出す」方式
バージョン番号や更新日時を使う。

編集データを取得する。

SELECT id, title, content, version
FROM articles
WHERE id = 1;

この時点のversionを記録しておく（例: version = 3）

保存時にversionをチェックする。

UPDATE articles
SET title = '新しいタイトル', content = '新しい内容', version = version + 1
WHERE id = 1 AND version = 3;

version = 3だった場合のみ更新される
もしAが保存した後にBが保存すると、Bのversion = 3は古くなるため、更新に失敗する
エラーが出た場合、競合していることをユーザーに知らせる

2. リアルタイム通知

WebSocketやポーリングを使って、他のユーザーが編集中かどうかを通知
Google Docsのように、「誰かが編集中」の表示を出す

3. ロック機能（悲観的ロック）

Aが編集画面を開いたら、その間Bは編集できない」方式
データベースのロックではなく、アプリ側で制御
例:
- 記事の「編集中」フラグを立てる

UPDATE articles SET is_locked = TRUE WHERE id = 1;

一定時間操作がなければ解除。

UPDATE articles SET is_locked = FALSE WHERE id = 1;

Bが編集しようとしたら「Aが編集中です」と警告。

Overview​

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

自動コミットの設定​

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

Atomicity（原子性）​

Consistency（一貫性）​

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

Durability（持続性）​

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

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

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

使用例​

リードアンコミッテッド（非コミット読取）」​

リードコミッテッド(RC: Read Committed)​

リピータブルリード(RR: Repeatable Read)​

分離レベルの緩和によって起こる3つの現象​

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

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

1. 一貫性の欠如​

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

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

MVCC（Multiversion Concurrency Control）​

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

デットロック​

DBMS デットロック対策(頻度を下げるため)​

MySQL（InnoDB）のロックとインデックスの関係​

デットロック確認​

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

オートコミット​

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

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

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

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

排他制御（楽観ロック・悲観ロック）の基礎​

排他制御の長さ​

排他制御の方式​

楽観ロック（楽観的排他制御）​

レースコンディション(Race Condition)​

レースコンディションを防ぐための解決策​

1. トランザクションの適切な分離レベルを設定​

2. ロック（Locking）の活用​

3. 楽観的ロック（Optimistic Locking）​

4. 行レベルロック（Row-level Locking）​

レースコンディションの例（銀行口座の送金処理）​

行ロックを使っても解決しないケース（編集ページの問題）​

1. 楽観的ロック（Optimistic Locking）​

2. リアルタイム通知​

3. ロック機能（悲観的ロック）​