トランザクション分離レベル

• Oracle

READ COMMITTED <==デフォルト
SERIALIZABLE

--セッションレベル
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

• MySQL

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ <==デフォルト
SERIALIZABLE

https://qiita.com/rubytomato@github/items/562a1638191aacaeb333

--グローバルレベル
vim /etc/my.cnf

[mysqld]
transaction-isolation = REPEATABLE-READ

--セッションレベル
set session transaction isolation level READ UNCOMMITTED;
set session transaction isolation level READ COMMITTED;
set session transaction isolation level REPEATABLE READ;
set session transaction isolation level SERIALIZABLE;

(5.6)
select @@tx_isolation;

(8.0.18)
select @@transaction_isolation;

set session transaction_isolation = 'READ-COMMITTED';
set session transaction_isolation = 'REPEATABLE-READ';

• PostgreSQL

READ UNCOMMITTED <==指定しても実際にはREAD COMMITTEDになる
READ COMMITTED <==デフォルト
REPEATABLE READ
SERIALIZABLE

--グローバルレベル
vim postgresql.conf

default_transaction_isolation = 'repeatable read'

--セッションレベル
begin;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--設定確認
SHOW TRANSACTION ISOLATION LEVEL;

• SQL Server

https://coogi-memo.hatenablog.com/entry/2018/10/09/171311

READ UNCOMMITTED
READ COMMITTED <==デフォルト
REPEATABLE READ
SERIALIZABLE
SNAPSHOT
READ COMMITTED SNAPSHOT

--設定確認
DBCC USEROPTIONS

--設定変更
-- トランザクション分離レベル（悲観的ロック)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--READ COMMITTED SNAPSHOT分離レベルへの変更
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON with rollback after 1 seconds;

--SNAPSHOT分離レベルへの変更
--データベースレベルとセッションレベルの両方で設定する必要がある
ALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT