Blocking Issue

Started by Sander, Ingo (NSN - DE/Munich)almost 15 years ago2 messages

ingo.sander@nsn.com

almost 15 years ago

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)

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.sander@nsn.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

Heikki Linnakangas

heikki.linnakangas@enterprisedb.com

almost 15 years ago

In reply to: Sander, Ingo (NSN - DE/Munich) (#1)

Re: Blocking Issue

On 09.02.2011 12:15, Sander, Ingo (NSN - DE/Munich) wrote:

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

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?

The foreign key causes the blocking. PostgreSQL doesn't make a
distinction on which columns are updated, as far as locking is
concerned. If the update was "update act_rnc set rnc_id=2 where
rnc_id=1", the insert would have to block to see if the update commits
or not - if it commits the insert would violate the foreign key and
needs to be aborted, but if it aborts the insert can succeed. With your
original example, the insert could go ahead in either case without
violating the foreign key, since the update doesn't change rnc_id field,
but PostgreSQL doesn't pay attention to that detail.

There's actually a patch in the current commitfest, awaiting review, to
address exactly that scenario. See
https://commitfest.postgresql.org/action/patch_view?id=502 and
http://archives.postgresql.org/message-id/1294953201-sup-2099@alvh.no-ip.org.

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