OSS-DB　Silver

OSS-DBは色々なものがありますが、商用データベースとの連携に優れ、エンタープライズシステムでも多くの導入実績があるのが「PostgreSQL」です。
OSS-DB Silverは「PostgreSQL　11」に関する能力を問う資格です。

試験で問われる範囲は以下の通りです。

これらから50題出題され、65%程度の正答率で合格となります。

今回は開発/SQLの中からトランザクションについて説明します。

Sec 10. トランザクション

概念

• BEGIN〜COMMITまでなど、一連の処理のまとまり
• ACID特性
  • Atomicity : 実行完了か全くされないかの二択
  • Consistency : DBの整合性
  • Isolation : トランザクションは別のトランザクションからの影響を受けない
  • Durability : トランザクションにより変更されたデータは確実に保持される

制御コマンド

• BEGIN , START TRANSACTION : トランザクションの開始

• COMMIT, END, END TRANSACTION : 処理の確定

• ROLLBACK, ABORT : トランザクションを開始する前の状態に戻す
  ※クラッシュした場合はROLLBACKは発行されないが、実行中のトランザクション処理は取り消される

• トランザクション中でSQLなどがエラーになった場合は、その時点でトランザクションはABORT扱いとなり、以降の処理はABORTまたはROLLBACKが実行されるまで、SQLを実行しても無視される

• SAVE POINT : トランザクション中に部分的なロールバックをしたい場合に使用

  SAVEPOINT セーブポイント名;
  ROLLBACK TO 戻る時点のセーブポイント名;
  # 不要になったセーブポイントの削除
  RELEASE SAVEPOINT セーブポイント名;

トランザクション分離性

• 分離レベルの違いにより、トランザクションが別のトランザクションから受ける影響の度合いが異なる

• 影響

  • ダーティリード : 自身のトランザクションから、別のトランザクションの未コミットな状態の挿入／更新／削除結果が見えること
  • ファジーリード：自身のトランザクションから、別のトランザクションのコミットされた更新／削除結果が見えてしまうこと
  • ファントムリード：自身のトランザクションから、別のトランザクションのコミットされた挿入結果が見えてしまうこと
  • 直列化異常：複数のトランザクションのコミット結果が、トランザクションを一つずつ重ならないように実行した場合と比較して、どのような順序を仮定しても違う結果となり、一貫性のない状態となってしまうこと。

• 分離レベル

  • Read uncommitted : ダーティリード、ファジーリード、ファントムリード、直列化異常が起こる
    ＊PostgreSQLで設定しても以下のRead committedと同じ振る舞いになる（対応していない
  • Read committed : ファジーリード、ファントムリード、直列化異常が起こる
    ＊PostgreSQLでのデフォルト
  • Repeatable read : ファントムリード、直列化異常が起こる
  • Serializable : ダーティリード、ファジーリード、ファントムリード、直列化異常のいずれも起こらない

  →分離レベルを強くすると、データベースの整合性に矛盾が生じる可能性が大きくなるため、エラーを発生して、片方のトランザクションが自動的にROLLBACKされる

• 分離レベルの設定

  # セッション単位
  SET default_transaction_isolation TO '分離レベル';
  # トランザクション単位(COMMITやABORTで元の分離レベルに戻る)
  # BEGINコマンドの直後に実行しないとエラーになる
  SET transaction_isolation TO '分離レベル';
  BEGIN ISOLATION LEVEL 分離レベル;
  START TRANSACTION ISOLATION LEVEL 分離レベル;

• 現在の分離レベルの確認

  SHOW default_transaction_isolation;
  SHOW transaction_isolation;

ロック

• 更新処理時の矛盾を避けるための排他処理

• 行ロック

  • 排他ロック：あるレコードの排他ロックがある処理によって取得されている場合、別の処理からはそのレコードに対して排他もしくは共有ロックを取得することができない
  • 共有ロック：排他ロックと衝突するが、共有ロック同士は衝突せず、同じレコードに複数の共有ロックを取得することができる

  →行ロックによって、更新処理の最中に別の更新処理で上書きされることはなくなる

• 排他ロックはUPDATEやDELETEなどのレコードの更新や削除処理で自動的に取得される

• FOR UPDATE(排他ロック)やFOR SHARE(共有ロック)で取得も可能

  SELECT 列名 FROM テーブル名 WHERE 条件 FOR UPDATE;
  SELECT 列名 FROM テーブル名 WHERE 条件 FOR SHARE;

• テーブルロック：テーブル単位での排他／共有ロックを明示的に取得する場合に使用する

  LOCK TABLE テーブル名 [IN ロックモード];

  • EXCLUSIVE MODE : ロック対象テーブルのSELECTのみを許す。INSERT/ UPDATE/ DELETEなどの処理のほか、VACUUMやANALYZEのメンテナンス処理もブロックする
  • ACCESS EXCLUSIVE MODE : ロック対象のテーブルの全ての処理をブロックする。ロックモードを省略した場合は、このモードとなる。
  • ALTER TABLEなどや一部のメンテナンスコマンドでは、暗黙的なテーブルの排他ロックが取得される。
  • ALTER TABLE ではDMLやSELECT文などのアクセスは待機させられるが、どのSQLが待たされるかは変更する項目に依存するため、全てのSQLが常に待たされるわけではない。

• デッドロック : 2つの処理が違いにお互いの処理の終了を待っている状態

  • デッドロックになった場合は、どちらかのトランザクションがロールバックしない限り、永久にロック待ちとなる。PostgreSQLでは、デッドロックを検知したトランザクションをアボートし、デッドロック状態から自動的に復活する。