SQL Server:如何为整个存储过程设置默认隔离级别?

问题描述

如果在 BEGIN 之后我有 SET TRANSACTION ISOLATION LEVEL ... 语句，给定的事务级别是否对存储过程的整个范围有效，无论我是否是否使用 BEGIN TRANSACTION ?也就是说，如果我有简单的 SELECT 语句，根据定义它们是原子的/事务性的，它们的默认事务级别是否会设置为给定的级别?

If right after BEGIN I have SET TRANSACTION ISOLATION LEVEL ... statement, will the given transaction level in force for the entire scope of the stored procedure regardless if I use BEGIN TRANSACTION or not? Namely if I have simple SELECT statements, which are atomic/transacted by definition, will the default transaction level for them set to the given one?

BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 -- will a transaction level for a atomic transaction created by SQL Server for this statement be READ COMMITTED 
SELECT * FROM T
END

推荐答案

首先，SQL Server 中的默认隔离级别是 Read Committed，因此除非您更改了默认隔离级别，否则该语句不会真正执行任何操作.

First, the default isolation level in SQL Server is Read Committed, so that statement doesn't really do anything unless you've changed the default isolation level.

但是，一般来说，是的，SET Transaction Isolation Level 会改变整个过程的隔离级别(实际上是连接的持续时间)

But, in general, yes, SET Transaction Isolation Level will change the isolation level for the whole procedure (the duration of the connection, in fact)

请记住，所有 SQL 语句都是隐式事务，这意味着，例如，如果更新失败 99%，它将自动回滚；不需要 BEGIN TRAN/COMMIT.

Keep in mind that all SQL statements are implicit transactions meaning that if, for example, an update fails 99% through, it will rollback automatically; no BEGIN TRAN/COMMIT is necessary.

要回答您的问题，是的，您的 SELECT 语句将继承您设置的隔离级别(如果未设置，则为默认值)，除非您使用诸如 WITH NOLOCK 之类的查询提示覆盖该行为这将使单个查询的行为就像您执行了 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

To answer your question, yes, your SELECT statements will inherit the isolation level you set (or the default if you do not set one) unless you override the behavior with a query hint like WITH NOLOCK which will make the individual query behave as though you did SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

这篇关于SQL Server:如何为整个存储过程设置默认隔离级别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！