SQL in a Nutshell, 2nd Edition

Beginning a Transaction

Beginning a transaction is the first step in guaranteeing atomicity when executing multiple SQL statements. After beginning a transaction, the transaction can be committed to make the changes performed by the executed SQL statements permanent or the transaction can be rolled back to abort the changes and leave the database unchanged.

Beginning an ADO.NET transaction

To begin a transaction using ADO.NET, invoke the BeginTransaction method on a connection object. A Transaction object is returned that can be used in the creation of ADO.NET Command objects that execute within the same transaction. To execute a Command object within the new transaction, you must first attach the Transaction object to the Command object’s Transaction property. Following is the syntax for starting a transaction by creating a Transaction object in ADO.NET:

{Odbc|OleDb|Sql}Transaction transaction = 
   connection.BeginTransaction([IsolationLevel.
      {Chaos | ReadCommitted | ReadUncommitted |RepeatableRead |   
       Serializable | Unspecified}] );
{Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
statement.Transaction = transaction;

When creating the Transaction object, you can optionally specify the isolation level that is used for the transaction. The isolation level controls how much (or little) your database transactions are insulated from the effects of other transactions. The available isolation levels are:

Chaos

Pending changes of other transactions with higher isolation levels ...