Deadlock when one process opens two separate connections?

From: Kaloyan Iliev <news1(at)faith(dot)digsys(dot)bg>
To: pgsql-general(at)postgresql(dot)org
Subject: Deadlock when one process opens two separate connections?
Date: 2006-09-26 10:51:36
Message-ID: 45190638.8060704@faith.digsys.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Yestarday in the software, which my company developes, there happens a
deadlock.
One process opens a transaction, SELECT FOR UPDATE some rows from a
table and then calls another function.
The second function didn't receive (by programmers mistake) database
handler and opens its own transaction and try to modify the same rows.
And in this moment the second transaction WAITS. The first transaction
cannot finish because waits for result from the second
and the second transaction cannot modify the rows because the first
SELECT FOR UPDATE them. In this case the process hangs for 2 hours
when I restart the database. So why didn't the data base discover the
deadlock and cancel one of the transactions?
Probbly beacause both transactions started from one process?
And as second question I look carefuly over the code and select for
update was called on one table of a complex query but for me it seems to
block all
result rows from all tables. I am not absolutely sure about this but I
will make some test and post the result if I prove there is a problem.
We use version:
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518

Kaloyan Iliev

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Florian Weimer 2006-09-26 10:56:29 Re: Deadlock when one process opens two separate connections?
Previous Message Jon Lapham 2006-09-26 10:25:03 Re: Restart after poweroutage