如何在 Amazon Redshift 中檢測和釋放鎖？

簡短說明

鎖定是一種保護機制，可控制同時存取表格的工作階段數目。鎖定也會決定哪些作業可以在這些工作階段中執行。大多數關聯式資料庫都使用列層級鎖定。但是，Amazon Redshift 使用表級鎖定。如果您在使用者表格或 DML 查詢上執行頻繁的 DDL 陳述式，您可能會遇到鎖定衝突。

Amazon Redshift 有三種鎖定模式：

• **AccessExclusiveLock:**主要是在 DDL 作業期間取得，例如 修改表格、刪除或截斷。AccessExclusiveLock 會封鎖所有其他鎖定嘗試。
• **AccessShareLock:**在卸載，選擇，更新或刪除作業期間獲得。AccessShareLock blocks 僅封鎖存取權限集合鎖定嘗試。AccessShareLock 不會封鎖試圖讀取或寫入表格上的其他工作階段。
• **ShareRowExclusiveLock:**在複製、插入、更新或刪除作業期間取得。ShareRowExclusiveLock 封鎖 AccessExclusiveLock 和其他 ShareRowExclusiveLock 嘗試，但不會封鎖 AccessShareLock 嘗試。

當查詢或交易取得資料表上的鎖定時，鎖定會在查詢或交易期間保持不變。正在等待取得相同鎖定的其他查詢或交易會遭到封鎖，但 AccessShareLock 除外。如需查詢為何會停止的詳細資訊，請參閱查詢停止。

若要解決鎖定問題，請識別持有鎖定的工作階段 (PID)，然後終止工作階段。如果工作階段未終止，請重新啟動叢集。

解決方法

執行查詢以識別持有鎖定的工作階段：

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

輸出看起來類似於：

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         |
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock     |   351959 | lineorder | f       |        19813 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

如果授予欄位中的結果為 ** f ** (false)，則另一個工作階段中的交易會持有鎖定。blocking_pid欄位顯示持有鎖定的會話的進程 ID。在這個范例中，PID 19813 正在持有鎖。

若要解除鎖定，請等待保留鎖定的交易完成。您也可以執行下列命令來手動終止工作階段：

select pg_terminate_backend(PID);

終止 PID 會重新運行所有正在運行的交易，並釋放會話中的所有鎖定。然後，正在等待獲取鎖定的其他交易聲明鎖定。

注意： PG_TERMINATE_BACKEND(PID)傳回的值「1」，通常表示成功的終站止了對 PID 的請求。但是，這並不能保證 PID 實際上已終止。在某些情況下，無法根據其內部狀態確定 PID。因此，最好也檢查 STV\ _SESSIONS（和其他相關的系統表）以確認 PID 是否實際終止。

如果 PG_TERMINATE_BACKEND(PID) 未成功終止工作階段，請重新啟動叢集以終止該進程。重新開機可透過 Amazon Redshift 主控臺進行叢集重新啟動，或使用 REBOOT_CLUSTER ](https://docs.aws.amazon.com/redshift/latest/dg/r_REBOOT_CLUSTER.html)透過資料庫重新[開機來執行。

相關資訊

鎖(Amazon Redshift)

並行寫入交易的潛在死結情況