postgresql Postgres 数据库锁定：查询永远运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26596566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres Database locked: Queries running forever
提问by user3422637
One of my python scripts ran some ALTER TABLE queries on the Postgres database. Something wrong happened and the tables got locked. When I run any query on any of those tables, it tells me Query Running and nothing happens. Currently, I am able to remove this lock only by shutting down my system and restarting it. Please tell me a better method. This is a Windows host.
我的一个 Python 脚本在 Postgres 数据库上运行了一些 ALTER TABLE 查询。发生了一些错误，桌子被锁上了。当我对这些表中的任何一个运行任何查询时，它告诉我查询正在运行，但没有任何反应。目前，我只能通过关闭系统并重新启动来移除此锁。请告诉我一个更好的方法。这是一个 Windows 主机。
回答by vyegorov
You should check for locks:
您应该检查锁：
SELECT l.*,a.*
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT granted;
You'll see a list of waitingsessions. And the following:
您将看到等待会话的列表。以及以下内容：
SELECT l.*,a.*
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE granted
AND (database,relation) IN (SELECT database,relation
FROM pg_locks WHERE NOT granted);
will give you a list of blockingsessions. If you use psql, use expandedoutputto get column-per-row output, better to view such information.
会给你一个阻止会话的列表。如果使用psql, 使用expandedoutput来获取 column-per-row 输出，最好查看此类信息。
The following SQL scriptwill display blocking tree (if there are blocked sessions), sessions on the top of each branch (yes, quite often there're several branches) will be the blocking ones.
下面的 SQL 脚本将显示阻塞树（如果有阻塞的会话），每个分支顶部的会话（是的，通常有几个分支）将是阻塞的。
I advise you to also have a look at this wiki pageand this question: Postgresql DROP TABLE doesn't work(though it speaks bout DROP TABLEthere, it might help).
我建议你也看看这个 wiki 页面和这个问题：Postgresql DROP TABLE 不起作用（虽然它在DROP TABLE那里说话，但它可能会有所帮助）。
In your case, I recommend to identify blockingsessions and try to find out whythey're blocking. Most typical case in my experience —?somebody forgot to press enter after COMMITand went out for lunch. If you're sure this will not hurt your system, you can kill blockingsession:
在你的情况，我建议，以确定阻塞会话并试图找出为什么他们封锁。在我的经历中最典型的案例——有人忘记按回车键COMMIT出去吃午饭了。如果您确定这不会损害您的系统，您可以终止阻塞会话：
SELECT pg_terminate_backend(pid);
回答by Anvesh
Reference taken from this article.Find blocking sessions:
参考来自这篇文章。查找阻塞会话：
SELECT
pl.pid as blocked_pid
,psa.usename as blocked_user
,pl2.pid as blocking_pid
,psa2.usename as blocking_user
,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
ON pl2.pid = psa2.pid
ON pl.transactionid = pl2.transactionid
AND pl.pid != pl2.pid
WHERE NOT pl.granted;
