支持的隔离级别及默认值

 OracleMySQLSQL Server

支持

Read Committed提交读

Serializable可串行化

Read Uncommitted未提交读

Read Committed提交读

Repeatable Read可重复读

Serializable可串行化

Read Uncommitted未提交读

Read Committed提交读

Repeatable Read可重复读

Serializable可串行化

Snapshot快照

Read Committed Snapshot已经提交读隔离

默认

Read Commit提交读

Repeatable Read可重复读

Read Commit提交读

 

设置语句

Oracle

-- 设置提交读 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置可串行化，SYS用户不支持该操作 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL

MySQL的设置可以分为系统级别（GLOBAL）和会话级别（SESSION）

-- 未提交读 SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 提交读 SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 可重复读 SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可串行化 SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SQL Server

-- 未提交读 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 提交读 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 可重复读 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可串行化 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 快照 ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON; -- 已经提交读隔离 ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON;

查询语句

Oracle

SELECT S.SID,S.SERIAL#,       CASE BITAND(T.FLAG,POWER(2,28))       WHEN 0 THEN 'READ COMMITTED' ELSE 'SERIALIZABLE' END AS ISOLATION_LEVEL FROM V$TRANSACTION T JOIN V$SESSION S ON T.ADDR = S.TADDR AND S.SID = SYS_CONTEXT('USERENV','SID')

MySQL

-- 查询当前会话的事务隔离级别 SELECT @@TX_ISOLATION; -- 查询数据库系统的事务隔离级别 SELECT @@GLOBAL.TX_ISOLATION; -- 两者同时查询 SELECT @@TX_ISOLATION AS current_session_isolation_level,     @@GLOBAL.TX_ISOLATION as global_isolation_level

SQL Server

DBCC USEROPTIONS