Blocking Issue

From: "Sander, Ingo (NSN - DE/Munich)" <ingo(dot)sander(at)nsn(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Blocking Issue
Date: 2011-02-09 10:15:47
Message-ID: 9EB22E4572ECF74AAFEAE743C74D26B2042861F4@DEMUEXC005.nsn-intra.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,
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.

Some outputs:
select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
tuple | 16385 | 16427 | 0 | 8 | | | | | | 3/80 | 9230 | ShareLock | t
relation | 16385 | 10985 | | | | | | | | 4/247 | 16535 | AccessShareLock | t
virtualxid | | | | | 4/247 | | | | | 4/247 | 16535 | ExclusiveLock | t
relation | 16385 | 16443 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t
transactionid | | | | | | 584 | | | | 3/80 | 9230 | ExclusiveLock | t
virtualxid | | | | | 3/80 | | | | | 3/80 | 9230 | ExclusiveLock | t
relation | 16385 | 16433 | | | | | | | | 3/80 | 9230 | AccessShareLock | t
relation | 16385 | 16427 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t
virtualxid | | | | | 5/535 | | | | | 5/535 | 2814 | ExclusiveLock | t
transactionid | | | | | | 583 | | | | 5/535 | 2814 | ExclusiveLock | t
relation | 16385 | 16449 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t
relation | 16385 | 16427 | | | | | | | | 3/80 | 9230 | RowShareLock | t
transactionid | | | | | | 583 | | | | 3/80 | 9230 | ShareLock | f
relation | 16385 | 16433 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t
(14 rows)

select relname, pg_class.oid from pg_class;
act_rnc_pkey | 16433
pg_inherits_parent_index | 2187
pg_inherits_relid_seqno_index | 2680
pg_toast_16435 | 16438
pg_trigger_oid_index | 2702
pg_toast_16435_index | 16440
act_rncgen | 16435
act_rncgen_pkey | 16441
pg_toast_16443 | 16446
pg_toast_16443_index | 16448
act_iuo_pkey | 16449
pg_amop | 2602
act_iuo | 16443
pg_largeobject | 2613
act_rnc | 16427
pg_toast_11361 | 11363
pg_toast_11361_index | 11365
pg_toast_11366_index | 11370

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?

Best Regards
Ingo Sander

Best Regards/mfG
Ingo Sander
=========================================================
Nokia Siemens Networks GmbH &Co. KG
NWS EP I&V Platf Technical Service DE

St.-Martin-Str. 76
D-81541 München
*Tel.: +49-89-515938390
*ingo(dot)sander(at)nsn(dot)com

Nokia Siemens Networks GmbH & Co. KG
Sitz der Gesellschaft: München / Registered office: Munich
Registergericht: München / Commercial registry: Munich, HRA 88537
WEEE-Reg.-Nr.: DE 52984304
Persönlich haftende Gesellschafterin / General Partner: Nokia Siemens Networks Management GmbH
Geschäftsleitung / Board of Directors: Dr. Hermann Rodler, Lydia Sommer, Olaf Horsthemke
Vorsitzender des Aufsichtsrats / Chairman of supervisory board: Herbert Merz
Sitz der Gesellschaft: München / Registered office: Munich
Registergericht: München / Commercial registry: Munich, HRB 163416

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2011-02-09 10:27:00 Re: SSI patch version 14
Previous Message Pavel Stehule 2011-02-09 10:09:57 Re: git commitmessages url format change