如何使SQL Server事务使用记录级锁?

我们有一个应用程序,最初是作为一个桌面应用程序编写的,在这些年前。每当您打开编辑屏幕时,它就会启动一个事务,如果单击“确定”,则提交;如果单击“取消”,则回滚。对于桌面应用程序来说,这是可行的,但是现在我们正试图转移到ado.net和sql server,并且长时间运行的事务是有问题的。

我发现当多个用户同时试图编辑同一个表时,我们会遇到一个问题。在我们的旧数据库中,每个用户的事务都会获取他们在事务期间修改的每个记录的记录级锁;因为不同的用户正在编辑不同的记录,所以每个人都会获得自己的锁,并且一切正常。但在SQL Server中,只要一个用户在事务中编辑记录,SQL Server就会在 整张桌子。 当第二个用户试图编辑同一个表中的不同记录时,第二个用户的应用程序只会锁定,因为sqlconnection会阻塞,直到第一个用户提交或回滚为止。

我知道长期的交易是不好的,我知道 最好的 解决方案是更改这些屏幕,使它们不再让事务长时间处于打开状态。但既然这意味着一些有侵略性和风险性的改变,我也想研究是否有办法让代码正常运行,这样我就知道我的选择是什么了。

如何使SQL Server中的两个不同用户的事务锁定单个记录而不是整个表?

这里有一个快速和肮脏的控制台应用程序,说明了这个问题。我创建了一个名为“test1”的数据库,其中一个名为“values”的表只有id(int)和value(nvarchar)列。如果运行应用程序,它会要求修改ID,启动一个事务,修改该记录,然后让事务保持打开状态,直到按Enter键。我想能够

1. 启动程序并告诉它更新id 1;
2. 让它得到它的事务并修改记录;
3. 启动程序的第二个副本并告诉它更新id 2;
4. 让它能够在第一个应用程序的事务仍处于打开状态时更新(并提交)。

目前,它在第4步冻结,直到我返回到应用程序的第一个副本并关闭它,或者按enter键使其提交。对command.executenonquery的调用将阻塞,直到第一个连接关闭。

public static void Main()
{
    Console.Write("ID to update: ");
    var id = int.Parse(Console.ReadLine());
    Console.WriteLine("Starting transaction");
    using (var scope = new TransactionScope())
    using (var connection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=test1;Integrated Security=True"))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "UPDATE [Values] SET Value = 'Value' WHERE ID = " + id;
        Console.WriteLine("Updating record");
        command.ExecuteNonQuery();
        Console.Write("Press ENTER to end transaction: ");
        Console.ReadLine();
        scope.Complete();
    }
}

以下是我已经尝试过的一些行为没有改变的事情:

• 将事务隔离级别更改为“读取未提交”
• 在update语句中指定“with(rowlock)”。