Re: foreign key constraint lock behavour in postgresql

From: david@lang.hm
Subject: Re: foreign key constraint lock behavour in postgresql
Date: ,
Msg-id: alpine.DEB.2.00.1002032134530.7512@asgard.lang.hm
(view: Whole thread, Raw)
In response to: foreign key constraint lock behavour in postgresql  (wangyuxiang)
Responses: Re: foreign key constraint lock behavour in postgresql  (Robert Haas)
List: pgsql-performance

Tree view

foreign key constraint lock behavour in postgresql  (wangyuxiang, )
 Re: foreign key constraint lock behavour in postgresql  (, )
  Re: foreign key constraint lock behavour in postgresql  (Robert Haas, )
   Re: foreign key constraint lock behavour in postgresql  ("Albe Laurenz", )
    Re: foreign key constraint lock behavour in postgresql  (Robert Haas, )
     Re: foreign key constraint lock behavour in postgresql  ("Albe Laurenz", )
      Re: foreign key constraint lock behavour in postgresql  ("Albe Laurenz", )

On Thu, 4 Feb 2010, wangyuxiang wrote:

> 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
concurrencytransactions . 
>
> oracle use better level lock to avoid block when do update

I could be wrong in this (if so I know I'll be corrected :-)

but Postgres doesn't need to lock anything for what you are describing.

instead there will be multiple versions of the 'b1' row, one version will
be deleted, one version that will be kept around until the first
transaction ends, after which a vaccum pass will remove the data.

David Lang


pgsql-performance by date:

From: Matthew Wakeling
Date:
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
From: Tory M Blue
Date:
Subject: bigint integers up to 19 digits.