SQL 如何找到当前的交易级别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1038113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find current transaction level?
提问by dance2die
How do you find current database's transaction level on SQL Server?
如何在 SQL Server 上找到当前数据库的事务级别？
回答by SQLMenace
Run this:
运行这个：
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
回答by thiagoh
just run DBCC useroptionsand you'll get something like this:
只需运行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
回答by SQLpro
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();
回答by Scott Ivey
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
回答by Eric Petroelje
If you are talking about the current transaction nestinglevel, then you would use @@TRANCOUNT.
如果您正在谈论当前的事务嵌套级别，那么您将使用@@TRANCOUNT.
If you are talking about transaction isolationlevel, use DBCC USEROPTIONSand look for an option of isolation level. If it isn't set, it's read committed.
如果您正在谈论事务隔离级别，请使用DBCC USEROPTIONS并寻找一个隔离级别选项。如果未设置，则读取已提交。
