在看完 Table & Row Level Lock 之後，我們要來看看 Deadlock（死鎖）。在資料庫的世界裡，當多個 transactions 彼此等待對方釋放資源，卻又沒有任何一方能先完成時，就會發生 Deadlock。這種情況會導致交易無限期地等待，影響系統效能和應用程式的正常運行。

幸好在 PostgreSQL 會自動偵測死鎖並解決它，但解法是強制中止其中一個 transaction，讓其他 transaction 繼續執行。不過，哪個 transaction 會被中止並沒有固定規則，所以我們無法依賴 PostgreSQL 自己處理，而應該在設計時避免死鎖的發生。

PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)

什麼是 Deadlock？

假設有兩個交易 A 和 B：

1. 交易 A 先鎖住資料表 account_id = 1，然後試圖鎖住 account_id = 2
2. 交易 B 先鎖住 account_id = 2，然後試圖鎖住 account_id = 1

這時候，交易 A 等交易 B 釋放 2，交易 B 等交易 A 釋放 1，結果變成雙方都無法前進，導致死鎖。

重現死鎖的狀況

我們可以模擬上述的情況，來看看死鎖是怎麼產生的。

1. 建立 Table & 塞入測試資料

import psycopg2
import time
import threading
from psycopg2 import Error

DB_CONFIG = "dbname= user= password="

def create_table(conn):
    try:
        cursor = conn.cursor()
        cursor.execute("DROP TABLE IF EXISTS accounts")

        # 建立 table
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS accounts (
                account_id SERIAL PRIMARY KEY,
                balance DECIMAL(10,2),
                name VARCHAR(100)
            )
        """
        )

        # 塞入測試資料
        cursor.execute(
            """
            INSERT INTO accounts (balance, name) VALUES 
            (1000, 'Account 1'), 
            (2000, 'Account 2')
        """
        )

        conn.commit()
        print("Table created and sample data inserted successfully")

    except Error as e:
        print(f"Error: {e}")
        conn.rollback()

2. transaction 1：修改 account_id = 1 → account_id = 2

def transaction1():
    try:
        conn = psycopg2.connect(DB_CONFIG)

        cursor = conn.cursor()
        print("Transaction 1 started")
        cursor.execute("BEGIN")
        
        # 鎖定 account_id = 1
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance - 100 
            WHERE account_id = 1
        """
        )
        print("Transaction 1: Updated account 1")

        time.sleep(5)

        # 鎖定 account_id = 2
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance + 100 
            WHERE account_id = 2
        """
        )
        print("Transaction 1: Updated account 2")

        cursor.execute("COMMIT")
        print("Transaction 1 committed")

    except Error as e:
        print(f"Transaction 1 error: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

3. transaction 2: 修改 account_id = 2 → account_id = 1

def transaction2():
    try:
        conn = psycopg2.connect(DB_CONFIG)

        cursor = conn.cursor()
        print("Transaction 2 started")

        cursor.execute("BEGIN")
        # 鎖定 account_id = 2
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance - 200 
            WHERE account_id = 2
        """
        )
        print("Transaction 2: Updated account 2")

        time.sleep(5)

        # 鎖定 account_id = 1
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance + 200 
            WHERE account_id = 1
        """
        )
        print("Transaction 2: Updated account 1")

        cursor.execute("COMMIT")
        print("Transaction 2 committed")

    except Error as e:
        print(f"Transaction 2 error: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

4. 模擬兩者同時進行

conn = psycopg2.connect(DB_CONFIG)

create_table(conn)
conn.close()

thread1 = threading.Thread(target=transaction1)
thread2 = threading.Thread(target=transaction2)

thread1.start()
thread2.start()

thread1.join()
thread2.join()

執行之後，PostgreSQL 在偵測到這種情況後，會中止其中一個 transaction並拋出錯誤。

如何避免 Deadlock？

雖然 PostgreSQL 能自動處理死鎖，但我們應該主動避免死鎖發生，以官方文件的解決方法為例：按照相同順序鎖定資源。

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

錯誤範例（可能導致 Deadlock）：

• 交易 A： LOCK table X → LOCK table Y
• 交易 B：LOCK table Y → LOCK table X

正確做法：

• 都遵循 LOCK table X → LOCK table Y 的順序

假如我們把 transaction 2 的順序改成跟 transaction 1 一樣的話：
transaction 2：修改 account_id = 1 → account_id = 2

def transaction2():
    try:
        conn = psycopg2.connect(DB_CONFIG)

        cursor = conn.cursor()
        print("Transaction 2 started")

        cursor.execute("BEGIN")
        
        # 一樣先鎖 account_id = 1
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance + 200 
            WHERE account_id = 1
        """
        )

        print("Transaction 2: Updated account 1")

        time.sleep(5)

        # 再鎖 account_id = 2
        cursor.execute(
            """
            UPDATE accounts 
            SET balance = balance - 200 
            WHERE account_id = 2
        """
        )
        print("Transaction 2: Updated account 2")

        cursor.execute("COMMIT")
        print("Transaction 2 committed")

    except Error as e:
        print(f"Transaction 2 error: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

transaction 2 就會等 transaction 1 先處理完，釋放鎖了之後，transaction 2 才會繼續。

重點回顧

• PostgreSQL 會自動偵測並解決 Deadlock，但會中止其中一個交易
• 統一 transaction 的鎖定順序，可以避免發生 Deadlock

參考資料

https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS