c# - 避免 SQL 事务中的死锁

Question

我有以下代码(简化)来访问我的数据库。我知道如何使用语句和参数，但我缩短了它以集中解决我的问题。

string ConnectionString = "ConnectionStringHere";

//1. insert Transaction
SqlConnection myConnection = new SqlConnection(ConnectionString);            
SqlTransaction myTrans = myConnection.BeginTransaction();                
string sUpdate = "INSERT INTO User (FirstName, LastName) VALUES ('jon','doe')";
SqlCommand myCommand = new SqlCommand(sUpdate, myConnection, myTrans);
myCommand.ExecuteNonQuery();

//2. select from the same table
SqlConnection myConnection2 = new SqlConnection(ConnectionString);
string sSelect = "SELECT FirstName, LastName FROM User WHERE ID = 123";
DataTable dtResult = new DataTable();
SqlDataAdapter myDataAdapter2 = new SqlDataAdapter(sSelect, myConnection2);
myDataAdapter2.Fill(dtResult); //TimeOut Exception here

//submit changes from my transaction here
myTrans.Commit();

在第二部分中，我得到了一个 TimeOutExcetion，因为我无法访问我的 User 表，直到我提交我的事务 myTrans.Commit(); 之后 -死锁。

我的问题是 - 避免此处出现死锁的最佳做法是什么？我可以通过将 SELECT 作为事务的一部分或通过设置 IsolationLevel 来避免异常

SqlTransaction myTrans = myConnection.BeginTransaction(IsolationLevel.ReadUncommitted);

但我不确定它们的用法。我不必担心无效数据，因为我总是按 ID 选择。

Answer 1

我看不出有任何理由将 SELECT 查询作为事务的一部分来解决死锁或超时问题。在您认为的第一个 sql 连接 myConnection 上设置 ReadUncommitted 隔离级别也不是正确的方法。我看到有两种可能的解决方案:

1. 第一个解决方案:在您启动的事务myTrans 上设置隔离级别IsolationLevel.ReadUncommitted 将无济于事。如果您对脏读感到满意，那么您实际上应该在第二个 SQL 连接 myConnection2 上设置此隔离级别，该连接是为触发 User< 上的 select 查询而建立的表。要通过 myConnection2 为 select 查询设置隔离级别，您需要使用 with (nolock) 表级提示。所以您的查询将开始看起来像:

   string sSelect = "SELECT FirstName, LastName FROM User WITH (NOLOCK) WHERE ID = 123";
   

   您可以获得更多详情here .另外，阅读脏读的后果 here这是使用此特定隔离级别的副作用。

2. 第二种解决方案:SQL Server 的默认隔离级别是Read Committed。因此，当您开始使用名为 myConnection2 的变量通过新的 SQL 连接触发查询时，它正在 ReadCommitted 隔离级别上工作。 ReadCommitted 隔离级别显示的默认行为是 Blocking Read 即，如果表上有未提交的更改(由于事件事务可以提交或回滚)，那么您的选择User 表上的语句将被阻止。它将等待事务完成，以便在回滚时读取新更新的数据或原始数据。如果它不执行这样的阻塞读取，那么它最终将执行脏读，这是众所周知的数据库并发问题。
   如果您不希望您的 SELECT 语句被阻止并希望继续使用行的最后提交值，则 SQL Server 中有一个名为 READ_COMMITTED_SNAPSHOT 的新数据库级别设置.以下是使用 SQL 脚本更改它的方法:

   ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
   

   引用 Pinal Dave 的文章 here :

If you are having problem with blocking between readers (SELECT) and writers (INSERT/UPDATE/DELETE), then you can enable this property without changing anything from the application. Which means application would still run under read committed isolation and will still read only committed data.

注意:这是一个数据库级别设置，将影响使用READCOMMITTED 隔离级别的数据库中的所有事务。

在我看来，您应该采用第一种解决方案。此外，还有一些要点您应该牢记以避免 SQL Server 查询中的死锁。引用来自 here 的 Pinal Dave :

• 尽量减少交易规模和交易时间。
• 每次在应用程序中始终以相同顺序访问服务器对象。
• 避免游标、while 循环或在运行时需要用户输入的过程。
• 减少应用程序的锁定时间。
• 尽可能使用查询提示来防止锁定(NoLock、RowLock)
• 使用 SET DEADLOCK_PRIORITY 选择死锁受害者。