C＃的方法来锁定SQL Server表方法来、SQL、Server
我有需要执行一组大规模更新（20K +）到一个SQL Server表C＃程序。因为其他用户可以通过内部网的网站在同一时间更新这些记录之一，我们需要用锁定表下的能力构建C＃程序。一旦表进行任何改动锁定到prevent其他用户/搜索，我们一定要去preform所要求的更新/插入。
由于我们正在处理那么多的记录，我们无法使用的TransactionScope （一开始似乎是最简单的方法），由于通过 MSDTC服务。我们需要使用另一种方法。
根据使用的SqlTransaction 对象似乎是最好的方法，我看过在互联网上，但我不能让表锁定。当程序运行时，我通过下面的code步骤，我仍然能够进行更新，并通过内部网站搜索。
我的问题是双重的。使用的SqlTransaction 正确我是谁？如果是的话（或者即使不）是有获得表锁，使运行搜索和preform更新目前的方案更好的方法？
我想为表，而下面的程序执行$ C $ C被锁定。
C＃
的SqlConnection的DbConnection =新的SqlConnection（dbConn）;
dbConnection.Open（）;
使用（的SqlTransaction事务= dbConnection.BeginTransaction（IsolationLevel.Serializable））
{
//实例化验证对象，拉链和渠道价值
_allRecords = GetRecords（）;
验证=新的验证（）;
validation.SetLists（_allRecords）;
而（_reader.Read（））
{
尝试
{
记录=新的ZIP codeTerritory（）;
_errorMsg =的String.Empty;
//转换行邮编codeTerritory类型
record.Channel code = _reader [0]的ToString（）;
record.DrmTerrDesc = _reader [1]的ToString（）;
record.IndDistrnId = _reader [2]的ToString（）;
record.State code = _reader [3]的ToString（）修剪（）。
record.Zip code = _reader [4]的ToString（）修剪（）。
record.LastUpdateId = _reader [7]的ToString（）;
record.Error codeS = _reader [8]的ToString（）;
record.Status = _reader [9]的ToString（）;
record.LastUpdateDate = DateTime.Now;
//处理日期时间类型separetly
DateTime值=新的日期时间（）;
如果（DateTime.TryParse（_reader [5]的ToString（），超时值））
{
record.EndDate = Convert.ToDateTime（_reader [5]的ToString（））;
}
其他
{
_errorMsg + =无效的结束日期;;
}
如果（DateTime.TryParse（_reader [6]的ToString（），超时值））
{
record.EffectiveDate = Convert.ToDateTime（_reader [6]的ToString（））;
}
其他
{
_errorMsg + =无效的生效日期;;
}
//如果我们错过LastUpdateId不处理
如果（string.IsNullOrEmpty（record.LastUpdateId））
{
_errorMsg + =缺少最后更新标识;;
}
//确保主键是有效的
如果（_reader [10]！=的DBNull.Value）
{
INT标识= 0;
如果（int.TryParse（_reader [10]的ToString（），掉id））
{
record.Id = ID;
}
其他
{
_errorMsg + =无效的ID;;
}
}
如果数据是正确的格式//验证业务规则
如果（string.IsNullOrWhiteSpace（_errorMsg））
{
_errorMsg = validation.ValidateZip code（记录）;
}
//跳过记录，如果发现任何错误
如果（！string.IsNullOrWhiteSpace（_errorMsg））
{
_issues ++;
//转换为邮编万一codeError类型，我们有数据/格式错误
_errors.Add（新邮codeError（_reader），_errorMsg）;
继续;
}
否则，如果（旗）
{
//单独的更新发布到相应的列表
SendToUpdates（记录）;
}
}
赶上（例外前）
{
_errors.Add（新邮codeError（_reader），工作应声阅读此记录，请查看所有列。）;
_issues ++;
}
} //结束时
//更新发生在以下三种方法之一。如果我通过code步骤，
//和停止程序这里，我以前输入的任何方法，然后
//通过我们的内部网的变化进行更新，以相同的记录
//发在网站上通过。没有表锁定已经发生，在这一点上。
如果（旗）
{
如果（_insertList.Count大于0）
{
Updates.Insert（_insertList，_errors）;
}
如果（_updateList.Count大于0）
{
_updates = Updates.Update（_updateList，_errors）;
_issues + = _updateList.Count - _updates;
}
如果（_autotermList.Count大于0）
{
// _ autotermed = Updates.Update（_autotermList，_errors）;
_autotermed = Updates.UpdateWithReporting（_autotermList，_errors）;
_issues + = _autotermList.Count - _autotermed;
}
}
器transaction.commit（）;
}
解决方案
SQL并没有真正提供了一种独占锁定一个表：它的目的是尽量让多人同时使用，同时保持ACID
您可以的尝试的使用上查询这些表提示：
TABLOCK
指定获取锁用于表级。锁的类型 取得依赖于正在执行的语句。例如，一个SELECT语句 可以获取共享锁。通过指定TABLOCK，共享锁被施加到 整个表而不是在行或页的水平。如果还指定了HOLDLOCK，该 表锁一直保持到事务结束。
TABLOCKX
指定的排它锁被采取在桌子上。
UPDLOCK
指定更新锁将被采取并保持到事务完成。 UPDLOCK需要更新锁对于读操作仅在行级或页面级。如果 UPDLOCK是结合TABLOCK，或表级的锁是采取一些其他 因此，排它（X）锁将采取替代。
XLOCK
指定排他锁被采取，并一直保持到事务 完成。如果有ROWLOCK，PAGLOCK或TABLOCK指定，则排它锁适用 以粒度合适的水平。
HOLDLOCK / SERIALIZABLE
让按住，直到交易完成共享锁更为严格， 而不是尽快释放共享锁为所需的表或数据页是没有 不再需要，该交易是否已结束与否。扫描是 与运行在SERIALIZABLE相同的语义交易执行 隔离级别。有关隔离级别的详细信息，请参阅设置事务 隔离级别（Transact-SQL中）。
另外，你可以尝试事务隔离级别设置SERIALIZABLE：
＆GT; - 语句不能读取已修改但尚未提交的其他资料
＆GT;交易。
＆GT;
＆GT; - 没有其他事务可以修改已经被读出的当前的交易数据
＆GT;直到当前事务完成。
＆GT;
＆GT; - 其他事务不能插入带将落在键值新行
＆GT;通过任何语句读取当前事务，直到当前键范围
＆GT;交易完成。
＆GT;
＆GT;范围锁被放置在匹配的查找条件的关键值的范围
＆GT;在一个事务中执行每个语句。这将阻止从更新的其他交易
＆GT;或插入任何有资格获得任何所执行的语句的行
＆GT;当前事务。这意味着，如果任何一个事务的语句均为
＆GT;执行第二次，他们将读取相同的行集。该范围内持有锁
＆GT;直到交易完成。这是最严格的隔离级别
＆GT;因为它锁定键的整个范围，并持有这些锁直到事务
＆GT;完成。因为并发低，只能用这个选项，如果必要。本
＆GT;选项有相同的效果作为所有表中的所有SELECT语句设置HOLDLOCK
＆GT;在一个事务。
但几乎可以肯定，锁升级会造成阻塞和您的用户将是pretty的水中死得多（在我的经验）。
所以...
等到你有一个定期维护窗口。坐落在单用户模式的数据库，进行更改，并使其重新联机。
I have a C# program that needs to perform a group of mass updates (20k+) to a SQL Server table. Since other users can update these records one at a time via an intranet website, we need to build the C# program with the capability of locking the table down. Once the table is locked to prevent another user from making any alterations/searches we will then need to preform the requested updates/inserts.
Since we are processing so many records, we cannot use TransactionScope (seemed the easiest way at first) due to the fact our transaction winds up being handled by the MSDTC service. We need to use another method.
Based on what I've read on the internet using a SqlTransaction object seemed to be the best method, however I cannot get the table to lock. When the program runs and I step through the code below, I'm still able to perform updates and search via the intranet site.
My question is twofold. Am I using the SqlTransaction properly? If so (or even if not) is there a better method for obtaining a table lock that allows the current program running to search and preform updates?
I would like for the table to be locked while the program executes the code below.
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 doesn't really provide a way to exclusively lock a table: it's designed to try to maximize concurrent use while keeping ACID.
You could try using these table hints on your queries:
TABLOCK
Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.
TABLOCKX
Specifies that an exclusive lock is taken on the table.
UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
XLOCK
Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
HOLDLOCK/SERIALIZABLE
Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Alternatively, you could try SET TRANSACTION ISOLATION LEVEL SERIALIZABLE:
> - Statements cannot read data that has been modified but not yet committed by other
> transactions.
>
> - No other transactions can modify data that has been read by the current transaction
> until the current transaction completes.
>
> - Other transactions cannot insert new rows with key values that would fall in the
> range of keys read by any statements in the current transaction until the current
> transaction completes.
>
> Range locks are placed in the range of key values that match the search conditions of
> each statement executed in a transaction. This blocks other transactions from updating
> or inserting any rows that would qualify for any of the statements executed by the
> current transaction. This means that if any of the statements in a transaction are
> executed a second time, they will read the same set of rows. The range locks are held
> until the transaction completes. This is the most restrictive of the isolation levels
> because it locks entire ranges of keys and holds the locks until the transaction
> completes. Because concurrency is lower, use this option only when necessary. This
> option has the same effect as setting HOLDLOCK on all tables in all SELECT statements
> in a transaction.
But almost certainly, lock escalation will cause blocking and your users will be pretty much dead in the water (in my experience).
So...
Wait until you have a schedule maintenance window. Set the database in single-user mode, make your changes and bring it back online.
