blocks|key|1521964|text|运行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521965|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|code-block|syntax|javascript|1521966|docs.microsoft.com+reference+for+the+constant+values。|offset|length|1521967|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql^0|0|0|0|1G|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Run this:

<pre><code>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
</code></pre>

<a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql" rel="noreferrer">docs.microsoft.com reference for the constant values</a>.

blocks|key|1521971|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1521972|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1306951|text|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();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1306952|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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();
</code></pre>

blocks|key|1522004|text|只需运行DBCC+useroptions，您将得到如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1522005|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|code-block|syntax|javascript|1522006|entityMap^0|4|G|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

just run <code>DBCC useroptions</code> and you'll get something like this:

<pre><code>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
</code></pre>

blocks|key|1306979|text|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();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1306980|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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();
</code></pre>

How do you find current database's transaction level on SQL Server?

How to find current transaction level?

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

问如何找到当前的交易层级？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何找到当前的交易层级？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何找到当前的交易层级？
EN