TransactionScope in a .NET Application

TransactionScope, conceptually simple, hides a lot of magic behind the scenes. It allows one transaction to be distributed across multiple machines. To do this, it needs sophisticated support from the database - and the overhead may or may not be high.

You may need a simpler and more limited piece of technology: DbTransaction. Your Db provider can implement a transaction with semantics like sql BEGIN TRANSACTION

and COMMIT TRANSACTION

: in short, a transaction that only spans one connection on one machine.

Then your code would look like ...

DbCommand cmd = ...;
using (DbTransaction trans = Connection.BeginTransaction()) {
  cmd.Transaction = trans; //sometimes optional, though MS-SQL requires it.

  cmd.ExecuteNonQuery()
  [...other db commands with the same connection and transaction...]
  trans.Commit();
}

      

        
        
        
      

    

To be crystal clear: you can use several other connections while the transaction is open, but there is no connection between them. Commands executed on different connections are not rolled back when the transaction is aborted (just like any C # code will not be rolled back when the transaction is aborted).

This approach is lighter, finer grained, and works for a wider range of database vendors, and does not require MSDTC downloads. On the other hand, transactions are connection specific (if you have multiple connections, they do not share the transaction), they cannot be distributed and you have to manually enlist the commands in the transaction (at least for MS -SQL).

Note that if you fail to enlist the command in the transaction, MS-SQL will throw an exception, SQLite will implicitly log the command, however, and I'm not sure what the oracle is doing. Since all commands must be registered in transactions, this is just an unauthorized redundant API, but this is not very problematic.