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

Re: foreign key constraint lock behavour in postgresql

From: david(at)lang(dot)hm
To: wangyuxiang <wyx6fox(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: foreign key constraint lock behavour in postgresql
Date: 2010-02-04 05:40:54
Message-ID: alpine.DEB.2.00.1002032134530.7512@asgard.lang.hm (view raw or flat)
Thread:
Lists: pgsql-performance
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 concurrency transactions .
>
> 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

In response to

Responses

pgsql-performance by date

Next:From: Glenn MaynardDate: 2010-02-04 06:30:06
Subject: Re: Slow query: table iteration (8.3)
Previous:From: wangyuxiangDate: 2010-02-04 04:05:33
Subject: foreign key constraint lock behavour in postgresql

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