Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@e-suzuno

PostgreSQL 外部キーのロック挙動を知りたかった

Last updated at Posted at 2024-08-02

目的

PostgreSQL 外部キーは設定すると親テーブルを共有ロックするため
デッドロックの原因になりうるという記事がたくさんヒットするが
現時点自分が管理しているデータベースで、デッドロックのログがヒットしないので
どのような状態で再現するか経験しておきたかった。

テスト用のテーブル情報の作成のSQL

parentテーブル、childテーブルの作成

create table parent
(
    id   serial
        primary key,
    name varchar(100)
);

create table child
(
    id   serial  primary key,
    parent_id integer references parent,
    name  varchar(100)
);

初期データの作成

insert into parent (name) values ('name1');
insert into parent (name) values ('name2');
insert into parent (name) values ('name3');

検証パターン１

Insertを行うと、外部キーで設定された親テーブルに対して共有ロックを行ってしまうので
それでデッドロックするという概要だけはなんとなく記事でたくさんヒットするので
なるほどInsertしてsleepしてやればいいのだなと思い
Pythonでプログラムを実行した

検証プログラム

import psycopg2
from concurrent.futures import ThreadPoolExecutor
import time

# データベース接続情報
conn_params = {
    'dbname': 'database',
    'user': 'database',
    'password': 'database',
    'host': 'localhost'
}

def transaction_1():
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t1", "BEGIN;")
        execute(cursor, "t1", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")
        print(f"t1 sleep")
        time.sleep(8) 
        print(f"t1 sleep end")
        execute(cursor, "t1", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")
        print(f"t1 end")
        conn.commit()
    except Exception as e:
        print(f"t1 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

def transaction_2():
    time.sleep(1) 
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t2", "BEGIN;")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');")
        print(f"t2 sleep")
        time.sleep(20)
        print(f"t2 sleep end")
        execute(cursor, "t2", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")
        print(f"t2 end")
        conn.commit()
    except Exception as e:
        print(f"t2 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def execute(cursor, head, sql):
    cursor.execute(sql)
    print(f"{head}:{sql}")


# 並列にトランザクションを実行
with ThreadPoolExecutor(max_workers=2) as executor:
    executor.submit(transaction_1)
    executor.submit(transaction_2)

検証結果

t1:BEGIN;
t1:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
t1 sleep
t2:BEGIN;
t2:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
t2:INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');
t2 sleep
t1 sleep end
t1:UPDATE parent SET name = 'Transaction 1' WHERE id = 1;
t1 end
t2 sleep end
t2:UPDATE parent SET name = 'Transaction 1' WHERE id = 1;
t2 end

プロセスは終了コード 0 で終了しました

期待している結果はデッドロックして例外が投げられることだが、全て正常に処理されてしまった。
そもそもこれでデッドロックするようなら
自分が稼働させているサービスもデッドロックしまくりなはずなので何か考えが抜けていると感じた。

検証パターン２

排他ロックを複合した時に発生するという記事を確認したので
排他ロックを含んだ下記のプログラムを用意した。

検証プログラム

import psycopg2
from concurrent.futures import ThreadPoolExecutor
import time

# データベース接続情報
conn_params = {
    'dbname': 'database',
    'user': 'database',
    'password': 'database',
    'host': 'localhost'
}


def transaction_1():
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t1", "BEGIN;")
        execute(cursor, "t1", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")

        print(f"Transaction 1 sleep")
        time.sleep(8)  # 競合を引き起こすために長めの待機時間
        print(f"Transaction 1 sleep end")
        # ロック
        execute(cursor, "t1", "SELECT * FROM parent WHERE id = 1 FOR UPDATE;")
        execute(cursor, "t1", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")

        print(f"Transaction 1 end")
        conn.commit()
    except Exception as e:
        print(f"Transaction 1 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def transaction_2():
    time.sleep(1)  # トランザクション1が最初のロックを取得する時間を確保
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t2", "BEGIN;")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');")

        # parent id 1を削除して新規作成
        print(f"Transaction 2 sleep")
        time.sleep(20)
        print(f"Transaction 2 sleep end")

        # ロック
        execute(cursor, "t2", "SELECT * FROM parent WHERE id = 1 FOR UPDATE;")
        execute(cursor, "t2", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")


        print(f"Transaction 2 end")
        conn.commit()
    except Exception as e:
        print(f"Transaction 2 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def execute(cursor, head, sql):
    cursor.execute(sql)
    print(f"{head}:{sql}")


# 並列にトランザクションを実行
with ThreadPoolExecutor(max_workers=2) as executor:
    executor.submit(transaction_1)
    executor.submit(transaction_2)

検証結果

t1:BEGIN;
t1:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
Transaction 1 sleep
t2:BEGIN;
t2:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
t2:INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');
Transaction 2 sleep
Transaction 1 sleep end
Transaction 2 sleep end
t1:SELECT * FROM parent WHERE id = 1 FOR UPDATE;
Transaction 2 failed: deadlock detected
DETAIL:  Process 145 waits for ShareLock on transaction 818; blocked by process 144.
Process 144 waits for ShareLock on transaction 819; blocked by process 145.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,5) in relation "parent"

t1:UPDATE parent SET name = 'Transaction 1' WHERE id = 1;
Transaction 1 end

プロセスは終了コード 0 で終了しました

今度はdeadlock detectedと期待したデッドロックが発生した。
なるほど共有ロックされてる状態のものに、排他ロックでひっかかってデッドロックするという事か

検証パターン３

検証パターン２でデッドロックが起きてしまう原因として
排他ロックを途中で行うのが一番問題なのではないだろうか
更新など色んな処理の前に最初から排他ロックをこなってからすべての処理を行うようにすれば問題が起きなくなるのでは

検証プログラム

import psycopg2
from concurrent.futures import ThreadPoolExecutor
import time

# データベース接続情報
conn_params = {
    'dbname': 'database',
    'user': 'database',
    'password': 'database',
    'host': 'localhost'
}


def transaction_1():
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t1", "BEGIN;")
        # ロックを最初に行う
        execute(cursor, "t1", "SELECT * FROM parent WHERE id = 1 FOR UPDATE;")
        execute(cursor, "t1", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")

        print(f"Transaction 1 sleep")
        time.sleep(8)  # 競合を引き起こすために長めの待機時間
        print(f"Transaction 1 sleep end")
        execute(cursor, "t1", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")

        print(f"Transaction 1 end")
        conn.commit()
    except Exception as e:
        print(f"Transaction 1 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def transaction_2():
    time.sleep(1)
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    try:
        execute(cursor, "t2", "BEGIN;")
        execute(cursor, "t2", "SELECT * FROM parent WHERE id = 1 FOR UPDATE;")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');")
        execute(cursor, "t2", "INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');")

        print(f"Transaction 2 sleep")
        time.sleep(20)
        print(f"Transaction 2 sleep end")

        execute(cursor, "t2", "UPDATE parent SET name = 'Transaction 1' WHERE id = 1;")

        print(f"Transaction 2 end")
        conn.commit()
    except Exception as e:
        print(f"Transaction 2 failed: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def execute(cursor, head, sql):
    cursor.execute(sql)
    print(f"{head}:{sql}")


# 並列にトランザクションを実行
with ThreadPoolExecutor(max_workers=2) as executor:
    executor.submit(transaction_1)
    executor.submit(transaction_2)

検証結果

t1:BEGIN;
t1:SELECT * FROM parent WHERE id = 1 FOR UPDATE;
t1:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
Transaction 1 sleep
t2:BEGIN;
Transaction 1 sleep end
t1:UPDATE parent SET name = 'Transaction 1' WHERE id = 1;
Transaction 1 end
t2:SELECT * FROM parent WHERE id = 1 FOR UPDATE;
t2:INSERT INTO child (parent_id, name) VALUES (1, 'Transaction 1');
t2:INSERT INTO child (parent_id, name) VALUES (2, 'Transaction 2');
Transaction 2 sleep
Transaction 2 sleep end
t2:UPDATE parent SET name = 'Transaction 1' WHERE id = 1;
Transaction 2 end

プロセスは終了コード 0 で終了しました

デッドロックが発生しなくなった。

検証を踏まえて

Insertを行うと外部キーによる共有ロックは知らなかったので検証してよかった。
あと排他ロックについては最初にやるべきという教訓を得た

2
1
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?