SQL 如何释放可能的 Postgres 行锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1063043/
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
How to release possible Postgres row locks?
提问by Liam
I ran an update statement on a large PostgreSQL table through the phpPgAdmin interface. This timed out as it ran for too long.
我通过 phpPgAdmin 界面在大型 PostgreSQL 表上运行更新语句。这超时了，因为它运行的时间太长了。
I can now update some rows from that table but not all. Trying to update some rows will hang.
我现在可以更新该表中的一些行，但不是全部。尝试更新某些行将挂起。
Are the rows locked? How can I allow these rows to be updated?
行是否被锁定？如何允许更新这些行？
采纳答案by j_random_hacker
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).
你运行的是什么版本的 PostgreSQL？以下假设为 8.1.8 或更高版本（它也可能适用于早期版本，我不知道）。
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:
我认为您的意思是 phpPgAdmin 超时——PostgreSQL 后端将花费与完成查询/更新所需的时间一样长的时间。在这种情况下，原始会话可能仍处于活动状态并且 UPDATE 查询仍在运行。我建议在托管 PostgreSQL 服务器进程的机器上运行以下查询（取自PostgreSQL 文档的第 24 章），以查看会话是否仍然有效：
ps auxwww|grep ^postgres
Several rows should appear: 1 for the postmastermaster 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.
应该出现几行：1 行用于postmaster主进程，1 行用于“编写器”、“统计缓冲区”和“统计收集器”进程。任何剩余的行都用于为数据库连接提供服务的进程。这些行将包含用户名和数据库名称。
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_queryand query_start). See chapter 24 for how to enable this in the future.
希望从中您可以看到您执行原始 UPDATE 的会话是否仍然存在。尽管理论上您可以通过SELECTing 从系统视图中找到更详细的信息pg_stat_activity，但默认情况下 PostgreSQL 并未设置为填充最有用的字段（例如current_query和query_start）。请参阅第 24 章了解将来如何启用此功能。
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.
如果您看到会话仍然存在，请终止它。您需要以运行该进程的用户（通常是postgres）或 root 身份登录才能这样做——如果您自己不运行服务器，请让您的 DBA 为您执行此操作。
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?)
还有一件事：为了更新表中的行，PostgreSQL 避免使用锁。相反，它允许每个写入事务创建一个新的数据库“版本”，当事务提交时它成为“当前版本”，前提是它不与其他事务同时进行的更新冲突。所以我怀疑你看到的“挂起”是由其他原因引起的——尽管我不确定是什么。（您是否检查过明显的事情，例如包含 DB 的磁盘分区是否已满？）
回答by Chris
It's possible to see the locks.
可以看到锁。
Here is a view to make it a bit easier than using pg_locks directly:
这是一个比直接使用 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;
Then you just select from the view:
然后您只需从视图中选择：
SELECT * FROM active_locks;
And kill it with:
并用以下命令杀死它：
SELECT pg_cancel_backend('%pid%');
Other solutions: http://wiki.postgresql.org/wiki/Lock_Monitoring
回答by chiru
Simple:
简单的：
Get the active locks from pg_locks:
从 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;
选择 t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t 其中 l.relation=t.relid 按关系 asc 排序；
Copy the pid(ex: 14210) from above result and substitute in the below command.
从上面的结果复制pid（例如：14210）并替换为下面的命令。
SELECT pg_terminate_backend('14210')
选择 pg_terminate_backend('14210')
回答by Saurabh Saha
To release possible locks from Postgres I generally follow these in sequence.
为了从 Postgres 释放可能的锁，我通常按顺序执行这些操作。
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';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';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__);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__);
通过运行以下查询在您的数据库中查找长时间运行的查询。这将帮助您获取阻止更新的长时间运行的查询的 PID。
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';或者您是否可以通过运行此查询找出哪些进程持有特定表的锁
SELECT * FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'Bill';一旦您找出“活动”的 PID 并阻止您的更新，您可以通过运行此查询来终止它。终止进程需要一些时间。
SELECT pg_cancel_backend(__pid__);通过运行查询 2 来检查进程是否被终止。如果它仍然处于活动状态，则通过运行此查询来终止此进程。
SELECT pg_terminate_backend(__pid__);
回答by Jakobovski
This will clear all locks on all tables.
这将清除所有表上的所有锁。
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
回答by Mladen Prajdic
i've never worked with PostreSql but if it's similar to others i'd say you have to kill the connection/end the transaction that's holding the locks.
我从未使用过 PostreSql 但如果它与其他人类似，我会说你必须终止连接/结束持有锁的事务。
