Disable read/write to a table via SqlTransaction in .net?

Question

How to use SqlTransaction in .net 2.0 so that when I start reading data from a table, that table is blocked for others (other programs) to read/write to that table?

If SqlTransaction is not a good option, than what is?

Answer 1

This should be allowed by using Serializable transaction together with TABLOCKX hint in initial select statement. TABLOCKX should take exclusive lock on the table so nobody else can use that table and Serializable transaction should demand HOLDLOCK which means that all locks are kept until end of the transaction (you can use HOLDLOCK directly).

Update: I just tested different scenarios in Management studio and it looks like you do not need to explicitly use Serializable transaction. Using TABLOCKX within any transaction is enough.

Be aware that such approach can be big bottleneck because only one transaction can operate on such table = no concurrency. Even if you read and work with single record from million nobody else will be able to work with the table until your transaction ends.

So the command should look like:

SELECT * FROM Table WITH (TABLOCKX) WHERE ...

To use serializable transaction you can use SqlTransaction:

using (SqlConnection connection = new SqlConnection(connectionString))
{
  connection.Open();
  SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);

  try
  {
    ...
    transaction.Commit();
  }
  catch (Exception)
  {
    transaction.Rollback();
    ...
  }
}

Or System.Transactions.TransactionScope (default isolation level should be Serializable).

using (TransactionScope scope = new TransactionScope())
{
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    ...
  }
  scope.Complete();
}