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.

Answer 2

As someone has pointed out, the transaction doesn't seem to be used after being taken out.

From the limited information we have on the app/purpose, it's hard to tell, but from the code snippet, it seems to me we don't need any locking. We are getting some data from source X (in this case _reader) and then inserting/updating into destination Y.

All the validation happens against the source data to make sure it's correct, it doesn't seem like we're making any decision or care for what's in the destination.

If the above is true then a better approach would be to load all this data into a temporary table (can be a real temp table "#" or a real table that we destroy afterwards, but the purpose is the same), and then in a single sql statement, we can do a mass insert/update from the temp table into our destination. Assuming the db schema is in decent shape, 20 (or even 30) thousand records should happen almost instantly without any need to wait for maintenance window or lock out users for extended periods of time

Also to strictly answer the question about using transaction, below is a simple sample on how to properly use a transaction, there should be plenty of other samples and info on the web

SqlConnection conn = new SqlConnection();
SqlCommand cmd1 = new SqlCommand();
SqlTransaction tran = conn.BeginTransaction();

...
cmd1.Transaction = tran;
...
tran.Commit();

Answer 3

Try this: when you get records from you table (in the GetRecords() function?) use TABLOCKX hint:

    SELECT * FROM Table1 (TABLOCKX)

It will queue all other reads and updates outside your transaction until the transaction is either commited or rolled back.

Answer 4

It's all about Isolation level here. Change your Transaction Isolation Level to ReadCommited (didn't lookup the Enum Value in C# but that should be close). When you execute the first update/insert to the table, SQL will start locking and no one will be able to read the data you're changing/adding until you Commit or Rollback thr transaction, provided they are not performing dirty reads (using NoLock on their SQL, or have the connection Isolation level set to Read Uncommited).. Be careful though, depending on how you're inserting/updating data you may lock the whole table for the duration of your transaction though which would cause timeout errors at the client when they try to read from this table while your transaction is open. Without seeing the SQL behind the updates though I can't tell if that will happen here.