C# method to lock SQL Server table

Question

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();
}

Answer 1

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.