Executing select sql query in a suppressed transaction scope

Question

Hi,

There is a selection sql query(select * from table1 e.g.) by design in a transactionscope in c#. Normally there is an ambient transaction but If I suppress the ambient transaction when executing this sql query, is there a performance gain or not?

using (TransactionScope scope1 = new TransactionScope())
{
     // here there are some business processes

   using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Suppressed)) //Is this suppressed transaction scope useful in terms of performance?
   {

       //Here there is a select sql query with no lock table hint.
   }
}

Answer 1

Yes- since TransactionScope (as you've used it in your sample) uses the Serializable isolation level, suppressing it for certain queries that don't need the protection of that isolation level will prevent read locks from being taken on the DB server (especially if you're using READ COMMITTED SNAPSHOT as your default isolation level). Also, if other things you've done would promote the transaction to the DTC (ie, multiple connections, etc), you'll save the time to coordinate DTCs, which can be slow.