PostgreSQL 在事务诊断和读取 pg_locks 中空闲
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3312929/
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
PostgreSQL Idle In Transaction Diagnosis and Reading pg_locks
提问by KRH
Setup: multiple webservers, running mod_wsgi, Apache, and pgbouncer which connects to the shared DB running Postgres 8.3.6. Application is running Django.
设置：多个网络服务器，运行 mod_wsgi、Apache 和 pgbouncer，后者连接到运行 Postgres 8.3.6 的共享数据库。应用程序正在运行 Django。
What we're seeing: 'idle in transaction' queries on the DB that hang out for a long time. In order to see them, I'll run something like this:
我们看到的是：长时间挂起的数据库上的“事务空闲”查询。为了查看它们，我将运行如下命令：
SELECT query_start, procpid, client_addr, current_query FROM pg_stat_activity
WHERE query_start < NOW() - interval '5 minutes';
Most results of course are just IDLE connections that pgbouncer is keeping open for use, but sometimes there will be these old 'IDLE in transaction' queries. I understand that this means that there is a query transaction which is waiting for something, or something which had a BEGIN but hasn't reached a COMMIT or ROLLBACK.
大多数结果当然只是 pgbouncer 保持打开以供使用的空闲连接，但有时会出现这些旧的“事务中的空闲”查询。我知道这意味着有一个查询事务正在等待某些东西，或者有一个 BEGIN 但尚未到达 COMMIT 或 ROLLBACK 的东西。
My next step was to try to use pg_locks to determine what the process is waiting on:
我的下一步是尝试使用 pg_locks 来确定进程正在等待什么：
select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g", pg_stat_activity.current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid
and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS>
order by query_start;
A lot of times, the result I get looks like so:
很多时候，我得到的结果是这样的：
relname | transactionid | mode | g | current_query | query_start | age | client_addr | procpid
---------+---------------+-----------------+---+-----------------------+------------------------------+-----------------+----------------+---------
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | ExclusiveLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
(10 rows)
I'm not sure how to read this (I guess it stems from not really understanding pg_locks). There's no relname, so is it saying that it's waiting on nothing? I thought that if granted was 'true', it had the lock. Since all these results are granted, is pg_locks showing me the locks that it has rather than what it's waiting for?
我不知道如何阅读这个（我猜它源于没有真正理解 pg_locks）。没有 relname，所以是说它什么都不等待？我认为如果授予是“真实的”，它就有了锁。既然所有这些结果都被授予了，pg_locks 是否向我展示了它拥有的锁而不是它正在等待的锁？
Right now I'm 'fixing' this by restarting Apache, which seems to shake the transactions loose, but obviously that's not a real solution. I'm looking for Postgres to give me a place on where to look to figure this out, especially since Django is supposed to manage its connections and transactions automatically.
现在我正在通过重新启动 Apache 来“修复”这个问题，这似乎使事务变得松散，但显然这不是真正的解决方案。我正在寻找 Postgres 给我一个地方来解决这个问题，特别是因为 Django 应该自动管理它的连接和事务。
回答by Matthew Wood
For Django specifically, this entry details why you see this issue:
特别是对于 Django，此条目详细说明了您看到此问题的原因：
I say "specifically" here because the real problem is web frameworks/drivers/ORMs working all the time in a transaction-based mode (and sometimes calling rollback after every freakin' SELECT query) when they should really be running in an Auto-Commit mode and handling the need for transactions only on an as-needed basis. The Apache::Sessions PostgreSQL persistence module was a disaster (at least a few years ago) as it only closed transactions when it was garbage collected. Yikes!
我在这里说“特别”是因为真正的问题是 Web 框架/驱动程序/ORM 一直在基于事务的模式下工作（有时在每个奇怪的 SELECT 查询后调用回滚），而实际上它们应该在自动提交中运行模式并仅根据需要处理交易需求。Apache::Sessions PostgreSQL 持久性模块是一场灾难（至少在几年前），因为它只在垃圾收集时关闭事务。哎呀！
