READ COMMITTED挙動確認

• MySQL

(8.0.22)

※binlog_format = statementの場合、下記エラー発生のため、binlog_format=rowに設定変更

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log
since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

-- テストデータ作成

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 = 'READ-COMMITTED';
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;

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

---- Session2

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

---- Session1

select * from tab1 order by col1;

→追加されている

---- Session2

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

---- Session1

select * from tab1 order by col1;
→更新されている

---- Session2

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

---- Session1

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;

→正しく更新される

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。

• Oracle

(19c)

-- テストデータ作成

drop table tab1;
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 READ COMMITTED;

---- 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;

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

---- Session2

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

---- Session1

select * from tab1 order by col1;

→追加されている

---- Session2

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

---- Session1

select * from tab1 order by col1;
→更新されている

---- Session2

select * from tab1 order by col1;
delete from tab1 where col1 = 3;
commit;
select * from tab1 order by col1;

---- Session1

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;

→正しく更新される

--結論

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 read committed;
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');
select * from tab1 order by col1;

---- Session1

select * from tab1 order by col1;

→追加されている

---- Session2

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

---- Session1

select * from tab1 order by col1;
→更新されている

---- Session2

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

---- Session1

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;

→正しく更新される

--結論

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 read committed
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;

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

---- Session2

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

---- Session1

select * from tab1 order by col1;

→追加されている

---- Session2

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

---- Session1

select * from tab1 order by col1;

→更新されている

---- Session2

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

---- Session1

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;

→正しく更新される

--結論

ANSI/ISO SQL標準どおりの挙動と思われる。