SQL 如何在没有事务的情况下初始化的 SqlCommand/SqlConnection 上设置隔离级别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4341590/
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 set isolation level on SqlCommand/SqlConnection initialized with no transaction
提问by kateroh
The following method is supposed to peroform a dirty read on an open connection. There are no transactions. Where do I set IsolationLevel?
以下方法应该在打开的连接上执行脏读。没有交易。我在哪里设置隔离级别？
public string DoDirtyRead(string storedProcName, SqlConnection connection)
{
using (SqlCommand command = new SqlCommand(storedProcName, connection))
{
command.CommandType = CommandType.StoredProcedure;
// HOW TO SET IsolationLevel to READ_UNCOMMITTED here?
command.ExecuteNonQuery();
}
}
采纳答案by Bob Palmer
On the BeginTransaction method: (MSDN link)
关于 BeginTransaction 方法：（MSDN 链接）
And if you just want to use hints in your SP at the table level, use WITH(NOLOCK)- but use at your own risk.
如果您只想在表级别的 SP 中使用提示，请使用WITH(NOLOCK)- 但使用风险自负。
回答by Eddie Deyo
If you don't want to do transactions, you can set it once when you open the connection and it will remain at that setting until you change it. So just do:
如果您不想进行交易，您可以在打开连接时设置一次，它会保持该设置直到您更改它。所以只需这样做：
connection.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();
Probably not the best for your specific case, since you are opening the connection, using it, and throwing it away, but I wanted to put this answer in for anyone with a longer-lived connection.
对于您的特定情况，可能不是最好的，因为您正在打开连接，使用它，然后扔掉它，但我想为任何连接寿命更长的人提供这个答案。
回答by ArBR
In your Stored Procedure, for transact-sqluse:
在您的Stored Procedure 中，对于transact-sql使用：
SET TRANSACTION ISOLATION LEVEL read uncommitted -- 0
SET TRANSACTION ISOLATION LEVEL read committed -- 1
SET TRANSACTION ISOLATION LEVEL repeatable read -- 2
SET TRANSACTION ISOLATION LEVEL read serializable -- 3
回答by piers7
Given you already have an existing connection (and possibly an existing transaction), I'd use a TransactionScope to control the isolation level of the child. This does a dirty read rowcount (I believe):
鉴于您已经有一个现有的连接（可能还有一个现有的事务），我会使用 TransactionScope 来控制子级的隔离级别。这会进行脏读行计数（我相信）：
using (var command = connection.CreateCommand())
using(new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions{IsolationLevel = IsolationLevel.ReadUncommitted}))
{
command.CommandText = string.Format("select count(*) from {0}", tableName);
return (int)command.ExecuteScalar();
}
回答by Gordon Prince
Add another parameter to your stored procedure to indicate the isolation level you want the stored procedure to run with.
将另一个参数添加到您的存储过程以指示您希望存储过程使用的隔离级别。
IF @isolevel = 0 SET TRANSACTION ISOLATION LEVEL read uncommitted; ELSE
IF @isolevel = 0 SET TRANSACTION ISOLATION LEVEL read uncommitted; 别的
Also I believe uncommitted needs two "t's" in it.
我也相信 uncommitted 需要两个“t”。
