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