From: wangyuxiang <wyx6fox@gmail.com>
To: pgsql-performance@postgresql.org
Subject: foreign key constraint lock behavour in postgresql
Date: Thu, 04 Feb 2010 12:05:33 +0800
Message-ID: <4B6A478D.5060805@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wyx6fox@gmail.com
Subject: Re: foreign key constraint lock behavour in postgresql
In-Reply-To: <4B6A478D.5060805@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox