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
