株式会社コーソル

コーソルDatabaseエンジニアのブログ

技術ブログ

主要RDBMS製品の比較 – 同時実行制御, トランザクション分離レベル

Oracle ACE Proの渡部です。 主要なRDBMS製品を比較します。

  • 大枠を整理することが最大の目的です。細かい例外事項や拡張機能は適宜記載を割愛しています。
  • 2022年9月時点の最新バージョンをベースに記載していますが、記載内容にバージョン依存は少ないはずです。
  • 時間ができた時に随時追記予定です。
  • もし誤りを見つけた場合は、優しく教えていただけると嬉しいです。→ https://twitter.com/wrcsus4 or ryota.watabe at cosol dot jp

「主要RDBMS製品の比較」ページ一覧

立場の表明

  • コーソルはデータベース関連製品の販売およびプロフェッショナルサービス提供を行っている営利企業です。
  • https://cosol.jp にある全てのコンテンツは、情報提供に加えて、コーソルの認知度向上、コーソルの営利活動の促進を目的としています。

Database Performance Analyzer DPA

コーソルのDbvisitサービス

著者について

データベースにおける同時実行制御の概観

データベースにおいて「並行して実行される変更処理をどのように扱うか？」が同時実行制御です。

端的に言うと、変更および参照処理が並行して実行されたときに発生しがちな以下のようなあまり嬉しくない動作（アノマリー, Anomaly）に対処するのか／しないのか、するならどうやって対処するか、が主な論点となります。

  • 変更途中のデータを他のユーザーに見せない
  • 他のユーザーによる上書き変更により、一度加えた変更が意図せず失われないようにする
  • 同じデータ参照処理が複数回実行されたとき、一貫性を持つデータ（同じ結果）を戻すこと
  • など

対処する／しないという方針にはいくつかのバリエーションがあり、それがトランザクション分離レベルとして示されます。ただし、トランザクション分離レベルで示されることはあくまでも「大枠」であり、詳細な動作の把握には、同時実行制御を実現する手段における動作（ロックをどの範囲で取るか、取らないか？など）含めた理解が必須なことには注意してください。

対処する手段として、主に以下が使用されます。

  1. MVCC (MultiVersion Concurrency Control, マルチバージョン型 同時実行制御)を用いた一貫性読取り (Consistent Read)
    • ユーザー毎に適切なバージョンのデータを戻すことで、変更中データなどの不適切なデータを含まない一貫性を持つデータの読み取りを実現する
    • 一貫性を持つ、特定バージョンのデータ（の集合）を「スナップショット」と呼ぶことがあります。
    • データの読み取り時にロックをかけません（かける必要がありません）。
    • 一貫性が適用される範囲に、文レベルとトランザクションレベルがあります。
  2. ロック (Locking Read/Write、変更対象データのロック)
    • 対象のデータにロックをかけることで、同じ対象に対して他のユーザーが並行してデータ操作を実行できないようにする
    • 変更系のSQLを実行するとと自動的にロックがかけられる。SELECT FOR UPDATEを使用して意図して（手動で）ロックをかけることも可能。テーブル設計や要件によっては、手動ロックが必要となるケースも多い。
    • ロックにモードがある。共有モードと排他モードが一般に使用されるが、製品により異なる部分も多い。
  3. 変更競合の検出
    • 隔離性／分離性（≒指定したトランザクション分離レベル）に反する変更が実行されたこと（されそうなこと）を検出し、エラーを発生させる
    • アプリケーション側でリトライすることを期待した位置づけの動作。

本記事の記載範囲とスタンス、留意点

  • データ（行）にかかわる同時実行制御についてのみ説明します。
    • データベースにおける同時実行制御の対象はデータ（行）だけではありません。オブジェクト定義や、メモリ上に展開された様々な管理情報やキャッシュも同時実行制御の対象に含まれますが、この記事では記載しません。
  • 同時実行制御は非常に複雑な論点であるため、データに限定したとしても、網羅的に論じるのは困難です。各RDBMSの比較を網羅的にすると、さらに困難になります。よって、実務上重要または私が興味深く感じた点に限定して記載します。
    • もし、私が触れていない範囲で面白い論点があれば、ぜひ教えてください！（時間があれば・・・頑張ります）
  • 個人的に興味深く感じた点について、デフォルト以外のトランザクション分離レベルにも触れていますが、デフォルト以外の分離レベルを実務で使用することを勧める意図はありません
    • 実務的には、デフォルトの分離レベル（もしくはREAD COMMITTED）を基本に、必要に応じてSELECT FOR UPDATEを補完的に使用することになるでしょう。

各製品がサポートするトランザクション分離レベルとデフォルト

トランザクション分離レベルは、同時実行制御における隔離（分離、Isolation）の度合いを示す「目安」や「大枠」のようなものです。

アノマリーを発生させる／させない により（ざっくり）定義づけられます。ANSI/ISO SQL標準では、READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE という4つのトランザクション分離レベルを定義しています。

  • Oracle: READ COMMITTEDとSERIALIZABLEに対応しています。デフォルトはREAD COMMITTEDです。READ ONLYトランザクションにも対応しています。
  • MySQL: ANSI/ISO SQL標準で定義された4つの分離レベルに対応しています。デフォルトはREPEATABLE READです。
  • PostgreSQL: READ COMMITTED、REPEATABLE READとSERIALIZABLEに対応しています。デフォルトはREAD COMMITTEDです。
    READ UNCOMMITTEDも指定可能ですが、READ COMMITTEDとして扱われます。
  • MS SQL Server: ANSI/ISO SQL標準で定義された4つの分離レベルに対応しています。デフォルトはREAD COMMITTEDです。MVCC（行のバージョン管理）を使用したSNAPSHOT分離レベルおよびREAD_COMMITTED_SNAPSHOTにも対応しています。

なお、繰り返しになりますが、トランザクション分離レベルで示されることはあくまでも「大枠」であり、詳細な動作の把握には、同時実行制御を実現する手段における動作（ロックをどの範囲で取るか、取らないか？など）含めた理解が必須なことに注意してください。

また、実務上は、各製品のデフォルトのトランザクション分離レベルを使用することになるはずです。よって、この記事でも各製品のデフォルトのトランザクション分離レベルを中心に記載します。

MVCCを用いた一貫性読取り(consistent read) と共有ロック

  • 変更と参照を同時に実行した場合の振る舞いを見ます。
    • 各製品のデフォルトのトランザクション分離レベルを基本とします。
  • この振る舞いから、以下を説明する意図です。
    • SQL Server特有の動作である参照時の共有ロック
    • MVCCを用いた一貫性読取り(Consistent Read)
      • 文レベルの一貫性読取り
      • トランザクションレベルの一貫性読取り

SQL Server 参照時の共有ロック

SQL Serverのデフォルトの分離レベルはREAD COMMITTEDです。SQL ServerのREAD COMMITTED分離レベルの特徴は以下のとおりです。

  • 通常のSELECTが共有ロックを要求します。
  • このため、通常のSELECTが変更SQLの排他ロックと競合しえます。競合した場合、SELECTが待機させられます。

SQL ServerのREAD COMMITTED分離レベルで、通常のSELECTが共有ロックを要求するのは、MVCCを使用しないためと整理できます。

Oracle ロックなしの一貫性読取り

Oracleのデフォルトの分離レベルもREAD COMMITTEDですが、SQL ServerのREAD COMMITTED分離レベルとは動作が異なります。特徴は以下のとおりです。

  • MVCCがあるため、通常のSELECTがロックを要求しない。よって、変更SQLの排他ロックとと競合しない。
  • データを変更するトランザクションの実行中（コミット前）に、通常のSELECTを実行すると、変更前のデータを得る。
  • MVCCによる読取り一貫性が「文レベル」であるため、トランザクションTx1のコミット後に実行された通常のSELECTはTx1による変更後のデータを得る
    • 同一トランザクション内で実行された複数回の通常のSELECTが、異なるデータを得る場合がある。

OracleのREAD COMMITTED分離レベルにおける読取り一貫性が「文レベル」である点に注意してください。トランザクションレベルの読取り一貫性を実現したい場合は、SERIALIZABLE分離レベルか、読取り専用トランザクションを使用する必要があります。

OracleのSERIALIZABLE分離レベルの特徴は以下のとおりです。

  • 読取り一貫性が「トランザクションレベル」であるため、Tx1コミット後に実行された通常のSELECTであっても、Tx2開始時点でコミット済みのデータを得る
  • 不適切な変更が並行して実行された状況を検知し、エラーとする仕組みが存在する（後述）

Oracleの読取り専用(READ ONLY)トランザクションの特徴は以下のとおりです。

  • 読取り一貫性が「トランザクションレベル」であるため、Tx1コミット後に実行された通常のSELECTであっても、Tx2開始時点でコミット済みのデータを得えます。
  • トランザクション内で変更処理を実行できない。

SQL Server 2005以降のMVCC拡張

先に説明したSQL ServerのREAD COMMITTED分離レベルは、通常のSELECTが共有ロックを要求するため、変更SQLの排他ロックと競合します。競合した場合、SELECTが待機させられるため、同時実行性に劣ります。

この問題に対処するため、SQL Server 2005からMVCC（SQL Serverマニュアルでの記載は「行のバージョン管理」）が導入されました。

  • 対象のデータベースについて、READ_COMMITTED_SNAPSHOTをあらかじめ有効化しておく必要があります。
    • ALTER DATABASE [DB名] SET READ_COMMITTED_SNAPSHOT ON;
  • READ_COMMITTED_SNAPSHOTが有効化されると、READ COMMITTED分離レベルの動作がREAD_COMMITTED_SNAPSHOTに変更されます。
    • Oracleの動作を想定したアプリケーションの移植容易性を強く意識しているように思えます。
  • MVCCがあるため通常のSELECTがロックを要求せず、Tx1の変更SQLと競合しない
  • 一貫性が文レベルである点も含め、OracleのREAD COMMITTED分離レベルの動作とよく似ています。
    • Oracleとの互換性、Oracleの動作を想定したアプリケーションの移植容易性を強く意識しているように思えます。

MVCC（行のバージョン管理）を用いたもう１つの動作（トランザクション分離レベル）が、SNAPSHOT分離レベルです。

  • 対象のデータベースについて、ALLOW_SNAPSHOT_ISOLATIONをあらかじめ有効化しておく必要があります。
    • ALTER DATABASE [DB名] SET ALLOW_SNAPSHOT_ISOLATION ON;
  • トランザクション分離レベルに SNAPSHOT を指定します。
  • 読取り一貫性が「トランザクションレベル」であるため、Tx1コミット後に実行された通常のSELECTであっても、Tx2開始時点でコミット済みのデータを得る

変更対象へのロック / Locking Read

MVCCを用いた一貫性読取り(Consistent Read)は、読取り時にロックをかけないため同時実行性に優れていますが、要件によっては、読取り時にロックをかけたい場合もあります (Locking read)。

Locking readの実現方法は以下のとおりです。

読取り時にロックをかけたい場合の例として、すこし粗いものですが、以下のような処理を並行して実行する状況を考えます。

  1. SELECT * FROM t1 FOR UPDATE; で現在の値を得る
    1. で得た結果と、アプリケーションに実装された何らかのビジネスロジックに基づき、変更後の値を決定する
      • ここでは簡単のため、アプリケーションに実装された何らかのビジネスロジックの内容は、「元の値を1.1倍する」とします。
      • 実務や例示の意義を考えると、もっと複雑なビジネスロジックが適切ですが、わかりにくくなるため、簡単なビジネスロジックとします。
  3. UPDATE t1 SET num = [変更後の値]; で新しい値に更新する

Oracleを例にしますが、SELECT FOR UPDATEで読取り時にロックをかけると、想定した結果を得ることができます。

逆に、読取り時にロックをかけないと、想定した結果を得られません。読取り時のロックが重要である点を理解いただけるかと思います。

RDBMS製品の比較という観点で、SQL ServerにおけるデフォルトのCOMMITTED READ分離レベルでの動作にも触れておきます。

  • 通常のSELECTは、共有ロックを要求する。共有ロック同志は競合しない（互換性がある）ため、上記のOracle SELECTの実行例と同様の振る舞いになる。
    • ただし、共有ロックと排他ロックは競合するため、シナリオが異なるとOracleの通常のSELECTと異なる振る舞いに可能性がある点に注意。
  • 行ロックを行うSELECT ... WITH(UPDLOCK,ROWLOCK) は、排他ロックを要求する。排他ロック同志は競合する（互換性がない）ため、上記のOracle SELECT FOR UPDATEの実行例と同様の振る舞いになる。

後勝ち / 変更競合の検出

データを読み取るときにLocking Readを使っていれば、問題は一切発生しないわけではありません。Locking Readを使うだけでは問題になりうるパターンの1つに「後勝ち」があります。

なお、「後勝ち」が発生したからと言って、すべからく問題になるというわけではありません。問題になるかどうかはアプリケーションの要件に依存します。

また、Locking Readを使うだけでは問題になりうるパターンには、「後勝ち」の他にも多数あります。

「後勝ち」に代表される、いくつかの問題となりうる振る舞いを防止するため、OracleおよびPostgreSQLは、同一のデータに変更が加えられた場合、それを検知してエラーとしていずれかの処理を失敗させる機能を持っています。

  • OracleはSERIALIZE分離レベルで、同一のデータが更新されようとした場合、後から実行された処理をORA-08177エラーで失敗させます。

  • PostgreSQLはREPEATABLE READ以上の分離レベルで、同一のデータが更新されようとした場合、後から実行された処理を"ERROR: could not serialize access due to concurrent update"エラーで失敗させます。

  • OracleのCOMMITTED READ分離レベルには、SERIALIZE分離レベルのような変更競合を検出する機能はありません。いわゆる「後勝ち」の動作となります。

  • MySQLにはOracleおよびPostgreSQLのような変更競合を検出する機能はありません。トランザクションをSERIALIZE分離レベルで実行した場合でもいわゆる「後勝ち」の動作となります。

PostgreSQLの強力な変更競合検出 / 直列可能性とは

PostgreSQLには、SERIALIZE分離レベルで機能する非常に強力な変更競合検出機能があります。個人的に興味があったのでここでも説明しておきます。また、直列可能性に関する説明も試みます。

なお、ここで実行例にはPostgreSQLマニュアルに記載されたものを使用します（少し不自然な例ですが、そこには目をつぶることにします）。

処理内容は、以下を並行して実行するものです。

  1. クラス1のデータの合計を得て、結果をクラス2のデータとしてINSERTする
    • SELECT SUM(value) FROM t2 WHERE class = 1;
    • INSERT INTO t2 VALUES(2, [合計] );
  2. クラス2のデータの合計を得て、結果をクラス1のデータとしてINSERTする
    • SELECT SUM(value) FROM t2 WHERE class = 2;
    • INSERT INTO t2 VALUES(1, [合計] );

まず、最初に変更競合検出機能がないと発生する問題を示すため、PostgreSQLのREPEATABLE READ分離レベルでの実行例を示します。

  • psqlの機能を使って、SELECTした結果をINSERTに引き渡しています。
    • SELECT での AS sum_val ... \gset
    • INSERT での :sum_val

REPEATABLE READ分離レベルでの実行結果は、直列可能性に反した不適切なものです。 すなわち、トランザクションを順番に実行した場合に絶対に発生しない結果になっています。

同じ処理を、SERIALIZE分離レベルで実行すると、変更競合検出機能により、"ERROR: could not serialize access due to concurrent update"エラーで失敗します。これにより、直列可能性に反した不適切な結果になることを防いでいるわけです。

なお、同じ処理をOracleにおいてSERIALIZE分離レベルで実行してもエラーにはなりません。PostgreSQLの変更競合検出機能は、Oracleの変更競合検出機能よりも強力であると言えます。

MySQLのデフォルト分離レベルはREPEATABLE READ

ここで取り上げたRDBMS製品のうち、MySQLだけがトランザクション分離レベルのデフォルトがREPEATABLE READ です。他の製品のデフォルト分離レベルはREAD COMMITTEDです（SQL Serverの共有ロック獲得など、振る舞いの違いはありますが）。

MySQLにおけるREPEATABLE READ分離レベルの動作に関しては優れた情報が多数ありますので、ここではポイントの整理とそれらの情報の紹介のみにとどめます。

  1. 通常のSELECTは、トランザクションレベルの一貫性読取りとなる
    • →「consistent readでは」ファントムリードが発生しない
    • トランザクション開始後の最初の読み取りによってスナップショットが確立される
  2. SELECT ~ FOR UPDATE (locking read) と通常のSELECT(non-locking read / consistent read)で、得られる結果が異なることがある
  3. 範囲条件を指定したSELECT FOR UPDATEのロック範囲が変わり、共有ロック相当の動作となる

「主要RDBMS製品の比較」ページ一覧

[PR] オンプレミス＆クラウドのマルチDB製品に対応した性能管理ツールDPA

Database Performance Analyzer (DPA) は、オンプレミス＆クラウドに対応するデータベース性能監視／分析ツールです。

Database Performance Analyzer DPA

この記事で取り上げたRDBMS製品を含む、非常に多くのデータベース製品／サービスに対応しています。

  • Oracle Database
  • MS SQL Server
  • Sybase SAP ASE
  • IBM Db2
  • MySQL / MariaDB / Percona Server for MySQL
  • PostgreSQL / Enterprise DB
  • AWS
    • Amazon RDS for Oracle Database / SQL Server / MySQL / MariaDB / PostgreSQL
    • Amazon Aurora for MySQL / PostgreSQL
  • Azure
    • Azure SQL Database
    • Azure SQL Managed Instance
    • Azure SQL for PostgreSQL
    • Azure Database for MySQL / MariaDB
  • Google Cloud
    • Google Cloud SQL for MySQL / PostgreSQL / SQL Server

以下の特徴があり、導入しやすく有用な製品です。

  • 非常に低価格。課金単位は監視インスタンスの数で、1インスタンス14.7万円/年から（2023年1月時点）。
  • インストールが容易。DBサーバへのエージェント導入は不要。
  • オンラインデモサイトですぐに使用感を確認可能 → https://cosol.jp/techdb/2022/08/dpa_online_demo/
  • 待機時間を基礎とする性能分析（近年主流の性能分析メソッド）
  • 機械学習アルゴリズムに基づく異常検知機能(Anomaly Detection)

なぜコーソルからDatabase Performance Analyzer (DPA)を購入すべきなのか

コーソルはDatabase Performance Analyzer (DPA)の一次代理店で、Database Performance Analyzer (DPA)の製品販売を行います。 SIer様、販社様がDatabase Performance Analyzer (DPA)を販売および導入することも可能です。

Database Performance Analyzer DPA

コーソルはデータベースの技術力を強みとしています。なかでもOracle Database技術力は日本随一です。MySQL、PostgreSQL、MS SQL Serverの資格や実績を持つエンジニアも多数在籍しております。

独自のDPAナレッジを公開

DPAの導入や監視設定に関する手順をナレッジとして公開しています。評価版をご利用される際の参考にしていただけると幸いです。

多数のOracle関連書籍を執筆

ORACLE MASTER Platinum取得者数 No.1

  • 単年度ORACLE MASTER Platinum取得者数7年連続No.1

7年連続ORACLE MASTER Platinum取得者数No.1！ Oracle Certification Award 2020

[PR] コーソルのデータベース運用関連製品とサービス

コーソルでは、データベース運用を製品とサービスでご支援します。

Database Performance Analyzer （DPA）

Database Performance Analyzer （DPA）は、オンプレミスとクラウド上の多くのデータベース製品に対応したデータベース性能管理製品です。低価格であるため、非常に導入しやすいです。

自動SQLチューニング機能を持つToad

Database Performance Analyzer （DPA）で検出された問題SQLをチューニングする際に、Toad for Oracle / Toad for SQL Serverの SQL Optimizer機能を使用できます。

リモートDBAサービス

リモートDBAサービスはDB・運用の専門家がお客様のデータベースに対して 必要な時に必要な対応を行うリモート接続型運用保守サービスです。

データベース運用・保守なら常駐しないリモートDBA

時間制コンサルティングサービス

時間制コンサルティングサービスは”必要な時に” ”必要な時間だけ”契約できる 時間契約型のコンサルティングサービスです。

データベース コンサルティングなら時間制コンサルティング

関連記事

プロフィール

On7tWW6m1Ul4

渡部 亮太

・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数

カテゴリー

アーカイブ

一覧へ戻る