// PostgreSQL 錯誤參考

PostgreSQL 55P03:無法取得鎖定

鎖定請求放棄等待:使用了 NOWAIT,或在另一個工作階段持有衝突鎖時逾時到期。

SQLSTATE
55P03
名稱
lock_not_available
類別
物件不在先決狀態
錯誤訊息
could not obtain lock on row in relation "orders"

它的意義

通常 PostgreSQL 會一直等到取得鎖定為止。55P03 表示該陳述式被指示不要等待: SELECT ... FOR UPDATE NOWAIT 或 LOCK TABLE ... NOWAIT 若鎖已被佔用就會立即失敗;而當 lock_timeout 已設定時,任何語句只要等到那個期限就會失敗。鎖定逾時會回報 canceling statement due to lock timeout 相同的程式碼。

持有鎖的其他工作階段不受影響。在交易區塊內,失敗的陳述式會中止整個交易。

常見原因

  • FOR UPDATE NOWAIT, FOR SHARE NOWAIT 或 LOCK TABLE ... NOWAIT 碰到被另一個交易鎖定的資料列或資料表。
  • A lock_timeout 已過期,通常發生在 DDL 上,例如 ALTER TABLE,這需要獨佔鎖定,而且會排在執行很久的查詢後面。
  • 長時間執行的交易,或在交易中閒置卻持有鎖的工作階段。

如何修正

  1. 找到持有鎖定的工作階段。

    SELECT pid, usename, state, xact_start, query
    FROM pg_stat_activity
    WHERE pid IN (
        SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity
    );
  2. 對於工作佇列,請略過被鎖住的資料列,而不是直接失敗。

    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    LIMIT 10
    FOR UPDATE SKIP LOCKED;
  3. 結構描述變更時,請刻意使用較短的 lock_timeout 刻意如此並重試,所以 ALTER 不會排在長查詢後面並封鎖其他人。

    SET lock_timeout = '3s';
    ALTER TABLE orders ADD COLUMN note text;
  4. 結束或縮短造成封鎖的交易;如果它已被遺棄,請將其終止。

    SELECT pg_terminate_backend(12345);  -- the blocking pid

安全地重現問題

在兩個連線中執行;之後用 ROLLBACK 結束工作階段 1。

-- Session 1:
CREATE TABLE q (id int PRIMARY KEY);
INSERT INTO q VALUES (1);
BEGIN; SELECT * FROM q WHERE id = 1 FOR UPDATE;
-- Session 2:
SELECT * FROM q WHERE id = 1 FOR UPDATE NOWAIT;  -- ERROR: could not obtain lock on row in relation "q"

相關錯誤

更多物件不處於前置狀態的錯誤

程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 PostgreSQL

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 PostgreSQL 和你使用的其他資料庫。