Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group