OracleからAzure DB（PostgreSQL）へ移行する~⑤PostgreSQLの動作の仕組み

今回はPostgreSQLの動作に関して「接続」、「SQL実行」、「トランザクション分離レベル」、「ロック」について記載したいと思います。

#クライアントからの接続要求によるサーバの動作
PostgreSQLクライアントからサーバに接続要求がある場合、まず、クライアントから接続要求をマスタサーバプロセスが受け付けます。
マスターサーバプロセスは接続要求に対してpg_hba.confの内容から接続を許可しているのか等の確認を行います。
サーバ接続に認証が必要な場合、認証要求をクライアントに送付し、クライアントが認証情報をサーバに送付、サーバ側で認証を行います。

認証が完了すれば、マスターサーバプロセスをフォークする形でバックエンドプロセスを立ち上げ、TCP/IPによる通信経路を確立します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/aa3af7dc-3922-2c4a-d7eb-8b71a830e653.png)

#クエリの実行
確立された通信経路で、クライアントはSQLの実行が可能です。

SQLを実行する際にはクライアントからSQL文を受け取るとまずは、構文を解析します。SQLの記述に間違いが無いかなどの確認を行った後、ビューなどが含まれている場合、リライタにてそれが実テーブルへ変換されます。
変換された後、各テーブル、インデックスなどの情報を基にプランナにて実行計画が生成され、生成された実行計画をベースにエグゼキュータにてSQLが実行されます。
この動きはOracleでもほぼ変わらない動きになるかと思います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/d004fd85-54fe-3c40-8d05-73525710e29a.png)

#トランザクション分離レベル
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処理について記載します。