Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id DCF57632F09 for ; Thu, 4 Feb 2010 00:05:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 70264-02 for ; Thu, 4 Feb 2010 04:05:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f188.google.com (mail-yx0-f188.google.com [209.85.210.188]) by mail.postgresql.org (Postfix) with ESMTP id 1825062DCFD for ; Thu, 4 Feb 2010 00:05:46 -0400 (AST) Received: by yxe26 with SMTP id 26so1963045yxe.28 for ; Wed, 03 Feb 2010 20:05:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:content-type; bh=ww1Ez8DXJucDGk/fUQovdhOOKd3MlgIpzhh0Yc/s3Zs=; b=x+yPQPFLBuKvWgnQipDzE1z3ECCmIL2tmIzXlJRnJvH6obS+hpD+eu/mlEhn6XphCf bDn1qwLHJF7rYC6ea7YaQtowWqwdCdxxXnL5Qnww4MOj1GysuBXvFGPDVPA5eDtN+bzN 6nEuEJK72EeADFV4eNFZh0xXoJs0yPYioPfDA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; b=VYHmiEnjJPPay+UF5ZV+iaEilzmo+72TBa3SH/qxX/5i8t981tSIcmCnQ0qgll8xbi CrapgQNlKAOChsEN87eaVoYKMRB0uFhOA6wKbQavhbmzLNjP/cVTTidwuqcb73YleNvK 67xzX/yanTiZQdw4JTbYXqOj2IvCJG5KoQ3GM= Received: by 10.150.183.3 with SMTP id g3mr1112873ybf.224.1265256345380; Wed, 03 Feb 2010 20:05:45 -0800 (PST) Received: from ?192.168.0.12? ([123.117.163.215]) by mx.google.com with ESMTPS id 4sm2748941yxd.52.2010.02.03.20.05.43 (version=SSLv3 cipher=RC4-MD5); Wed, 03 Feb 2010 20:05:44 -0800 (PST) Message-ID: <4B6A478D.5060805@gmail.com> Date: Thu, 04 Feb 2010 12:05:33 +0800 From: wangyuxiang User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: foreign key constraint lock behavour in postgresql Content-Type: multipart/alternative; boundary="------------010808000307020703010602" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/73 X-Sequence-Number: 37494 This is a multi-part message in MIME format. --------------010808000307020703010602 Content-Type: text/plain; charset=GB2312 Content-Transfer-Encoding: 7bit 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 --------------010808000307020703010602 Content-Type: text/html; charset=GB2312 Content-Transfer-Encoding: 7bit 
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
--------------010808000307020703010602--