再來要介紹的是 Row Level 的鎖了，相較於 Table Level Lock 會鎖住整張表，Row Level Lock 只會影響特定的資料行。PostgreSQL 總共有 4 種 Row Level Lock，可以再劃分為兩種類別：讀鎖 & 寫鎖，今天就先來看看 SHARE 系列的兩個讀鎖。

什麼是 FOR SHARE？

FOR SHARE 的用意是鎖定指定的 row，這時候其他人不能 UPDATE / DELETE 和加上寫鎖，但允許其他查詢讀取到這筆資料。使用的時機是想要保證在當下的 transaction 中，此筆資料確定一定存在，防止別人修改刪除。

當有其他 transaction 需要對同一行進行 UPDATE 或 DELETE 時，必須等到持有 FOR SHARE 鎖的 transaction 完成後，才能進行更改。

FOR SHARE - A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.

使用 FOR SHARE 確保資料存在

先來看沒有加上 FOR SHARE 會發生的情境：

1. 建立 table & 測試資料

import psycopg2
import threading
import time

DB_CONFIG = "dbname= user= password="

def setup_database():
    try:
        conn = psycopg2.connect(DB_CONFIG)
        cursor = conn.cursor()

        # 建立 table
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS accounts (
                account_id INT PRIMARY KEY,
                name TEXT NOT NULL
            );
        """
        )

        # 塞入測試資料
        cursor.execute("TRUNCATE accounts;")
        cursor.execute("INSERT INTO accounts (account_id, name) VALUES (1, 'User A');")

        conn.commit()
        cursor.close()
        conn.close()
        print("Database setup complete.")

    except Exception as e:
        print(f"Error setting up database: {e}")

2. transaction 1 第一次讀取 account_id = 1 → 等待 transaction 2 刪除 → 再次讀取

def transaction_1():
    try:
        conn = psycopg2.connect(DB_CONFIG)
        cursor = conn.cursor()
        print("Transaction 1: Starting...")

        # 第一次讀取
        cursor.execute("BEGIN;")
        cursor.execute("SELECT * FROM accounts WHERE account_id = 1 FOR SHARE;")
        rows = cursor.fetchall()
        print(f"Transaction 1: Initial select result: {rows}")

        # 等待 transaction 2 進行刪除
        time.sleep(5)

        # 第二次讀取
        cursor.execute("SELECT * FROM accounts WHERE account_id = 1;")
        rows = cursor.fetchall()
        print(f"Transaction 1: After delete select result: {rows}")

        conn.commit()
        cursor.close()
        conn.close()

    except Exception as e:
        print(f"Error in Transaction 1: {e}")

3. transaction 2 同時要刪除資料

def transaction_2():
    try:
        # 讓 transaction 1 先進行讀取
        time.sleep(1)  

        conn = psycopg2.connect(DB_CONFIG)
        cursor = conn.cursor()
				
		# 刪除資料
        print("Transaction 2: Deleting account_id = 1")
        cursor.execute("BEGIN;")
        cursor.execute("DELETE FROM accounts WHERE account_id = 1;")
        conn.commit()
        print("Transaction 2: Deleted account_id = 1.")

        cursor.close()
        conn.close()

    except Exception as e:
        print(f"Error in Transaction 2: {e}")

4. 模擬兩者同時進行

def run_transactions():
    setup_database()

    t1 = threading.Thread(target=transaction_1)
    t2 = threading.Thread(target=transaction_2)

    t1.start()
    t2.start()

    t1.join()
    t2.join()

if __name__ == "__main__":
    run_transactions()

5. 得到結果

過程：

1. transaction 1 先讀取 account_id = 1。
2. transaction 2 刪除 account_id = 1 並 COMMIT。
3. transaction 1 再次讀取 account_id = 1，發現資料已經被刪除，造成不一致的情況。

如果我們將第一次 SELECT 的地方加上 FOR SHARE 呢？

# transaction 1 讀取時加上 FOR SHARE
cursor.execute("SELECT * FROM accounts WHERE account_id = 1 FOR SHARE;")

過程：

1. transaction 1 讀取 account_id = 1 並加上 FOR SHARE 鎖。
2. transaction 2 嘗試刪除 account_id = 1，但無法立刻執行，必須等待 transaction 1 完成。
3. transaction 1 再次讀取 account_id = 1，然後 COMMIT。
4. transaction 2 獲得執行權限，成功刪除 account_id = 1。

如此一來，就可以保證 transaction 1 內都能夠讀取到 account_id = 1 的資料了。

比 FOR SHARE 再弱一點點的 FOR KEY SHARE

相對於 FOR SHARE 禁止其他 transaction 修改和刪除，FOR KEY SHARE 是允許其他 transaction 修改的，但是不能改到有 key 的欄位（例如 PRIMARY KEY、UNIQUE）。

FOR KEY SHARE - Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

這個鎖什麼時候會出現呢？假如有兩張 table products 以及 categories ，products 的 category_id 為 foreign key，對照到 categories 的 id：

-- products
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INTEGER NOT NULL,
    -- 設定 category_id 為 foreign key
    CONSTRAINT fk_category
        FOREIGN KEY (category_id)
        REFERENCES categories (id)
);

-- categories
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

當我想幫 id = 1 的產品更新 category_id 時，假如從 1 改成 100：

UPDATE products SET category_id = 100 WHERE id = 1

PostgreSQL 會自動將 categories 的 id = 100 加上 FOR KEY SHARE ，以確保改成 100 的時候，不會有人把 categories 的 id = 100 這筆資料給刪了，但是其他欄位比如 name 還是可以開放修改。當不需要禁止整個 row 的全部欄位都不能修改，就會使用 FOR KEY SHARE 。

還有兩個 Row Level Lock，明天再來看看 UPDATE 系列！

重點回顧

• FOR SHARE 可以允許其他 transaction 讀取資料，但禁止 UPDATE 和 DELETE。
• FOR KEY SHARE 可以允許其他 transaction 讀取資料，但禁止 DELETE，開放非鍵值（key）的修改。
• 使用時機：當需要確保某筆資料在 transaction 內不變時，例如讀取某個對象後依賴它進行進一步操作。

參考資料

https://www.postgresql.org/docs/17/explicit-locking.html