tree-tips

drop table if exists child;
create table child (id int, pid int, primary key (id, pid))engine=innodb;
 
drop table if exists parent;
create table parent (id int, count int, primary key (id))engine=innodb;
insert into parent values (1, 0);
 
alter table child add foreign key (id) references parent (id);

トランザクションA
-------------------------------------
begin;
insert into child values (1, 1);


update parent set count = count + 1 where id = 1;


commit;

トランザクションB
-------------------------------------


begin;
insert into child values (1, 2);


update parent set count = count + 1 where id = 1;
commit;

トランザクションA
-------------------------------------
begin;
insert into child values (1, 1);


update parent set count = count + 1 where id = 1;

ここで外部キーの機能が自動的に親テーブルの外部キーidをslock。

Aがwhere句でidにxlock。しかし、Bが既にidにslockしているので、AのxlockはBのslockにブロックされてロック開放待ち。

この状態でセッションBのupdateが実行されると、お互いのロックの開放待ちになり、Bがデッドロック。

トランザクションA
-------------------------------------
begin;
select id from parent where id = 1 for update;


insert into child values (1, 1);


update parent set count = count + 1 where id = 1;


commit;

トランザクションB
-------------------------------------


begin;
select id from parent where id = 1 for update;


insert into child values (1, 2);


update parent set count = count + 1 where id = 1;
commit;

トランザクションA
-------------------------------------
begin;
update parent set count = count + 1 where id = 1;


insert into child values (1, 1);

commit;

トランザクションB
-------------------------------------


begin;
update parent set count = count + 1 where id = 1;

insert into child values (1, 2);
commit;

トランザクションA
-------------------------------------
begin;
select id from parent where id = 1 lock in share mode;
insert into child values (1, 1);


update parent set count = count + 1 where id = 1;


commit;

トランザクションB
-------------------------------------


begin;
select id from parent where id = 1 lock in share mode;
insert into child values (1, 2);


update parent set count = count + 1 where id = 1;
commit;