Re: foreign key constraint lock behavour in postgresql

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