エラー発生時のロールバック範囲

• MySQL

(8.0.22)
https://dev.mysql.com/doc/refman/8.0/ja/innodb-error-handling.html

エラー内容によって、文レベルのロールバックとなったり、トランザクションレベルのロールバックとなったりする

デッドロックエラーはトランザクションレベルのロールバック
その他は文レベルのロールバック

drop table tab2;
drop table tab1;

create table tab1(col1 int,col2 varchar(1));
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

insert into tab1 values(10,'x');

select * from tab1;

-- データサイズ違反

start transaction;

select * from tab1;

insert into tab1 values(100,'x');
insert into tab1 values(110,'xx');

commit;

select * from tab1;

-- 外部キー制約違反

start transaction;

select * from tab2;

insert into tab2 values(1,10);
insert into tab2 values(2,20);

commit;

select * from tab2;

• Oracle

(19c)

常に文レベルのロールバック

drop table tab2 purge;
drop table tab1 purge;

create table tab1(col1 int,col2 varchar2(1));
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

insert into tab1 values(10,'x');

commit;

select * from tab1;

-- データサイズ違反

select * from tab1;

insert into tab1 values(100,'x');
insert into tab1 values(110,'xx');

commit;

select * from tab1;

-- 外部キー制約違反

select * from tab2;

insert into tab2 values(1,10);
insert into tab2 values(2,20);

commit;

select * from tab2;

• PostgreSQL

(13)

常にトランザクションレベルのロールバック

drop table tab2;
drop table tab1;

create table tab1(col1 int,col2 varchar(1));
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

insert into tab1 values(10,'x');

select * from tab1;

-- データサイズ違反

start transaction;

select * from tab1;

insert into tab1 values(100,'x');
insert into tab1 values(110,'xx');

commit;

select * from tab1;

-- 外部キー制約違反

start transaction;

select * from tab2;

insert into tab2 values(1,10);
insert into tab2 values(2,20);

commit;

select * from tab2;

• SQL Server

(2019)

エラー内容によって、文レベルのロールバックとなったり、トランザクションレベルのロールバックとなったりする

XACT_STATE()でトランザクションの状態が分かる
set xact_abort onを指定することで、文レベルのロールバックによる中途半端なコミットを防止できる

drop table tab2;
drop table tab1;

create table tab1(col1 int not null,col2 varchar(1));
create table tab2(col1 int not null,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

insert into tab1 values(10,'x');

select * from tab1;

-- データサイズ違反

set xact_abort on
-- set xact_abort off

begin transaction;

select * from tab1;

insert into tab1 values(100,'x');
insert into tab1 values(110,'xx');

select XACT_STATE();
commit;

select * from tab1;

→
set xact_abort onの場合、トランザクションレベルのロールバック
set xact_abort offの場合、文レベルのロールバック

-- 外部キー制約違反

set xact_abort on
-- set xact_abort off

begin transaction;

select * from tab2;

insert into tab2 values(1,10);
insert into tab2 values(2,20);

select XACT_STATE();
commit;

select * from tab2;

→
set xact_abort onの場合、トランザクションレベルのロールバック
set xact_abort offの場合、文レベルのロールバック