在Dapper SqlBuilder或sp_exectuesql中运行SET语句

Question

我试图使用SqlBuilder在Dapper中设置事务隔离级别，但似乎不起作用。

var builder = new SqlBuilder();
var sqlStatement = builder.AddTemplate(@"
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT * FROM Users
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
conn.Query<User>(sqlStatement.RawSql);

但这样做是可行的：

conn.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
var result = conn.Query<User>(sqlStatement);
conn.Execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

我认为这可能是因为使用SqlBuilder，Dapper构建了一个动态SQL并使用sp_executesql存储过程执行。

为了证明我的假设，我尝试了以下SQL语句：

exec sp_executesql N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'

在该语句之后，我查询sys.dm_exec_sessions来检查我的连接，它仍然显示ReadCommitted，这是我的数据库中的默认隔离级别。

是否可以在单独的SET中设置事务级别(或任何其他.Execute语句)？或者是一种在sp_executesql中使用sp_executesql语句的特殊方法

Answer 1

经过另一次测试后，我在这里给出的例子似乎是有效的。我认为它必须执行我们的生产SQL查询。

我们找到了其他几种方法来处理这个问题。

在事务中指定隔离级别：

using (var trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    conn.Query<User>(sqlStatement.RawSql, transaction: trans).Dump();
}

在构建器模板中添加隔离级别：

var readUncommitted = builder.AddTemplate("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
var readCommitted = builder.AddTemplate("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

Answer 2

试试这个：

SELECT * FROM Users with (nolock)

这与在执行select语句之前使用SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED相同。

更多信息：WITH(NOLOCK)