如何释放可能的Postgres行锁？

Question

我通过phpPgAdmin界面在一个大的PostgreSQL表上运行了一条更新语句。由于运行时间过长，这个操作超时了。

现在我可以更新该表中的一些行，但不是全部。尝试更新某些行会导致卡住。

这些行被锁定了吗？如何允许更新这些行？

- Liam

Answer 1

可以查看锁定情况。

这里提供了一种比直接使用pg_locks更简单的视图：

CREATE OR REPLACE VIEW public.active_locks AS 
 SELECT t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
   FROM pg_locks l
   JOIN pg_stat_all_tables t ON l.relation = t.relid
  WHERE t.schemaname <> 'pg_toast'::name AND t.schemaname <> 'pg_catalog'::name
  ORDER BY t.schemaname, t.relname;

然后您只需从视图中进行选择：

SELECT * FROM active_locks;

并通过以下方式终止它：

SELECT pg_cancel_backend('%pid%');

其他解决方案： http://wiki.postgresql.org/wiki/Lock_Monitoring

- Chris

---

Postgres 中没有名为 active_locks 的视图或表。您是不是指的是 pg_locks？ - user330315

你是正确的，我们的数据库中有一个自定义视图，我已经修改了答案。 - Chris

请执行以下命令以终止进程：使用“SELECT pg_cancel_backend(pid) FROM active_locks;”代替。 - Gilbou

使用"SELECT pg_terminate_backend('pid')"强制终止进程。 - Deepak Kumar

当我运行pg_cancel_backend时，我收到错误消息：“ERROR: invalid input syntax for integer: "pid"”。 - user1724295

Answer 2

简单易懂：

从pg_locks获取活动锁：

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid 
ORDER BY relation asc;

从上面的结果中复制pid（例如：14210），并将其替换为下面的命令。

SELECT pg_terminate_backend(14210)

- chiru

---

pg_cancel_backend(14210)将使用SIGINT而不是SIGTERM取消查询，文档pg_terminate_backend(14210)将终止会话以及查询。 - ps2goat

Answer 3

释放Postgres中可能存在的锁定，我通常按照以下步骤进行操作。

1. Find long-running queries in your DB by running the following query. This will help you fetch the PIDs of the long-running query which is blocking your update.

   SELECT
   pid,
   now() - pg_stat_activity.query_start AS duration,
   query,
   state
   FROM pg_stat_activity
   WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
   

2. or if you can find out which processes are holding a lock on a particular table by running this query

   SELECT *
   FROM pg_locks l
   JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
   WHERE t.relname = 'Bill';
   

3. Once you figure out the PID which is 'active' and blocking your update you can kill it by running this query. It takes some time to kill the process.

   SELECT pg_cancel_backend(__pid__);
   

4. Check by running Query 2 if the process is killed. If it still is active then kill this process by running this query.

   SELECT pg_terminate_backend(__pid__);
   

- Saurabh Saha

Answer 4

你正在运行哪个版本的PostgreSQL？以下假设你运行的是8.1.8或更新版本（它可能也适用于早期版本，但我不确定）。

我猜你指的是phpPgAdmin超时了——PostgreSQL后端将花费足够的时间来完成一个查询/更新。在这种情况下，原始会话可能仍然存在，而UPDATE查询仍在运行。建议在托管PostgreSQL服务器进程的机器上运行以下查询（摘自PostgreSQL文档的第24章），查看会话是否仍然存在：

ps auxwww|grep ^postgres

会显示几行: 一个是 postmaster 主进程，还有一个是 "writer"、"stats buffer" 和 "stats collector" 进程。任何剩余的行都是为服务于数据库连接的进程所用，这些行将包含用户名和数据库名称。

希望从中可以看出您执行原始 UPDATE 的会话是否仍然存在。虽然理论上您可以通过从系统视图 pg_stat_activity 中进行 SELECT 来查找更详细的信息，但默认情况下 PostgreSQL 并未设置以填充最有用的字段（如 current_query 和 query_start）。请参阅第 24 章以了解如何在将来启用此功能。

如果您看到会话仍然存在，请终止它。您需要以运行该进程的用户身份（通常是 postgres）或 root 登录才能这样做 - 如果您自己不运行服务器，请让您的 DBA 为您执行此操作。

还有一件事：对于更新表中的行，PostgreSQL 避免使用锁定。相反，它允许每个写事务创建数据库的新“版本”，当事务提交时，该版本成为“当前版本”，前提是它不与其他事务同时进行的更新发生冲突。因此，我怀疑您看到的“挂起”是由其他原因引起的 - 尽管是什么原因，我不确定。（您是否检查了一些显而易见的事情，例如包含数据库的磁盘分区是否已满？）

- j_random_hacker

---

太好了，这个方法可行，谢谢。请注意，当我杀掉导致问题的进程时，一些其他等待锁被释放的进程得以运行。这引起了一些进一步的问题。在杀死第一个有问题的进程之前，我应该杀死标记为“WAITING”的进程，即在运行ps auxwww|grep ^postgres命令时。 - Liam

如果你看到会话仍然存在，请将其终止。对于新手，找到显示为 postgres 15398 ... idle in transaction 或其他类似的内容，并在 Putty 中键入 kill 15398。 - Noumenon

Answer 5

这将清除所有表上的所有锁定。

SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE pid <> pg_backend_pid();

请在生产数据库上谨慎使用。

- Jakobovski

Answer 6

我从未使用过PostreSql，但如果它与其他数据库类似，我会建议您终止持有锁的连接/事务。

- Mladen Prajdic