锁定SQL Server表的C＃方法

雪总是白的

我有一个C＃程序，需要对SQL Server表执行一组批量更新（20k
+）。由于其他用户可以一次通过Intranet网站一次更新这些记录，因此我们需要构建具有锁定表功能的C＃程序。一旦表被锁定以防止其他用户进行任何更改/搜索，我们将需要执行所请求的更新/插入。
由于我们正在处理大量记录，因此我们无法使用TransactionScope（起初似乎是最简单的方法），因为我们的交易最终由MSDTC服务处理。我们需要使用另一种方法。
根据我在网上阅读的内容，使用SqlTransaction对象似乎是最好的方法，但是我无法锁定表。当程序运行并且我逐步浏览以下代码时，我仍然能够通过Intranet网站执行更新和搜索。
我的问题是双重的。我使用SqlTransaction得当吗？如果是这样（或什至不是），是否有更好的方法来获取表锁定，该表锁定允许正在运行的当前程序搜索并执行更新？
我希望表在程序执行以下代码时被锁定。
C＃
SqlConnection dbConnection = new SqlConnection(dbConn);
dbConnection.Open();
using (SqlTransaction transaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
{
//Instantiate validation object with zip and channel values
_allRecords = GetRecords();
validation = new Validation();
validation.SetLists(_allRecords);
while (_reader.Read())
{
      try
      {
         record = new ZipCodeTerritory();
         _errorMsg = string.Empty;
         //Convert row to ZipCodeTerritory type
         record.ChannelCode = _reader[0].ToString();
         record.DrmTerrDesc = _reader[1].ToString();
         record.IndDistrnId = _reader[2].ToString();
         record.StateCode = _reader[3].ToString().Trim();
         record.ZipCode = _reader[4].ToString().Trim();
         record.LastUpdateId = _reader[7].ToString();
         record.ErrorCodes = _reader[8].ToString();
         record.Status = _reader[9].ToString();
         record.LastUpdateDate = DateTime.Now;
         //Handle DateTime types separetly
         DateTime value = new DateTime();
         if (DateTime.TryParse(_reader[5].ToString(), out value))
         {
            record.EndDate = Convert.ToDateTime(_reader[5].ToString());
         }
         else
         {
            _errorMsg += "Invalid End Date; ";
         }
         if (DateTime.TryParse(_reader[6].ToString(), out value))
         {
            record.EffectiveDate = Convert.ToDateTime(_reader[6].ToString());
         }
         else
         {
            _errorMsg += "Invalid Effective Date; ";
         }
         //Do not process if we're missing LastUpdateId
         if (string.IsNullOrEmpty(record.LastUpdateId))
         {
            _errorMsg += "Missing last update Id; ";
         }
         //Make sure primary key is valid
         if (_reader[10] != DBNull.Value)
         {
            int id = 0;
            if (int.TryParse(_reader[10].ToString(), out id))
            {
                  record.Id = id;
            }
            else
            {
                  _errorMsg += "Invalid Id; ";
            }
         }
         //Validate business rules if data is properly formatted
         if (string.IsNullOrWhiteSpace(_errorMsg))
         {
            _errorMsg = validation.ValidateZipCode(record);
         }
         //Skip record if any errors found
         if (!string.IsNullOrWhiteSpace(_errorMsg))
         {
            _issues++;
            //Convert to ZipCodeError type in case we have data/formatting errors
            _errors.Add(new ZipCodeError(_reader), _errorMsg);
            continue;
         }
         else if (flag)
         {
            //Separate updates to appropriate list
            SendToUpdates(record);
         }
      }
      catch (Exception ex)
      {
         _errors.Add(new ZipCodeError(_reader), "Job crashed reading this record, please review all columns.");
         _issues++;
      }
}//End while

//Updates occur in one of three methods below. If I step through the code,
//and stop the program here, before I enter any of the methods, and then
//make updates to the same records via our intranet site the changes
//made on the site go through. No table locking has occured at this point.
if (flag)
{
      if (_insertList.Count > 0)
      {
         Updates.Insert(_insertList, _errors);
      }
      if (_updateList.Count > 0)
      {
         _updates = Updates.Update(_updateList, _errors);
         _issues += _updateList.Count - _updates;
      }
      if (_autotermList.Count > 0)
      {
         //_autotermed = Updates.Update(_autotermList, _errors);
         _autotermed = Updates.UpdateWithReporting(_autotermList, _errors);
         _issues += _autotermList.Count - _autotermed;
      }
}
transaction.Commit();
}

解决方案:


            SQL并未真正提供独占锁定表的方法：它旨在在保持ACID的同时尝试最大程度地提高并发使用率。
您可以尝试在查询中使用这些表提示：
标签块

指定将获取的锁应用于表级别。获取的锁的类型取决于正在执行的语句。例如，一个SELECT语句可以获取一个共享锁。通过指定TABLOCK，共享锁将应用于整个表，而不是行或页面级别。如果还指定了HOLDLOCK，则表锁将保持到事务结束。

TABLOCKX

指定在表上采用排他锁。

密码锁

指定将获取并保持更新锁，直到事务完成。UPDLOCK仅在行级别或页面级别为读取操作获取更新锁。如果UPDLOCK与TABLOCK结合使用，或者由于其他原因采用了表级锁定，则将采用排他（X）锁定。

XLOCK

指定在事务完成之前要获取并保持排他锁。如果使用ROWLOCK，PAGLOCK或TABLOCK指定，则排他锁适用于适当的粒度级别。

保持锁定/可串行化

通过保持共享锁直到事务完成，使共享锁更具限制性，而不是在不再需要所需的表或数据页时释放共享锁，而不管事务是否已完成。使用与在SERIALIZABLE隔离级别运行的事务相同的语义执行扫描。有关隔离级别的更多信息，请参见SET
TRANSACTION ISOLATION LEVEL（Transact-SQL）。

或者，您可以尝试设置可事务隔离的SET TRANSACTION ISOLATION LEVEL：

语句无法读取已被其他事务修改但尚未提交的数据。

在当前事务完成之前，没有其他事务可以修改当前事务已读取的数据。

在当前事务完成之前，其他事务不能插入其键值将落入当前事务中任何语句读取的键范围内的新行。

范围锁位于与事务中执行的每个语句的搜索条件匹配的键值范围内。这将阻止其他事务更新或插入任何符合当前事务执行的任何语句的行。这意味着，如果第二次执行事务中的任何语句，它们将读取相同的行集。范围锁将保持到事务完成为止。这是隔离级别中最严格的限制，因为它锁定整个范围的键并保持锁定，直到事务完成为止。由于并发性较低，因此仅在必要时使用此选项。此选项与在事务中所有SELECT语句中的所有表上设置HOLDLOCK的作用相同。

但是几乎可以肯定，锁升级将导致阻塞，并且您的用户将被淹死（根据我的经验）。
所以…
请等待，直到有一个计划维护窗口。将数据库设置为单用户模式，进行更改并使其重新联机。

锁定SQL Server表的C＃方法

雪总是白的 LV1