接著讀鎖下一個要介紹的是寫鎖，寫鎖的用途其實很生活化，大家應該都有搶過演唱會門票吧？寫用寫鎖的情境就像是搶演唱會門票一樣，為了防止兩個人可以同時搶到一樣的位子，我們想要幫特定資料加上寫鎖，以確保第一個人在修改這筆資料時，其他人無法同時修改。

什麼是 FOR UPDATE（寫鎖）？

• 其他 transaction 讀取同一行時，會被阻塞，直到鎖釋放（避免兩個人同時修改）。
• 確保當前 transaction 可以安全地更新該資料行，而不會產生併發問題。

這樣的機制適合用在資源競爭的場景，例如：搶演唱會門票、產品扣庫存、多人預約同一個會議室。

FOR UPDATE - FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.

使用 FOR UPDATE 確保只有一人能修改

先來看看沒有加上 FOR UPDATE 時會發生什麼狀況：

1. 建立 table & 測試資料

import psycopg2
import threading
import time

DB_CONFIG = "dbname= user= password="

def setup_database():
    conn = psycopg2.connect(DB_CONFIG)
    cur = conn.cursor()
    
    # 建立 table
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS seats (
            seat_id SERIAL PRIMARY KEY,
            reserved_by INTEGER
        )
    """
    )
    
    # 塞入測試資料
    cur.execute(
        "INSERT INTO seats (seat_id, reserved_by) VALUES (1, NULL) ON CONFLICT (seat_id) DO UPDATE SET reserved_by = NULL"
    )

    conn.commit()
    cur.close()
    conn.close()

2. 兩個人搶同一個位子

def reserve_seat(user_id):
    conn = psycopg2.connect(DB_CONFIG)
    cur = conn.cursor()

    try:
		# 選擇座位
        cur.execute("BEGIN")
        cur.execute("SELECT reserved_by FROM seats WHERE seat_id = 1 FOR UPDATE")

        result = cur.fetchone()
        if result and result[0] is None:
            # 模擬做了一些操作
            time.sleep(3)

            # 進行更新
            cur.execute(
                "UPDATE seats SET reserved_by = %s WHERE seat_id = 1", (user_id,)
            )
            conn.commit()
            print(f"User {user_id} successfully reserved the seat.")
        else:
            print(
                f"User {user_id} could not reserve the seat. Already reserved by {result[0]}."
            )

    finally:
        cur.close()
        conn.close()

3. 模擬兩者同時進行

def main():
    setup_database()

    t1 = threading.Thread(target=reserve_seat, args=(1,))
    t2 = threading.Thread(target=reserve_seat, args=(2,))

    t1.start()
    t2.start()

    t1.join()
    t2.join()

if __name__ == "__main__":
    main()

4. 得到結果

過程：

1. transaction 1 和 transaction 2 同時查詢 seat_id = 1，都看到該座位是空的。
2. 兩個 transaction 同時嘗試預訂該座位，並將狀態改為「已預訂」。
3. 結果：兩個人都搶到同一個座位！

這時候記得 SELECT 時加上 FOR UPDATE，就會變成只有一個人能搶到了：

# 選擇座位時加上 FOR UPDATE
cur.execute("SELECT reserved_by FROM seats WHERE seat_id = 1 FOR UPDATE")

過程：

1. transaction 1 先查詢 seat_id = 1，並加上 FOR UPDATE，此時它鎖住該筆資料。
2. transaction 2 也嘗試查詢 seat_id = 1，但它會「被阻塞」，因為 transaction 1 的 FOR UPDATE 會鎖定這筆資料，阻止 transaction 也對同一行上 FOR UPDATE 的鎖，直到 transaction 1 完成。
3. transaction 1 完成修改並 COMMIT，這時候 transaction 2 才能繼續執行，並發現該座位已被預訂。

可以多跑幾次腳本看看，每次搶到的人可能會不同，但一定只有一個人能搶到。

跟 FOR SHARE 的差別

FOR SHARE 和 FOR UPDATE 都是禁止其他 transaction 更改和刪除。但是他們的差別在於 FOR SHARE 是一種共用鎖，FOR UPDATE 是一種排他鎖。一個經典的例子就可以想成圖書館的書：

• SELECT ... FOR UPDATE：你拿走一本書，並把它鎖在你的個人研究室裡。沒有人可以碰這本書，即使是想看看也不行。這是為了保證你能夠在這本書上寫筆記或做任何修改，而不會有其他人干擾。
• SELECT ... FOR SHARE：你拿到一本書，但你允許其他幾個人也拿一份影本。你們所有人都保證這本書的原件不會被丟掉或修改，但你們都只能讀，不能在原件上寫東西。

所以如果對應到互斥的表格，會發現 FOR UPDATE x FOR UPDATE 是互斥的（只能有一個人拿到書），但是 FOR SHARE x FOR SHARE 不是互斥的（一群人可以拿影本）。

比 FOR UPDATE 再弱一點點的 FOR NO KEY UPDATE

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

根據文件提到的，在執行 UPDATE 指令時，如果修改的是 key 欄位，PostgreSQL 會使用 FOR UPDATE 的鎖，但如果修改是非鍵值的欄位，就會是用 FOR NO KEY UPDATE 的鎖。

FOR UPDATE - Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.

FOR NO KEY UPDATE - This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

重點回顧

• FOR UPDATE 適用於資源競爭場景（如搶票、扣庫存、預約等），確保不會有併發衝突。
• FOR SHARE 鎖定允許同時讀取同一筆資料並確保其不被修改，而 FOR UPDATE 鎖定則讓交易對該筆資料的獨佔寫入權，阻止任何其他交易的讀取或寫入。

參考資料

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