如何在没有事务的情况下初始化的 SqlCommand/SqlConnection 上设置隔离级别

发布于 2024-10-05 08:32:41 字数 401 浏览 8 评论 0原文

以下方法应该在打开的连接上执行脏读。没有交易。在哪里设置 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();
    }
}

原文

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();
    }
}

分享到QQ

分享到微博

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

发布评论

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

可可 2024-10-12 08:32:41

如果您不想进行交易，您可以在打开连接时设置一次，它将保持该设置，直到您更改为止。所以就这样做：

connection.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();

可能不是最适合您的具体情况，因为您打开连接，使用它，然后扔掉它，但我想为任何拥有较长连接的人提供这个答案。

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.

回复收藏 0 原文

悲歌长辞 2024-10-12 08:32:41

在 BeginTransaction 方法上：（MSDN 链接）

如果您只是想在表级别使用 SP 中的提示，请使用 WITH(NOLOCK) - 但使用风险需您自担。

回复收藏 0 原文

找个人就嫁了吧 2024-10-12 08:32:41

假设您已经有一个现有连接（可能还有一个现有事务），我将使用 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();
}

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):

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();
}

回复收藏 0 原文

时间海 2024-10-12 08:32:41

在您的存储过程中，对于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

In your Stored Procedure, for transact-sql use:

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

回复收藏 0 原文