如何查找当前事务级别？

如何在 SQLServer 上查找当前数据库的事务级别？

如果您讨论的是当前的事务 筑巢级别，那么您将使用 @@TRANCOUNT。

如果您正在谈论事务 与世隔绝级别，那么使用 DBCC USEROPTIONS并寻找 隔离级别的选项。如果没有设置，就是 读取承诺。

如果您正在谈论事务 与世隔绝级别，那么使用 DBCC USEROPTIONS并寻找 隔离级别的选项。如果没有设置，就是 读取承诺。

运行这个:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID

常数值的 learn.microsoft.com 参考。

DECLARE   @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE   @IsolationLevel varchar(100)


INSERT    @UserOptions
EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')


SELECT    @IsolationLevel = Value
FROM      @UserOptions
WHERE     SetOption = 'isolation level'


-- Do whatever you want with the variable here...
PRINT     @IsolationLevel

SELECT CASE
WHEN transaction_isolation_level = 1
THEN 'READ UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED'
WHEN transaction_isolation_level = 3
THEN 'REPEATABLE READ'
WHEN transaction_isolation_level = 4
THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5
THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM   sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE  session_id = @@SPID
AND  d.database_id = DB_ID();

只要运行 DBCC useroptions，就会得到这样的结果:

Set Option                  Value
--------------------------- --------------
textsize                    2147483647
language                    us_english
dateformat                  mdy
datefirst                   7
lock_timeout                -1
quoted_identifier           SET
arithabort                  SET
ansi_null_dflt_on           SET
ansi_warnings               SET
ansi_padding                SET
ansi_nulls                  SET
concat_null_yields_null     SET
isolation level             read committed

SELECT CASE
WHEN transaction_isolation_level = 0 THEN 'Unspecified'
WHEN transaction_isolation_level = 1 THEN 'Read Uncommitted'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'OFF' THEN 'Read Committed'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 1 THEN 'Snapshot Read Committed'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 0 THEN 'Snapshot'
WHEN transaction_isolation_level = 3 THEN 'Repeatable Read'
WHEN transaction_isolation_level = 4 THEN 'Serializable' END AS TRANSACTION_ISOLATION_LEVEL,
d.is_read_committed_snapshot_on,
d.snapshot_isolation_state_desc
FROM sys.dm_exec_sessions
CROSS JOIN sys.databases AS d
where session_id = @@SPID
AND  d.database_id = DB_ID();