Postgresのトランザクション分離レベルについての備忘録

はじめに

最近データベース(Postgres)について興味があり時間があれば調べていました。 トランザクション分離レベルについての理解度が浅すぎると思い、手を動かしながら確認することで理解度をあげることができたので備忘録を残します。

特に、今回はSERIALIZABLE の挙動について確認しました。

PostgreSQL: Documentation: 16: 13.2. Transaction Isolation

環境

• docker : postgres:12.17-alpine3.19

 SELECT version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.17 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
(1 row)

準備

• テーブルの作成
• 初期データを作成

テーブルの作成

CREATE TABLE my_table (
    v INT
);

初期データを作成

INSERT INTO my_table (v) VALUES (3);
INSERT INTO my_table (v) VALUES (4);

SELECT * FROM my_table;
 v
---
 3
 4

確認

• ファントムリードやノンリピータブルリードが起きないことを確認
• シリアライゼーションアノマリーが起きないか確認

ファントムリードやノンリピータブルリードが起きないことを確認

最初に1つトランザクション(A)を開始してSERIALIZABLEにします。SHOWを利用して、トランザクション内の分離レベルを確認できます。

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW TRANSACTION ISOLATION LEVEL;
 transaction_isolation
-----------------------
 serializable

もう1つトランザクション(B)を開始します

BEGIN;

Bで新しくインサートしてコミットしてデータを確認してみます。5が挿入されています

INSERT INTO my_table (v) VALUES (5);
COMMIT;

SELECT * FROM my_table;
 v
---
 3
 4
 5

Aでデータを確認してみると、5は見えていないので、ファントムリードされないことを確認できます。

 SELECT * FROM my_table;
 v
---
 3
 4

同様にして、Bでデータを書き換えても、Aは更新されていないので、ノンリピータブルリードが起きていないことを確認できます。

UPDATE my_table SET v = 4 WHERE v = 3;
COMMIT;
SELECT * FROM my_table;
 v
---
 4
 5
 4

シリアライゼーションアノマリーの問題が起きないか確認

一番大きい値を１件取得して +1 した値をレコードに挿入するとします。
実現するには、下記のようなクエリになると思います。

INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;

2つ新しくトランザクション(A,B)を開始してどちらも、SERIALIZABLEに設定してデータを挿入します。

Aのトランザクションで6が追加される

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Bのトランザクションでも6が追加される

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO my_table (v) SELECT v + 1 FROM my_table ORDER BY v DESC LIMIT 1;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Aのトランザクションをコミット

COMMIT;
SELECT * FROM my_table;
 v
---
 4
 5
 4
 6

Bのトランザクションでコミット

COMMIT;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

後（B）でコミットしたトランザクションはエラーとなりロールバックされます。 Bは、先(A)で変更されたトランザクションの状態を反映していないのでエラーとなり、データの挿入を防ぎシリアライゼーションアノマリーの問題を防ぐことができます。

詳細は下記の閲覧をおすすめします。
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

最後に

全体的に、トランザクション分離レベルについての知識は浅く特に SERIALIZABLE に関してはほぼ何もわかっていませんでした。 手を動かして、動作を確認できて理解をあげることができたと思います。

Vultr Kubernetes Engine に firewall 機能が追加されたのでTerraformで設定してみた

はじめに

VultrのKubernetes（VKE）のクラスタに対してfirewallを設定できるようになっていました。
Terraformにも対応されていたのでTerraformにてfirewallの設定を行ってみました。

VKE Firewall 機能について

VKEのFirewall機能はノード（インスタンス）に対して、Firewallが自動でリンクする機能のようです。
なので、Firewall 機能を有効にしてノードを増やすと設定したFirewallのルールがノードに適用されていました。

※ ドキュメントを見つけれなかったですが、管理画面を見ている限りFirewallはインスタンスに設定されているようでした。

Terraformで設定してみる（管理画面も必要）

Vultrのプロバイダ 2.18 以上で Kubrenetes のArgument Reference のオプションに enable_firewall が追加されます。

registry.terraform.io

したがって enable_firewall = true を設定すると有効化されます。

resource "vultr_kubernetes" "main" {
  region   = <リージョン>
  version  = <バージョン>
  enable_firewall = true
}

しかし、plan してみるとわかるのですが現在（2023/12/21）クラスタが再作成されてしまいます。 新しくクラスタを作る場合は問題ないのですが既存クラスタで設定する場合は不都合がありました。

+ enable_firewall        = true # forces replacement

そこでライフサイクルを設定して管理画面から有効化します。

resource "vultr_kubernetes" "main" {
  region   = <リージョン>
  version  = <バージョン>
  enable_firewall = true
  lifecycle {
    ignore_changes = [enable_firewall]
  }
}

terraform plan して apply してみるとステートファイルが書き換わっていると思います。
(planやらapplyやらしていたら、ステートファイルがいつの間にか変わっていたので、多分applyが必要？)

"firewall_group_id": "<firewallのid>",

firewallのルール設定してみる

先ほど設定したFirewallはTerraform内で参照することができるので下記のようにルールを追加することが可能です。

resource "vultr_firewall_rule" "main" {
    firewall_group_id = vultr_kubernetes.main.firewall_group_id
    protocol = "tcp"
    ip_type = "v4"
    subnet = "0.0.0.0"
    subnet_size = 0
    port = "22"
    notes = "ssh"
}

さいごに

いつの間にかVKEにFirewallを設定できるようになって、インスタンスに対して自動でIP制限を設定できるようになっていました。今までインスタンスが外部に公開されてしまう課題に対しては個別にインスタンスに設定する必要がありましたがアップデートにて自動で設定されるようになり管理が簡単になりました。

FlaskとSQLAlchemyで作るPostgreSQLを使ったシンプルなキューの実装

はじめに

アプリケーションで非同期処理を行う際、RabbitMQのようなキューは大変便利です。 ただし、今回プライベートで作成していたアプリケーションでは、シンプルな機能だけで十分でした（可視性タイムアウトやACKなどの高度な機能は必要ありません）。
また、構成を極力簡素に保ちたいため、別途キュー用のミドルウェアを設置することは避けたいと考えました。

そこで、今回はDBにシンプルなキューの役割を担ってもらうことにしました。 調べてみると、Postgres の機能の一つに SKIP LOCKED という機能がありこれを利用することでキューのようなことを実現できそうです。※1
今回は、Flask + SQLAlchemy + PostgreSQL を利用してシンプルなHTTPベースのキューを実装してみました。

利用するもの

• Python
• Flask
• SQLAlchemy
• PostgreSQL -> バージョン 15.5

ざっくり仕組み ※2

PostgreSQLの機能に SKIP LOCKED という機能があります。 これは、ロックできない行をスキップして取得することができるそうです。 SELECT してロックする際に SKIP LOCKED を利用することで、既にロックされた行を無視して取得できます。

実装

• SQLAlchemyでモデルを定義
• メッセージを登録する処理を追加
• メッセージを取得する処理を追加
• Flaskでエンドポイントを定義
• 確認

SQLAlchemyでモデルを定義

まずはモデルを定義します。 messageカラムにJSONで値を入れることができます。
レコードが増える度にidをインクリメントしていきます。
※ 取得する際はidが大きい値から取得します

from sqlalchemy import Column, Integer, JSON
class MQ(Base):
    __tablename__ = "mq"
    id = Column(Integer, primary_key=True)
    message = Column(JSON)
    created_at = Column(DateTime, default=datetime.utcnow)

メッセージを登録する処理追加

メッセージを登録する処理を add_message() に実装します。 先ほど実装した MQというモデルに、messageを登録します

class Query:
    def __init__(
        self,
        DATABASE_URL: str = "postgresql+psycopg2://postgres:postgres@localhost:5432/tegami",
    ) -> None:
        engine = create_engine(DATABASE_URL)
        Session = sessionmaker(bind=engine)
        self.session = Session()
        Base.metadata.create_all(engine)

    def add_message(self, message: dict):
        model_message = MQ(message=message)
        self.session.add(model_message)
        self.session.commit()

メッセージを取得する処理追加

ロックを取得できるものから最大のIDに対してロックをかける処理を行っています。 SQLAlchemyでは with_for_update(skip_locked=True) を利用することで既にロックされているレコードを無視できます。

    def get_message(self):
        try:
            message = (
                self.session.query(MQ)
                .order_by(MQ.id.asc())
                .with_for_update(skip_locked=True)
                .limit(1)
                .one()
            )
            self.session.delete(message)
            self.session.commit()
            return message.message
        except Exception as e:
            self.session.rollback()
            return None

ちなみに、上記コードが実行された際のSQLは下記でした。

SELECT mq.id AS mq_id, mq.message AS mq_message, mq.created_at AS mq_created_at 
FROM mq ORDER BY mq.id ASC 
 LIMIT %(param_1)s FOR UPDATE SKIP LOCKED

Flaskでエンドポイント定義

/api/message に対してPOSTすることでキューにメッセージを登録することができます。
さらに /api/messageに対してGETすることでメッセージを取得することができます

@app.route("/api/message", methods=["POST"])
def add_message():
    req = request.get_json()
    query.add_message(message=req)
    return "OK",200


@app.route("/api/message")
def get_message():
    message = query.get_message()
    if message:
        return message,200
    else:
        return "",501

確認

キューにメッセージを積みたい場合

curl -X POST -H "Content-Type: application/json" -d '{"text":"test"}' http://127.0.0.1:8000/api/message

キューからメッセージを取得したい場合

curl http://127.0.0.1:8000/api/message

最後に

Flask + SQLAlchemy + PostgreSQL を使ってシンプルなキューを実装してみました。
MQモデルやモデルへのクエリ処理を修正することでさらに複雑な処理を実装できそうです。

参考

• ※1 https://blog.enjoyxstudy.com/entry/2017/09/10/000000
• ※2 PostgreSQL: Documentation: 16: SELECT

Fly.ioのコマンド操作：開発時間外のアプリ無効化の手順

はじめに

プライベートな開発で flyio を利用しています。

課題

開発の時間以外は、リクエストできないようにしたいのですが、現在のWebコンソールを見た限りはアプリケーションをいったん削除するしかなさそうでした。
アプリケーションの削除までは行いたくないと思ったので、何か解決方法はないかとドキュメントを読んでると解決できそうな方法があったので紹介です。

解決

コマンドを利用して下記を実行すると、インスタンス(VM)の数を0にすることができました。 実際にアクセスすると、リクエストを受け付けなくなっていました。

flyctl scale count 0

確認のためインスタンスのサイズを確認するコマンドを実行してみると、インスタンスの表示がなくなっていると思います。

flyctl machine list

さらに、開発を再開したいときは下記コマンドでインスタンスを起動できました。

flyctl scale count 1

fly.io

Svelte(Vite)ビルド時に変数を渡したい

はじめに

普段インフラがメインであまりフロント周りを触る機会がなかったものの、趣味のWebアプリを作るためにフロントエンドのフレームワーク Svelte に入門しました。

svelte.jp

課題

コードの動作の中でバックエンドのAPIサーバにアクセスする箇所が出てきました。
普段はローカルで開発するので、開発時のAPI http://localhost ~~ にリクエストしたいのですが、サーバにデプロイする際は https://ドメイン ~~ にアクセスしてほしいものです。 しかし、下記のようにコードにハードコードしてしまうと、開発時とサーバデプロイ時でAPIのエンドポイントを変更する必要があるので手間でした。

const API_ENDPOINT: string = <APIのエンドポイント>

解決

SvelteのビルドはViteというものが行っており、Viteでビルドする際に変数を渡すことができそうです。 ja.vitejs.dev

実際にビルド(npm run build)してみると vite build というのが行われているぽい

> frontend@0.0.1 build
> vite build

vite のドキュメントを読んでいると、.envや.env.developmentなど変数を記載しておくことでビルド時に変数を渡すことができます。 例えば下記のように

VITE_API_ENDPOINT="http://localhost:8000"

Svelteのコード内で利用する際は下記のように利用できるので、ビルド時に変数を書き換える必要がなくなりました。

// import.meta.env.VITE_~~ のように VITE_で始まる変数をコード内で利用できる
const API_ENDPOINT: string = import.meta.env.VITE_API_ENDPOINT

環境変数とモード | Vite

さらに、
ビルド時に開発時は .env.development を利用して、サーバデプロイ時は .envを利用するように package.json を変更します。

"scripts": {
        "build-dev": "vite build --mode development",
        "build": "vite build",

これで、 npm run build-dev 実行時は env.development が利用され、npm run build 実行時は .env の変数が格納されるようにできました。

個人で作ってるサービス動作環境の備忘録 1

コンテナ関連の技術を使いたい

コンテナ関連の技術が好きなのでサービスも関連技術で構築したいところです。
下記3つの候補を考え、今のところDocker Swarmを利用することにしました。

• Kubernetes
• Docker Swarm
• Nomad

した3つが大きい理由です

• 開発で使う docker-compose の yaml ファイルを流用しやすい
• クラスタを組むのが割と簡単
• 単純に Docker コマンドに慣れている

サーバはVPS

Vultr VPSというサービスを利用してそこに仮想サーバを立ててクラスタを組んでいきます。

https://www.vultr.com

一部 Conoha を利用します。

www.conoha.jp

Terraformでのインフラ管理

手動で作成するのはあまりしたないので、Infrastructure as code を実現するツールの一つである、 Terraformを利用します。
雑に説明すると各プロバイダー（今回はVultr）のサーバやネットワーク..などのリソースをコードで管理できるようになるツールです。

詳しくは公式サイト
www.terraform.io

TerraformでVultrリソースを操作する際は下記を見ながら設定しています。

https://registry.terraform.io/providers/vultr/vultr/latest/docs

Getting started with Kind(kubernetes in docker)

What is Kind?

Kind can running kubernetes on docker

Environment

• centos7

How to install

Befor doing....

$ yum -y update

Require

• kubectl
• docker
• kind

Installing kubectl

Install Kind . But can not install kubectl .... so
install kubectl

$ curl -LO https://storage.googleapis.com/kubernetes-release/release/`curl -s https://storage.googleapis.com/kubernetes-release/release/stable.txt`/bin/linux/amd64/kubectl
$ chmod +x ./kubectl
$ mv ./kubectl /usr/local/bin/kubectl

Verification

$ kubectl

Install docker

next step is docker install

$ yum -y remove docker docker-common docker-selinux docker-engine
$ yum install -y yum-utils device-mapper-persistent-data lvm2
$ yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
$ yum makecache fast
$ yum -y install docker-ce
$ systemctl enable docker
$ systemctl start docker

Verification

$ docker -v

Install Kind

finally..... install Kind

$ curl -Lo ./kind https://github.com/kubernetes-sigs/kind/releases/download/v0.5.1/kind-$(uname)-amd64
$ chmod +x ./kind
$ mv ./kind  /usr/local/bin/kind

Verification

$ kind

Create a kubernetes cluster with Kind

$ kind create cluster
$ export KUBECONFIG="$(kind get kubeconfig-path --name="kind")"
$ kubectl cluster-info

Let's Try

• kubernetes.io

Reference

• Install and Set Up kubectl - Kubernetes
• https://docs.aws.amazon.com/ja_jp/eks/latest/userguide/install-kubectl.html
• kind
• kind試す - Qiita