追踪死锁 [英] Tracking down deadlocks

问题描述

我正在对postgres数据库进行一系列数据挖掘，并且已经遇到了一个有问题的死锁问题。问题是，

postgres正在检测它们然后解决有问题的过程，并且

我无法弄清楚是什么导致了它们。我有大量的选择查询

（但没有更新），然后是一个插入表中的查询。

没有从该表中选择任何内容。那么死锁会在哪里？


pg_stat_activity有一个名为current_query的列，看起来好像有点跟踪它有用，但它不是人口稠密。


哦，我正在运行7.4.2。

----------------- ----------（广播结束）---------------------------

提示5：您是否检查过我们广泛的常见问题解答？

http://www.postgresql.org/docs/faqs/FAQ.html

I''m doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can''t figure out what''s causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?

pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it''s not being populated.

Oh, I''m running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

推荐答案

嗨Ben ，

查看此邮件列表中的外键和死锁。

简短信息：

Postgres在更新子记录时专门锁定外键的引用记录

关系因此，如果一个插入查询的多次运行（在
不同的事务中）可能会导致死锁，如果它们更新了以相反的顺序引用相同父键的行。

检查你的外国钥匙......


HTH，

Csaba。


周三， 2004-06-16 17:33，Ben写道：

Hi Ben,

Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...

HTH,
Csaba.

On Wed, 2004-06-16 at 17:33, Ben wrote:

我正在对postgres数据库进行一系列数据挖掘，并且遇到了一个有趣的死锁问题。问题是，postgres正在检测它们然后解决有问题的过程，并且我无法弄清楚是什么导致了它们。我有大量的选择查询
（但没有更新），然后单个查询插入到表中。
没有从该表中选择。那么死锁在哪里呢？

pg_stat_activity有一个名为current_query的列，它似乎对跟踪它有用，但它没有被填充。

---------------------------（结束广播）---------------------------
提示5：您查看了我们广泛的常见问题解答吗？

http://www.postgresql.org/docs/faqs/ FAQ.html

I''m doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can''t figure out what''s causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?

pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it''s not being populated.

Oh, I''m running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------（广播结束）---------------------------

提示5：您是否查看了我们广泛的常见问题解答？

http://www.postgresql.org/docs/ faqs / FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

嗨Ben，


查看此邮件列表中的外键和死锁。

简短信息：

Postgres在更新子记录时专门锁定外键的引用记录

关系因此，如果一个插入查询的多次运行（在
不同的事务中）可能会导致死锁，如果它们更新了以相反的顺序引用相同父键的行。

检查你的外国钥匙......


HTH，

Csaba。


周三， 2004-06-16 17:33，Ben写道：

Hi Ben,

Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...

HTH,
Csaba.

On Wed, 2004-06-16 at 17:33, Ben wrote:

我正在对postgres数据库进行一系列数据挖掘，并且遇到了一个有趣的死锁问题。问题是，postgres正在检测它们然后解决有问题的过程，并且我无法弄清楚是什么导致了它们。我有大量的选择查询
（但没有更新），然后单个查询插入到表中。
没有从该表中选择。那么死锁在哪里呢？

pg_stat_activity有一个名为current_query的列，它似乎对跟踪它有用，但它没有被填充。

---------------------------（结束广播）---------------------------
提示5：您查看了我们广泛的常见问题解答吗？

http://www.postgresql.org/docs/faqs/ FAQ.html

I''m doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can''t figure out what''s causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?

pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it''s not being populated.

Oh, I''m running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------（广播结束）---------------------------

提示5：您是否查看了我们广泛的常见问题解答？

http://www.postgresql.org/docs/ faqs / FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Ben< be *** @ silentmedia.com>写道：

Ben <be***@silentmedia.com> writes:

我正在对postgres数据库进行大量的数据挖掘，并且遇到了一个有趣的死锁问题。问题是，postgres正在检测它们然后解决有问题的过程，并且我无法弄清楚是什么导致了它们。

I''m doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can''t figure out what''s causing them.

死锁中涉及的进程应在

DETAIL消息中标识死锁错误。也许您正在使用客户端

代码，但没有向您显示详细信息？如果是这样，请查看postmaster

log。您可以将消息中提到的过程PID关联到

pg_stat_activity.procpid。


问候，tom lane


---------------------------（广播结束）----------------- ----------

提示8：解释分析是你的朋友

The processes involved in the deadlock should be identified in the
DETAIL message for the deadlock error. Perhaps you are using client
code that doesn''t show you the DETAIL? If so, look in the postmaster
log. You can correlate the process PIDs mentioned in the message to
pg_stat_activity.procpid.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

这篇关于追踪死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！