今回はPostgreSQLの動作に関して「接続」、「SQL実行」、「トランザクション分離レベル」、「ロック」について記載したいと思います。
#クライアントからの接続要求によるサーバの動作
PostgreSQLクライアントからサーバに接続要求がある場合、まず、クライアントから接続要求をマスタサーバプロセスが受け付けます。
マスターサーバプロセスは接続要求に対してpg_hba.confの内容から接続を許可しているのか等の確認を行います。
サーバ接続に認証が必要な場合、認証要求をクライアントに送付し、クライアントが認証情報をサーバに送付、サーバ側で認証を行います。
認証が完了すれば、マスターサーバプロセスをフォークする形でバックエンドプロセスを立ち上げ、TCP/IPによる通信経路を確立します。
#クエリの実行
確立された通信経路で、クライアントはSQLの実行が可能です。
SQLを実行する際にはクライアントからSQL文を受け取るとまずは、構文を解析します。SQLの記述に間違いが無いかなどの確認を行った後、ビューなどが含まれている場合、リライタにてそれが実テーブルへ変換されます。
変換された後、各テーブル、インデックスなどの情報を基にプランナにて実行計画が生成され、生成された実行計画をベースにエグゼキュータにてSQLが実行されます。
この動きはOracleでもほぼ変わらない動きになるかと思います。
#トランザクション分離レベル
Oracleから移行する上で重要なのがトランザクション分離レベルです。
PostgreSQLでも、6.5以降Oracel同様はMVCCが搭載されています。PostgreSQLで準備されているトランザクション分離レベルを記載します。
| 分離レベル | 意味 | PostgreSQLでの扱い |
|---|---|---|
| READ UNCOMMITED | コミットされていないデータが参照される可能性がある。 | この指定を行ってもREAD COMMITEDとして扱う。（この分離レベルはPostgreSQLでは使えない。） |
| READ COMMITED | 問い合わせが実行される直前までにコミットされたデータのみを参照する事が可能。 | PostgreSQLではデフォルトの分離レベル |
| REPEATABLE READ | トランザクションが開始される前までにコミットされたデータのみを参照する。単一トランザクション内の連続するSELECT分は、常に同じデータを参照する。 | PostgreSQL 9.1からサポートされている。 |
| SERIALIZE | 最も厳しいトランザクション分離レベル。並列実行された複数のトランザクションの実行であっても、シリアルに扱われたものと同じ結果が返される。 | PostgreSQL 9.0以前ではREPETABLE READを指定しても、この挙動となる。 |
デフォルトはOracle Databaseと同じ、READ COMMITEDとなります。
Oracle からPostgreSQLへマイグレーションする場合、トランザクション分離レベルを変更する必要はありませんが、知識として知っておいた方がよいと思いまして、紹介させていただきました。
##MVCC(MultiVersion Concurrency Control)
MVCCは、書き込み処理（トランザクション）が行われている最中に他のユーザによる読み取りアクセスがあった場合、書き込み直前の状態（スナップショット）を処理結果として返します。（書き込み中も読み取り可能で、読み取り中でも書き込み可能）
SQL規格（SQL92）で定められた４種類のIsolation levelにおいてREAD COMMITEDに該当する処理となります。
MVCCを採用するその他のデータベースは以下のとおりです。
- Microsoft SQL Server 2005以降 – READ_COMMITED_SNAPSHOT
- Oracle Database 4以降
- MySQL（エンジンにInnoDBを使用した時のみ）
#ロック
トランザクションが実行されるために、テーブルやレコードに対してOracle同様にPostgreSQLでもロックを取得します。
PostgreSQLにおけるロックの種類をここに記載します。
-
ACCESS SHARE
SELECTを実行する際に獲得 -
ROW SHARE
SELECT FOR UPDATE、SELECT FOR SHARE、FOR NO KEY UPDATE、FOR KEY SHAREを実行する際に獲得 -
ROW EXCLUSIVE
INSER、DELETE、UPDATEを実行する際に獲得 -
SHARE UPDATE EXCLUSIVE
VACUMM、ANALYZE、CREATE INDEX、CONCURRENTLY、ALTER TABLE（SET STATISTICS）、SET、RESET、VALIDATE CONSTRAINT、CLUSTER ON、SET WITHOUT CLUSTERを実行する際に獲得 -
SHARE
CREATE INDEXを実行する際に獲得 -
SHARE ROW EXCLUSIVE
同一セッション内での競合を防止する為の取得するロック -
ACCESS EXCLUSIVE
ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULLで獲得される（LOCK TABLE文の発行時に獲得されるデフォルトのロックモード）
また、以下にロック競合に対するマトリクスを記載します。
各ロックモードの競合（〇：競合しない、×：競合する）
| ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE | |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | 〇 | 〇 | 〇 | 〇 | 〇 | 〇 | 〇 | × |
| ROW SHARE | 〇 | 〇 | 〇 | 〇 | 〇 | 〇 | × | × |
| ROW EXCLUSIVE | 〇 | 〇 | 〇 | 〇 | × | × | × | × |
| SHARE UPDATE EXCLUSIVE | 〇 | 〇 | 〇 | × | × | × | × | × |
| SHARE | 〇 | 〇 | × | × | × | × | × | × |
| SHARE ROW EXCLUSIVE | 〇 | 〇 | × | × | × | × | × | × |
| ACCESS EXCLUSIVE | × | × | × | × | × | × | × | × |
ROW SHAREの詳細な競合（〇：競合しない、×：競合する）
| FOR KEY SHARE | FOR SHARE | ROW SHARE | FOR UPDATE | |
|---|---|---|---|---|
| FOR KEY SHARE | 〇 | 〇 | 〇 | × |
| FOR SHARE | 〇 | 〇 | × | × |
| ROW SHARE | 〇 | × | × | × |
| FOR UPDATE | × | × | × | × |
#最後に
今回はPostgreSQLの動作の仕組みについて記載しました。Azure Database for PostgreSQLでも基本的には同じですが、一部pg_hba.confが機能しなかったりします（別途ファイアーウォールがあるため）。このあたりは後程記載します。
##次回は
次はPostgreSQLのVACUUM処理について記載します。