[GENERAL] Deadlock when one process opens two separate connections?

[Kaloyan Iliev]

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

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Deadlock when one process opens two separate connections?

[Florian Weimer]

* Kaloyan Iliev:

 Probbly beacause both transactions started from one process?

Yes, the deadlock detector isn't psychic.  It can't know about lock
ordering constraints which are external to PostgreSQL.

-- 
Florian Weimer[EMAIL PROTECTED]
BFK edv-consulting GmbH   http://www.bfk.de/
Durlacher Allee 47tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match