SERIALIZABLE挙動確認

• MySQL

(8.0.22)
-- テストデータ作成

drop table tab1;
create table tab1(col1 int primary key,col2 varchar(10));
insert into tab1 values(1,'A');
insert into tab1 values(2,'A');
insert into tab1 values(3,'A');
insert into tab1 values(11,'A');
insert into tab1 values(12,'A');
insert into tab1 values(13,'A');
select * from tab1 order by col1;

-- 挙動確認
---- Session1
select @@session.transaction_isolation;
set session transaction_isolation = 'SERIALIZABLE';
select @@session.transaction_isolation;

start transaction;
--start transaction WITH CONSISTENT SNAPSHOT;
※WITH CONSISTENT SNAPSHOTオプションはREPEATABLE READ isolation levelでだけ意味を持つ

---- Session2

select * from tab1 order by col1;
update tab1 set col2 = 'AA';
select * from tab1 order by col1;

---- Session1
select * from tab1 order by col1;
--select * from tab1 where col1 = 3;

→
start transaction～最初のselect文までの更新は反映される

---- Session2

select * from tab1 order by col1;
insert into tab1 values(4,'A');
--update tab1 set col2 = 'B' where col1 = 2;
--delete from tab1 where col1 = 1;

→
Session1で全件selectした場合、追加、更新、削除すべて待ちになる
Session1で1件selectした場合、異なる行への追加、更新、削除は可能

---- Session1

select * from tab1 order by col1;
insert into tab1 values(14,'A');
select * from tab1 order by col1;

select * from tab1 order by col1;
update tab1 set col2 = 'B' where col1 = 12;
select * from tab1 order by col1;

select * from tab1 order by col1;
delete from tab1 where col1 = 13;
select * from tab1 order by col1;

→正しく更新される

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。
処理タイミングにより待ちが発生する。

• Oracle

(19c)

-- テストデータ作成

drop table tab1 purge;
create table tab1(col1 int primary key,col2 varchar2(10));
insert into tab1 values(1,'A');
insert into tab1 values(2,'A');
insert into tab1 values(3,'A');
insert into tab1 values(11,'A');
insert into tab1 values(12,'A');
insert into tab1 values(13,'A');
select * from tab1 order by col1;
commit;

-- 挙動確認
---- Session1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---- Session2

select * from tab1 order by col1;
update tab1 set col2 = 'AA';
commit;
select * from tab1 order by col1;

---- Session1
select * from tab1 order by col1;
--select * from tab1 where col1 = 3;

→
SET TRANSACTION～最初のselect文までの更新は反映されない

---- Session2

select * from tab1 order by col1;
insert into tab1 values(4,'A');
--update tab1 set col2 = 'B' where col1 = 2;
--delete from tab1 where col1 = 1;
commit;
select * from tab1 order by col1;

→
追加、更新、削除すべて可能

---- Session1

select * from tab1 order by col1;
insert into tab1 values(14,'A');
select * from tab1 order by col1;

select * from tab1 order by col1;
update tab1 set col2 = 'B' where col1 = 12;
select * from tab1 order by col1;

select * from tab1 order by col1;
delete from tab1 where col1 = 13;
select * from tab1 order by col1;

→いずれも「ORA-08177: このトランザクションのアクセスをシリアル化できません」発生

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。
処理タイミングによりエラーが発生する。

• PostgreSQL

(13)

※SET TRANSACTIONはトランザクション内で最初に指定する必要がある

-- テストデータ作成

drop table tab1;
create table tab1(col1 int primary key,col2 varchar(10));
insert into tab1 values(1,'A');
insert into tab1 values(2,'A');
insert into tab1 values(3,'A');
insert into tab1 values(11,'A');
insert into tab1 values(12,'A');
insert into tab1 values(13,'A');
select * from tab1 order by col1;

-- 挙動確認
---- Session1
start transaction;
show transaction isolation level;
set transaction isolation level serializable;
show transaction isolation level;

---- Session2

select * from tab1 order by col1;
update tab1 set col2 = 'AA';
select * from tab1 order by col1;

---- Session1
select * from tab1 order by col1;

→start transaction～最初のselect文までの更新は反映される

---- Session2

select * from tab1 order by col1;
insert into tab1 values(4,'A');
--update tab1 set col2 = 'B' where col1 = 2;
--delete from tab1 where col1 = 3;
select * from tab1 order by col1;

→
追加、更新、削除すべて可能

---- Session1

select * from tab1 order by col1;
insert into tab1 values(14,'A');
select * from tab1 order by col1;

select * from tab1 order by col1;
update tab1 set col2 = 'B' where col1 = 12;
select * from tab1 order by col1;

select * from tab1 order by col1;
delete from tab1 where col1 = 13;
select * from tab1 order by col1;

→正しく更新される

select * from tab1 order by col1;
update tab1 set col2 = 'X';
select * from tab1 order by col1;

→
エラーになる
ERROR: could not serialize access due to concurrent update

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。
更新の内容によってはエラーになる場合がある。

• SQL Server

(2019)

-- テストデータ作成

drop table tab1;
create table tab1(col1 int primary key,col2 varchar(10));
insert into tab1 values(1,'A');
insert into tab1 values(2,'A');
insert into tab1 values(3,'A');
insert into tab1 values(11,'A');
insert into tab1 values(12,'A');
insert into tab1 values(13,'A');
select * from tab1 order by col1;

-- 挙動確認
---- Session1

dbcc useroptions
set transaction isolation level serializable
dbcc useroptions

begin transaction;

---- Session2

select * from tab1 order by col1;
update tab1 set col2 = 'AA';
select * from tab1 order by col1;

---- Session1
select * from tab1 order by col1;
--select * from tab1 where col1 = 3;

→start transaction～最初のselect文までの更新は反映される

---- Session2

select * from tab1 order by col1;
insert into tab1 values(4,'A');
--update tab1 set col2 = 'B' where col1 = 2;
--delete from tab1 where col1 = 1;

→
Session1で全件selectした場合、追加、更新、削除すべて待ちになる
Session1で1件selectした場合、異なる行への追加、更新、削除は可能

---- Session1

select * from tab1 order by col1;
insert into tab1 values(14,'A');
select * from tab1 order by col1;

select * from tab1 order by col1;
update tab1 set col2 = 'B' where col1 = 12;
select * from tab1 order by col1;

select * from tab1 order by col1;
delete from tab1 where col1 = 13;
select * from tab1 order by col1;

→正しく更新される

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。
処理タイミングにより待ちが発生する。