如何释放可能的Postgres行锁？ [英] How to release possible Postgres row locks?

问题描述

我通过phpPgAdmin接口在大型PostgreSQL表上运行了一条更新语句。

I ran an update statement on a large PostgreSQL table through the phpPgAdmin interface. This timed out as it ran for too long.

我现在可以更新该表中的某些行，但不是全部。尝试更新某些行会挂起。

I can now update some rows from that table but not all. Trying to update some rows will hang.

行是否被锁定？

推荐答案

您正在运行什么版本的PostgreSQL？以下内容假设使用8.1.8或更高版本（我也不知道它可能也适用于早期版本）。

What version of PostgreSQL are you running? The following assumes 8.1.8 or later (it may apply to earlier versions too, I don't know).

我认为您的意思是phpPgAdmin超时- PostgreSQL后端将花费完成查询/更新的时间。在这种情况下，原始会话可能仍然存在，并且UPDATE查询仍在运行。我建议运行以下查询（取自PostgreSQL文档的第24章 ）在托管PostgreSQL服务器进程的计算机上，以查看会话是否仍然存在：

I presume that you mean that phpPgAdmin timed out -- the PostgreSQL backend will take as long as it takes to complete a query/update. In that case, it's possible that the original session is still alive and the UPDATE query is still running. I suggest running the following query (taken from chapter 24 of the PostgreSQL docs) on the machine that hosts the PostgreSQL server process, to see whether the session is still alive:

ps auxwww|grep ^postgres

应该出现几行： postmaster 主进程，写程序，统计缓冲区和统计收集器进程各1个。其余所有行均用于为数据库连接提供服务的进程。这些行将包含用户名和数据库名称。

Several rows should appear: 1 for the postmaster master process, and 1 each for "writer", "stats buffer", and "stats collector" processes. Any remaining lines are for processes serving DB connections. These lines will contain the username and database name.

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

Hopefully, from that you can see whether the session you performed the original UPDATE in is still hanging around. Although in theory you could find more detailed info by SELECTing from the system view pg_stat_activity, by default PostgreSQL is not set up to populate the most useful fields (such as current_query and query_start). See chapter 24 for how to enable this in the future.

如果您看到会话仍在那儿，请杀死它。您需要以运行进程的用户身份登录（通常为 postgres ）或以root用户身份登录-如果您自己未运行服务器，请获取DBA

If you see the session is still there, kill it. You will need to be logged in as the user running the process (usually postgres) or root to do so -- if you don't run the server yourself, get your DBA to do this for you.

另一件事：为了更新表中的行，PostgreSQL避免使用锁。取而代之的是，它允许每个写入事务创建一个新的数据库版本，只要提交该事务与其他事务同时进行的更新不冲突，该新版本就会在提交事务后成为当前版本。因此，我怀疑您看到的挂起是由其他原因引起的-尽管不确定，但我不确定。 （您是否检查过明显的事情，例如包含数据库的磁盘分区是否已满？）

One more thing: for updating rows in a table, PostgreSQL avoids using locks. Instead, it allows every writing transaction to create a new "version" of the DB, which becomes the "current version" when the transaction is committed, provided it doesn't conflict with updates made in the meantime by other transactions. So I suspect the "hanging" you're seeing is caused by something else -- though what, I'm not sure. (Have you checked the obvious things, like whether the disk partition containing the DB is full?)

这篇关于如何释放可能的Postgres行锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！