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

简短描述

锁定是一项保护机制，用于控制可同时访问表的会话数量。锁定还决定可以在这些会话中执行的操作。大多数关系数据库使用行级锁定。但是，Amazon Redshift 使用的是表级锁定。如果在用户表或 DML 查询上执行频繁的 DDL 语句，则您可能会遇到锁定冲突。

Amazon Redshift 具有三种锁定模式：

• **AccessExclusiveLock：**主要在 DDL 操作过程中获取，如 ALTER TABLE、DROP 或 TRUNCATE。AccessExclusiveLock 将阻止其他所有锁定尝试。
• **AccessShareLock：**在 UNLOAD、SELECT、UPDATE 或 DELETE 操作过程中获取。AccessShareLock 仅阻止 AccessExclusiveLock 尝试。AccessShareLock 不会阻止尝试对表进行读取和写入操作的其他会话。
• **ShareRowExclusiveLock：**在 COPY、INSERT、UPDATE 或 DELETE 操作过程中获取。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 |

如果 granted 列中的结果为 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 重启数据库来重启集群，从而执行重启。

---

相关信息

锁定（Amazon Redshift）

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