如何在 Amazon Redshift 中检测和释放锁定？

简短描述

当频繁地对用户表或数据操作语言 (DML) 查询执行数据定义语言 (DDL) 语句时，可能会遇到锁定冲突。

Amazon Redshift 有以下三种锁定模式：

• AccessExclusiveLock 阻止所有其他锁定尝试，主要在 DDL 操作（例如 ALTER TABLE、DROP 或 TRUNCATE）过程中获取。
• AccessShareLock 仅阻止 AccessExclusiveLock 尝试，在 UNLOAD、SELECT、UPDATE 或 DELETE 操作过程中获取。AccessShareLock 不阻止尝试对表进行读取或写入操作的其他会话。
• ShareRowExclusiveLock 阻止 AccessExclusiveLock 和其他 ShareRowExclusiveLock 尝试，但不阻止 AccessShareLock 尝试。ShareRowExclusiveLock 在 COPY、INSERT、UPDATE 或 DELETE 操作过程中获取。

要解决此问题，请确定问题表锁定，确定问题查询（如有必要），然后释放问题表锁定。

解决方法

**注意：**如果您在运行 AWS 命令行界面 (AWS CLI) 命令时收到错误，请参阅 AWS CLI 错误故障排除。此外，请确保您使用的是最新版本的 AWS CLI。

检测锁定

要确定占用锁定的进程，请运行以下查询：

SELECT
  a.txn_start,
  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,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(trim(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
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 SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = 'database name'
  AND tablename = 'table name';

要检测特定表中的锁定，请将 database name 替换为您的数据库名称，将 table name 替换为您的表名称。

**注意：**您可以在 Amazon Redshift 预置集群和 Amazon Redshift Serverless 上运行上述查询。

输出示例：

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

如果 granted（授予）列中的结果为 f (false)，则事务正在等待锁定，因为该锁定由另一个事务占用。blocking_pid 列显示当前占用此锁定的会话的进程 ID (PID)。

检测问题查询

如果特定表的锁定问题持续出现，请使用 SYS_QUERY_HISTORY 来查看哪个查询导致了问题。

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

释放锁定

要释放锁定，请完成以下步骤：

1. 等到占用锁定的事务被释放。
2. 运行 PG_TERMINATE_BACKEND 函数以手动释放锁定。
   **注意：**当命令成功请求停止进程时，PG_TERMINATE_BACKEND(PID) 查询返回值 1。最佳做法是检查 SYS_SESSION_HISTORY 以确认该进程已停止。
3. 对于 Redshift 预置集群，如果 PG_TERMINATE_BACKEND 未成功停止进程，请运行 REBOOT_CLUSTER 函数。
   **注意：**REBOOT_CLUSTER 会在不关闭连接的情况下重新启动集群。
4. 对于 Redshift 预置集群，如果 REBOOT_CLUSTER 未成功停止进程，请从 Amazon Redshift 控制台重启集群或运行 reboot-cluster AWS CLI 命令。
   **注意：**reboot-cluster 会关闭所有当前连接。