如何找到当前的交易水平?

发布于 2024-07-25 18:50:57 字数 36 浏览 9 评论 0原文

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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

微凉 2024-08-01 18:50:57

运行此命令:

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 参考常量值

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

learn.microsoft.com reference for the constant values.

终弃我 2024-08-01 18:50:57

只需运行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

just run DBCC useroptions and you'll get something like this:

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
月隐月明月朦胧 2024-08-01 18:50:57
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();
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();
护你周全 2024-08-01 18:50:57
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
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
翻了热茶 2024-08-01 18:50:57

如果您正在谈论当前事务嵌套级别,那么您将使用@@TRANCOUNT

如果您正在谈论事务隔离级别,请使用DBCC USEROPTIONS并查找隔离级别选项。 如果未设置,则已提交读取

If you are talking about the current transaction nesting level, then you would use @@TRANCOUNT.

If you are talking about transaction isolation level, use DBCC USEROPTIONS and look for an option of isolation level. If it isn't set, it's read committed.

携余温的黄昏 2024-08-01 18:50:57
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();
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();
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文