如何在 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 操作过程中获取。

当查询或事务获取了表上的锁定时，该锁定将在查询或事务持续期间一直存在。除 AccessShareLock 之外，其他等待获取相同锁定的查询或事务将被阻止。有关查询可能无法响应的原因的详细信息，请参阅查询故障排除。

解决方法

**注意：**以下解决方案适用于未部署多可用区的用户。

确定持有锁定的会话进程，然后停止该进程。如果该进程未停止，请重启您的集群。

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

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 |

如果 granted 列中的结果为 f (false)，则表明其他会话中的事务正持有此锁定。blocking_pid 列将显示当前持有此锁定的会话的进程 ID (PID)。在前面的示例中，PID 19813 持有此锁定。

要释放锁定，请等待持有此锁定的事务完成。

您也可以运行以下命令来手动终止该进程：

select pg_terminate_backend(PID);

**注意：**在前面的命令中，请将 PID 替换为您的进程 ID。

当您停止该进程时，会话将回滚所有正在运行的事务并释放会话中的所有锁定。然后，等待获取锁定的其他事务将领取该锁定。

注意：PG_TERMINATE_BACKEND(PID) 命令返回值 1，表示该命令已成功请求停止进程。但是，该进程可能仍未停止。最佳做法是检查 STV_SESSIONS 和其他相关的系统表以确认该进程已停止。

如果 PG_TERMINATE_BACKEND(PID) 未成功停止进程，请使用 Amazon Redshift 控制台或运行 REBOOT_CLUSTER 来重启集群并停止进程。

相关信息

LOCK

并发写入事务中可能出现的死锁情况