我们有一个应用程序,最初是作为桌面应用程序编写的,很多年前。每当您打开编辑屏幕时,它会启动一个事务,并在单击“确定”提交或单击“取消”回滚。这对于桌面应用程序来说还可以,但现在我们正在尝试转移到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调用会阻塞,直到第一个连接关闭。
我发现,当多个用户同时尝试编辑同一张表(不同子集)时,我们将遇到问题。在我们的旧数据库中,每个用户的事务都会获取记录级锁定,以便在其事务期间修改的每个记录;由于不同的用户正在编辑不同的记录,因此每个人都拥有自己的锁定并且一切正常。但是在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)”