foreign key constraint lock behavour in postgresql

From: wangyuxiang <wyx6fox(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: foreign key constraint lock behavour in postgresql
Date: 2010-02-04 04:05:33
Message-ID: 4B6A478D.5060805@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

foreign key constraint lock behavour :

The referenced FK row would be added some exclusive lock , following is the case:

CREATE TABLE tb_a
(
id character varying(255) NOT NULL,
"name" character varying(255),
b_id character varying(255) NOT NULL,
CONSTRAINT tb_a_pkey PRIMARY KEY (id),
CONSTRAINT fk_a_1 FOREIGN KEY (b_id)
REFERENCES tb_b (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE tb_b
(
id character varying(255) NOT NULL,
"name" character varying(255),
CONSTRAINT tb_b_pkey PRIMARY KEY (id)
)

before these two transaction begin ,the tb_b has one rows: {id:"b1",name:"b1"}

transaction 1:

begin transaction;
insert into tb_a(id,b_id) values('a1','b1');

//block here;

end transaction;
-----------------
transaction 2:

begin transaction;
// if transaction 1 first run , then this statement would be lock untill transaction1 complete.
update tb_b set name='changed' where id='b1';

end transction;
-----------------

transaction 3:

begin transaction;

delete tb_b where id='b1';

end transaction;
-------------

result:
in postgresql8.4 , transaction 2 and transaction 3 would be block until transaction 1 complete.
in oracle10g , transaction 2 would ne be block ,but transaction 3 would be block .
in mysql5 with innoDB, same behavour with postgresql5

my analyze:

For the FK constraints ,this is reasonable , there is this case may happen:

when one transaction do insert into tb_a with the fk reference to one row ('b1') on tb_b,
simultaneously , another transaction delete the 'b1' row, for avoid this concurrency confliction , then need to lock the 'b1' row.

from this point ,I think i can find some magic why mysql take so better performance for bulk update or delete on concurrency transactions .

oracle use better level lock to avoid block when do update

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message david 2010-02-04 05:40:54 Re: foreign key constraint lock behavour in postgresql
Previous Message Robert Haas 2010-02-04 03:05:47 Re: Slow query: table iteration (8.3)