Transactions ensure that a set of related operations are all completed or are aborted, leaving the involved resources in the state that they were in when the transaction was started. Transactions are most commonly used when all related operations must be completed successfully (e.g., debiting and crediting respective bank accounts when funds are transferred from one to the other). The related operations are bound together into a transactional unit of work that must either completely succeed or completely fail. This is referred to as committing or aborting the transaction.
ADO.NET supports manual transactions whose bounds are defined by the application using explicit commands to begin and end the transaction. Manual transactions are easy to code and offer both performance and flexibility, but can’t span multiple data resources. Instances of a .NET Framework class can also be registered with COM+ component services to enlist in and participate in automatic transactions. Automatic transactions are the only choice when a transaction spans multiple data resource.
When selecting a transaction model, DBMS transactions are another option. DBMS transactions contain all transaction logic within a stored procedure; they offer the best performance but are more difficult to code than ADO.NET manual transactions. The DBMS transaction model is also the most limited model. If more than one stored procedure needs to be called within the context of a single transaction, either the manual or automatic transaction model must be used. DBMS transactions can be used together with manual transactions, but code must be written to deal with transactions that are rolled back from within the stored procedures.
Finally, it’s important to understand that transactions should be used only when required. Using transactions imposes a performance penalty due to the system overhead in managing the transaction. Transactions can also block the work of other users in the system, which causes performance problems. For that reason, if transactions are required, the isolation level of the transactions should be carefully considered.
Manual Transactions
Manual transactions use explicit statements to control the boundaries of a transaction. Transactions are started, and subsequently either committed or rolled back. The SQL .NET data provider allows savepoints to be defined that allow a transaction to be partially rolled back. The OLE DB .NET data provider allows new, or nested, transactions to be started within the boundaries of the parent transaction. If transactions are nested, the parent can’t commit until all nested transactions have committed.
A Transaction is started by calling the BeginTransaction( ) method of a Connection object. You can set a Command object to run in a transaction by setting its Transaction property to a Transaction object connected to the same Connection as the Command object. An overloaded constructor for the Command object allows this to be done in a single statement.
Once running in a Transaction, commands can be executed on the Command object within a try/catch block. If an exception is raised, the Rollback( ) method can be called on the Transaction to roll back all changes; otherwise, the Commit( ) method persists the changes.
The following example demonstrates these concepts. Order and order detail records are inserted within a transaction, thereby ensuring that either both or neither record is added:
String connString = “Data Source=(local);Integrated security=SSPI;” +
“Initial Catalog=Northwind;”;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
//create command and enlist in transaction
SqlCommand cmdOrder = new SqlCommand(“InsertOrder”, conn, tran);
cmdOrder.CommandType = CommandType.StoredProcedure;
SqlCommand cmdOrderDetail = new SqlCommand(“InsertOrderDetail”,
conn, tran);
cmdOrderDetail.CommandType = CommandType.StoredProcedure;
SqlParameterCollection orderParams = cmdOrder.Parameters;
orderParams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);
orderParams[“@OrderID”].Direction = ParameterDirection.InputOutput;
orderParams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);
// … code to define remaining parameters
SqlParameterCollection orderDetailParams = cmdOrderDetail.Parameters;
orderDetailParams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);
orderDetailParams.Add(“@ProductID”, SqlDbType.Int, 5, “ProductID”);
// … code to define remaining parameters
String result = “”;
try
{
// insert order
cmdOrder.Parameters[“@OrderID”].Value = -1;
cmdOrder.Parameters[“@CustomerID”].Value = “ALFKI”;
// … set the other parameters
cmdOrder.ExecuteNonQuery();
// insert order detail with OrderID from the inserted order
cmdOrderDetail.Parameters[“@CustomerID”].Value =
(Int32)cmdOrder.Parameters[“@OrderID”].Value;
cmdOrderDetail.Parameters[“@ProductID”].Value = 20;
//… set the other parameters
cmdOrderDetail.ExecuteNonQuery();
//if okay to here, commit the transaction
tran.Commit();
result = “Transaction commit.”;
}
catch (SqlException ex)
{
tran.Rollback();
result = “ERROR: ” + ex.Message + “; Transaction rollback.”;
}
catch (FormatException ex)
{
tran.Rollback();
result = “ERROR: ” + ex.Message + “; Transaction rollback.”;
}
finally
{
conn.Close();
}
Isolation Levels
The transaction isolation level specifies the transaction locking level for a connection. It determines the extent to which changes to data within a transaction are visible outside that transaction while uncommitted.
Table 16-1 lists and describes problems that might occur if several users access data concurrently without locking.
Table 16-1. Concurrency problems |
|
| Condition | Description |
| Lost update | Two or more transactions select the same row and subsequently update the row. The transactions are unaware of each other and, as a result, updates overwrite one another, resulting in lost data. |
| Uncommitted dependency
(dirty read) |
A second transaction selects a row that has been updated, but not committed, by another transaction. The data being read might be further updated or rolled back by the original transaction, resulting in invalid data in the second transaction. |
| Inconsistent analysis
(nonrepeatable read) |
A second transaction reads different data each time the same row is read. The second transaction reads data that has been changed and committed by another transaction between the reads. |
| Phantom read | An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected within the transaction are missing the newly inserted rows and contain deleted rows that no longer exist. |
Locks ensure transactional integrity and maintain database consistency by controlling how resources can be accessed by concurrent transactions. A lock is an object that indicates a user has some dependency on a resource. Other users are prevented from performing operations that would adversely affect the dependency of the user with the lock. Locks are managed internally by system software and acquired and released as a result of actions taken by users. Table 16-2 lists and describes resource lock modes used by ADO.NET.
Table 16-2. Resource lock modes |
|
| Lock mode | Description |
| Shared | Allows concurrent transactions to read the locked resource. Another transaction can’t modify the locked data while the lock is held. |
| Exclusive | Prevents access, both read and modify, to a resource by concurrent transactions. |
Isolation level is the level at which a transaction is prepared to accept inconsistent data; it is the degree to which one transaction must be isolated from other transactions. As the isolation level increases, access to current data increases at the expense of data correctness. Table 16-3 lists and describes the different isolations supported by ADO.NET. The first four levels are listed in order of increasing isolation.
Table 16-3. IsolationLevelEnumeration |
|
| Name | Description |
| ReadUncommitted | No shared locks are issued, and exclusive locks aren’t honored. A dirty read is possible. |
| ReadCommitted | Shared locks are held while data is read by the transaction. Dirty reads aren’t possible, but nonrepeatable reads or phantom rows can occur because data can be changed before it is committed. |
| RepeatableRead | Shared locks are placed on all data used in a query preventing other users from updating the data. Nonrepeatable reads are prevented, but phantom reads are still possible. |
| Serializable | A range lock, where the individual records and the ranges between records are covered, is placed on the data preventing other users from updating or inserting rows until the transaction is complete. Phantom reads are prevented. |
| Chaos | Pending changes from more highly isolated transactions can’t be overwritten. Not supported by SQL Server. |
| Unspecified | A different isolation level than the one specified is being used, but that level can’t be determined. |
The isolation level can be changed programmatically at any time. If it is changed within a transaction, the new locking level applies to all remaining statements within the transaction.
The following example demonstrates how to set the isolation level for a transaction:
String connString = “Data Source=(local);Integrated security=SSPI;” +
“Initial Catalog=Northwind;”;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran =
conn.BeginTransaction(IsolationLevel.RepeatableRead);
// returns IsolationLevel.RepeatableRead
IsolationLevel il = tran.IsolationLevel;
Savepoints
Rolling back a transaction cancels the effect of all statements in that transaction. In some cases, it is only necessary to roll back a portion of the transaction. This can be done using savepoints.
A savepoint is created using the Save( ) method of the Transaction object. The method takes a string argument specifying the name of the savepoint. A transaction is rolled back to the savepoint by calling the RollBack() method and specifying the name of the savepoint as the optional argument. Savepoints are supported only by the SQL .NET managed data provider; nested transactions can be used with the OLE DB data provider to accomplish a similar result. The following example demonstrates how to use savepoints:
String connString = “Data Source=(local);Integrated security=SSPI;” +
“Initial Catalog=Northwind;”;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
//create command and enlist in transaction
SqlCommand cmd = new SqlCommand(“InsertCustomer”, conn, tran);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameterCollection cparams = cmd.Parameters;
cparams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);
cparams.Add(“@CompanyName”, SqlDbType.NVarChar, 40, “CompanyName”);
// … code to define remaining parameters
try
{
// insert a record into the table
cmd.Parameters[“@CustomerID”].Value=”CUST1″;
// … set the other parameters
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tran.Rollback();
Console.WriteLine(
“ERROR: {0}: Transaction rollback (CUST1).”, ex.Message);
conn.Close();
return;
}
tran.Save(“SavePoint1”);
try
{
// insert a second record into the table
cmd.Parameters[“@CustomerID”].Value = “CUST2”;
// … set the other parameters
cmd.ExecuteNonQuery();
//if okay to here, commit the transaction
tran.Commit();
Console.WriteLine(“Transaction commit (CUST1 and CUST2).”);
}
catch (SqlException ex)
{
tran.Rollback(“SavePoint1”);
tran.Commit();
Console.WriteLine(
“ERROR: {0} Transaction commit (CUST1); ” +
“Transaction rollback (CUST2).”, ex.Message);
}
finally
{
conn.Close();
}
Nested Transactions
Nesting allows one transaction to initiate other transactions. These nested transactions are initiated using the Begin( ) method of the Transaction object, which returns a reference to a new transaction object. However, rolling back this transaction rolls back only the nested transaction and not the entire transaction.
Nested transactions aren’t supported by the SQL Server .NET data provider although savepoints can be used with that data provider to accomplish a similar result. The OLE DB provider for SQL Server also doesn’t support nested transactions.
Transactions Using a DataAdapter
The DataAdapter uses its Command objects DeleteCommand, InsertCommand, and UpdateCommand to update changes back to the data source. As a result, using transactions from a DataAdapter isn’t very different from using them with the Command object directly. If custom updating logic is being used with the DataAdapter, simply create the transaction and assign it to the three update Command objects for the DataAdapter. The following example illustrates how to use transactions with the DataSet and the DataAdapter objects with custom update logic:
String connString = “Data Source=(local);Integrated security=SSPI;” +
“Initial Catalog=Northwind;”;
String sqlSelect = “SELECT * FROM Orders”;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn);
DataSet ds = new DataSet();
// define update logic for the data adapter
// load data from the data source into the DataSet
da.Fill(ds, “Orders”);
// start the transaction
SqlTransaction tran = conn.BeginTransaction();
// associate transaction with the data adapter command objects
da.DeleteCommand.Transaction = tran;
da.InsertCommand.Transaction = tran;
da.UpdateCommand.Transaction = tran;
// … modify the data in the DataSet
// submit changes, commit or rollback, and close the connection
try
{
da.Update(ds, “Orders”);
// commit if successful
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
}
finally
{
conn.Close();
}
When the CommandBuilder generates the updating logic used by the DataAdapter, it doesn’t generate updating logic when it is instantiated. Good design dictates minimizing data interaction within a transaction. This means that the updating logic for the CommandBuilder should be generated before the transaction is started, rather inside the transaction. This is accomplished by calling the GetDeleteCommand(), GetInsertCommand(), and GetUpdateCommand() methods of the CommandBuilder object prior to using it with a transaction the first time. The following example illustrates how to use a transaction with a DataAdapter object that uses a CommandBuilder object to provide update logic:
// … create the connection and data adapter as with custom update logic
// use a command builder to define updating logic
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// generate updating logic for command objects
cb.GetDeleteCommand();
cb.GetInsertCommand();
cb.GetUpdateCommand();
// load data from the data source into the DataSet
da.Fill(ds, “Orders”);
// start the transaction
SqlTransaction tran = conn.BeginTransaction();
// associate transaction with command builder command objects
cb.GetDeleteCommand().Transaction = tran;
cb.GetInsertCommand().Transaction = tran;
cb.GetUpdateCommand().Transaction = tran;
// … modify the data in the DataSet
// submit changes, commit or rollback, and close the connection
try
{
da.Update(ds, “Orders”);
// commit if successful
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
}
finally
{
conn.Close();
}
Automatic Transactions
Microsoft Transaction Server (MTS), COM+ 1.0, and the .NET CLR support the same automatic distributed transaction model. The .NET Framework provides support for transactional components through COM+ services. There are two key benefits to COM+ transactions:
- They allow distributed transactions that span multiple data sources.
- Objects that can participate in COM+ transactions are free from having to anticipate how they might be used within a transaction. A client can perform different tasks with multiple objects, all in the context of a single transaction without the participating objects being aware of the transaction.
Instances of a .NET Framework class can participate in automatic transactions. Once an object is marked to participate in a transaction, it automatically executes within a transaction. This transactional behavior is controlled by the value of the transaction attribute for the .NET class, ASP.NET page, or XML web service method using the object. This allows the instantiated object to be configured programmatically to participate automatically in an existing transaction, to start a new transaction, or to not participate in a transaction.
When a transactional object accesses a data resource, a transaction occurs according to the value of the declarative transaction attribute of the object. When the transactional object accesses a data resource, the data driver enlists in the transaction through the distributed transaction coordinator (DTC).
During the lifetime of an automatic transaction, the objects participating in it can vote to either commit the transaction they are participating in by calling the static SetComplete( ) method of the ContextUtil class or to abort the transaction by calling the static SetAbort( ) method of the ContextUtil class. In the absence of an explicit vote, the default is to commit the transaction. The transaction is committed once it completes if none of the participating objects have voted to abort.
Alternatively, you can apply the AutoCompleteAttribute attribute to a transactional method. This attribute instructs .NET to automatically commit the transaction, provided no exceptions are encountered. If an unhandled exception is thrown, the transaction is automatically rolled back.
For a .NET Framework object to participate in an automatic transaction, it must be registered with COM+ component services. The following steps will prepare a class to participate in an automatic transaction:
- Apply the TransactionAttribute to the class, and specify the transaction behavior, timeout, and isolation level.
- Derive the class from ServicedComponent allowing access to COM+ services.
- Sign the assembly with a strong name. Use the sn.exe utility to create a key pair with the following syntax:
sn -k MyApp.snk
Add the AssemblyKeyFileAttribute or AssemblyKeyNameAttribute assembly attribute specifying the file containing the key pair, for example:
[assembly: AssemblyKeyFileAttribute(“MyApp.snk”)]
- Register the assembly containing the class with the COM+ catalog[1] by executing the .NET Services Registration Tool (regsvcs.exe) with the following syntax:
[1] COM+ services are explored in detail in COM and .NET Component Services, by Juval Löwy (O’Reilly).
regsvcs /appname:MyApp MyAssembly.dll
This step isn’t strictly required. If a client calling the class is managed by the CLR, the registration is performed automatically.
This example is a simple .NET class that participates automatically in transactions:
using System.EnterpriseServices;
using System.Runtime.CompilerServices;
using System.Reflection;
namespace ServicedComponentCS
{
[Transaction(TransactionOption.Required)]
public class ServicedComponentTest : ServicedComponent
{
[AutoComplete]
public void TranTest()
{
bool success = true;
// … do work and store the outcome to success variable
if(success)
{
// don’t need the next line since AutoComplete
// ContextUtil.SetComplete();
}
else
{
ContextUtil.SetAbort();
throw new System.Exception(“Error in Serviced ” +
“Component. Transaction aborted.”);
}
}
}
}
The TransactionOption.Required attribute in this example indicates that every method in this class must be used in a transaction, but the method can use the existing transaction if one has already been started in the caller’s context. The full list of TransactionOption values is featured in Table 16-4.
Table 16-4. Values for the TransactionOption enumeration |
|
| Value | Description |
| Disabled | Indicates that transactions aren’t created or used automatically with this object. |
| NotSupported | Indicates that the object doesn’t run within the scope of transactions. When a request is processed, its object context is created without a transaction, regardless of whether there is a transaction active. |
| Supported | Indicates that the object runs in the context of an existing transaction, if one exists. If no transaction exists, the object runs without a transaction. |
| Required | Indicates that the object requires a transaction. It runs in the scope of an existing transaction, if one exists. If no transaction exists, the object starts one. |
| RequiresNew | Indicates that the object requires a transaction, and a new transaction is started for each request. |
Microsoft.NET 