Re: Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?

On 26.04.2011 09:45, Prakash Itnal wrote:
> I have create the following tables:
> 1. rnc table
> CREATE TABLE act_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);
> 2. rncgen table
> CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY, rncsubObj_Cnt
> integer, rncgen_data BYTEA);
> 3. iuo table which has a foreign key reference to rnc table
> CREATE TABLE act_iuo(iuo_id integer NOT NULL primary key, rnc_id integer NOT
> NULL, iuo_data BYTEA, FOREIGN KEY(rnc_id) references act_rnc(rnc_id) on
> delete cascade);
>
> Now i open two transactions (separate session with psql). In the first
> transaction I give the following sql sequence:
> begin;
> update act_rnc set rnc_data='rnc_data' where rnc_id=1;
>
> The transaction will be open.
>
> In a second transaction i give the following sql sequence:
> begin;
> insert into act_iuo values (1,1,'iuo_data');
>
> --> now the second transaction is blocked. I work with PostgreSQL 9.0.
>
>...
>
> I assume that the access to act_rnc_pkey causes the blocking, however why?
> Or how I can resolve the blocking (commit one transaction solves the
> problem, but should Postgres not recognize the blocking situation and
> release one transaction?). Is this an error in Postgres?

No. It's the application's responsibility to commit the first
transaction. PostgreSQL won't kill your transaction just because it's
blocking some other transaction.

PostgreSQL does detect deadlocks, but the above is not a deadlock.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com