PostgreSQL トランザクション、カーソル、ロック

PostgreSQL

トランザクション

第7回 トランザクション

トランザクション内でエラーが発生した場合、ROLLBACKコマンドが実行されるまで全てのSQLはエラーとなる。
トランザクション内で構文エラーが起きた場合は、トランザクションの全ての処理が取り消される。
自動コミットがONになっている場合は、明示的にトランザクションを開始しない限り、SQLが処理された時点でその内容はコミットされる。
トランザクション内でSETコマンドを実行した場合、トランザクションがコミットされると変更したパラメータ値はセッション内で保存される。
また、トランザクションがロールバックされると、変更したパラメータ値はトランザクション開始時の値に戻る。

  • BEGIN, START TRANSACTION
  • COMMIT, END, END TRANSACTION
  • ROLLBACK, ABORT
ACID特性
複数トランザクション同時実行による不整合を生じる可能性
  • ダーティリード
  • 反復不能読み取り（Fuzzy Read）
    • トランザクション内で同じレコードを2回読み込んだとき、読み込み間隔内に他のトランザクションが該当レコードの更新をコミットしたことで、1回目と2回目の読み込み結果が変わってしまうこと。
  • ファントムリード
    • トランザクション内で同一条件のレコード抽出を2回行ったとき、読み込み間隔内に他のトランザクションがレコードの挿入/削除をコミットしたことで、1回目と2回目の結果のレコード数が変わってしまうこと。
トランザクション分離レベル
  • READ UNCOMMITTED
    • 未コミット状態のデータであっても読み込む。
    • ダーティリードが発生。
    • PostgreSQLの仕様で内部的には「READ COMMITTED」になる。
  • READ COMMITTED
    • コミットされたデータのみを読み込む。
    • ダーティリードに対応。
    • 反復不能読み取りが発生。
  • REPEATABLE READ
    • トランザクション中は他のトランザクションでコミットされた更新を参照しないことで、同じレコードを繰り返し読んでも常に同じ結果が得られることを保証する。
    • 反復不能読み取りに対応。
    • ファントムリードが発生。
  • SERIALIZABLE
    • トランザクションの逐次実行をエミュレートし、直列的に実行した場合と同じ結果になることを保証する。
    • ファントムリードに対応。
トランザクション分離レベル設定
  • セッション単位
    • SET default_transaction_isolation TO '分離レベル';
  • トランザクション単位
    • SET TRANSACTION ISOLATION LEVEL 分離レベル;
    • トランザクションの開始時に指定
      • BEGIN ISOLATION LEVEL 分離レベル;
      • START TRANSACTION ISOLATION LEVEL 分離レベル;

SAVEPOINT

https://www.postgresql.jp/document/11/html/sql-savepoint.html

同じ名前のセーブポイントを作成した場合はセーブポイントが上書きされるため、削除すると上書き前のセーブポイントに戻る。

受験対策

2.103_Silverの例題解説「S2 運用管理 - S2.4 バックアップ方法（トランザクションログ（WAL）とWALアーカイブ）」

カーソル

https://www.postgresql.jp/document/9.6/html/plpgsql-cursors.html

SELECT文の結果を、LIMITを使用せずに任意の位置から必要な件数分取り出したり、取り出した行の更新・削除を行いたい場合に使用する。

  • DECLARE カーソル名 [オプション1] CURSOR [オプション2] FOR データを返す問い合わせ;
    • オプション1
      • INSENSITIVE（デフォルト）取得したデータは、取得元テーブルの更新に影響を受けない
      • SCROLL（デフォルト）カーソルは順方向、逆方向どちらにも移動可能
      • NO SCROLL カーソルは順方向に移動可能、逆方向はエラーになる
    • オプション2
FETCH

FETCHはカーソルの移動と同時にデータを取得するコマンド。

  • FETCH [オプション [FROM]] カーソル名;

データの取得はせずにカーソルの移動のみを行う場合はMOVEコマンドを使用。

  • 「PRIOR」オプションを指定すると、前の行のデータを取得する。
  • オプションを省略した場合のデフォルトは「NEXT」となる 。
  • 「ALL」オプションを指定すると、カーソルのある次の行以降の全データを取得する。
  • カーソルを使用してデータを取得するため、事前にカーソルが作成されている必要がある 。

ロック

行ロック

https://www.postgresql.jp/document/11/html/sql-select.html#SQL-FOR-UPDATE-SHARE

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

FOR UPDATEを使用すると、問い合わせによって検索された行が排他ロックされる。
共有ロックと共有ロックは衝突しない。
共有ロックと排他ロック、排他ロックと排他ロックだと衝突する。

テーブルロック

https://www.postgresql.jp/document/11/html/sql-lock.html

  • LOCK TABLE テーブル名 [IN ロックモード]
    • ロックモード
      • EXCLUSIVE SELECTのみ許可、あとは全てブロック。
      • ACCESS EXCLUSIVE 全てブロック。（デフォルト）

テーブル全体のロックで使用する。
PostgreSQLには、デッドロックを検知すると対象のトランザクションをロールバックし、自動で回復させる機能がある。
行に対するロックは、排他ロックと共有ロックの2種類である 。
行に対するロックは「SELECT FOR SHARE」や「SELECT FOR UPDATE」を使用して設定できる 。
ACCESS EXCLUSIVEモードでテーブルをロックした場合、他トランザクションに対して、ロック対象のテーブルへの全ての処理をブロックする。