Transaction = unit of work
Desired properties = ACID
ACID
Transaction demarcation -- causing a tx to begin or end. Tx end can be commit or rollback.
Can work with provider transactions or work the DTC = Distributed Transaction Coordinator.
In .NET 1.x, working with the DTC required using Enterprise Services (the .NET hook to COM+).
In .NET 2.0, there is a new System.Transactions namespace providing new ways of doing transactions, including using of lightweight transactions with an automatic move into DTC if required.
Generally: do as little work in transaction as possible. Try to do only INSERT, UPDATE, DELETE operations; do SELECTs in a tx only if you need to take advantage of locking rows.
Code examples from project Connected\Tx.
Here is a simple example. Note that Course.Title has a uniqueness constraint on it, so the second insert will fail.
using (DbConnection conn = GetDbConnection())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into Course(Title) values('Underwater Breathing')";
cmd.CommandType = CommandType.Text;
conn.Open();
DbTransaction tx = conn.BeginTransaction();
try
{
cmd.Transaction = tx; // if you don't do this, you will get an error
Console.WriteLine("Trying 1");
cmd.ExecuteNonQuery();
Console.WriteLine("Trying 2");
cmd.ExecuteNonQuery();
Console.WriteLine("Trying to commit.");
tx.Commit();
Console.WriteLine("Made it.");
}
catch (Exception e)
{
DescribeException(e, "in the course of business.");
try
{
tx.Rollback();
}
catch (Exception e2)
{
DescribeException(e2, "while attempting to rollback");
}
}
}
New in .NET 2.0 is the System.Transactions namespace. It provides transactions that are
Only the SQL Server data provider (System.Data.SqlClient) using SQL Server 2005 fully interacts with promotability. However, the OleDb provider can, sometimes, work with the lightweight part. (not with the Jet engine).
Your project will need to reference System.Transactions.dll.
Here is an example:
try
{
using (TransactionScope ts = new TransactionScope())
{
using (DbConnection conn = GetDbConnection())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into Course(Title) values('Underwater Breathing')";
cmd.CommandType = CommandType.Text;
conn.Open();
Console.WriteLine("Trying 1");
cmd.ExecuteNonQuery();
Console.WriteLine("Trying 2");
cmd.ExecuteNonQuery();
Console.WriteLine("Trying to commit.");
}
ts.Complete();
Console.WriteLine("Made it.");
}
}
catch (Exception e)
{
DescribeException(e, "in the course of business.");
}
IsolationLevel values [from MS docs]:
| Member name | Description |
|---|---|
| Chaos | The pending changes from more highly isolated transactions cannot be overwritten. |
| ReadCommitted | Volatile data cannot be read during the transaction, but can be modified. |
| ReadUncommitted | Volatile data can be read and modified during the transaction. |
| RepeatableRead | Volatile data can be read but not modified during the transaction. New data can be added during the transaction. |
| Serializable | Volatile data can be read but not modified, and no new data can be added during the transaction. |
| Snapshot | Volatile data can be read. Before a transaction modifies data, it verifies
if another transaction has changed the data after it was initially read.
If the data has been updated, an error is raised. This allows a transaction
to get to the previously committed value of the data. When you try to promote a transaction that was created with this isolation level, an InvalidOperationExceptionis thrown with the error message "Transactions with IsolationLevel Snapshot cannot be promoted". |
| Unspecified | A different isolation level than the one specified is being used, but the level cannot be determined. An exception is thrown if this value is set. |
When nested transaction scopes occur, the inner transaction has three options. These are set on the c-tor. Values are from the TransactionScopeOption enumeration [from MS docs]:
| Member name | Description |
|---|---|
| Required | A transaction is required by the scope. It uses an ambient transaction if one already exists. Otherwise, it creates a new transaction before entering the scope. This is the default value. |
| RequiresNew | A new transaction is always created for the scope. |
| Suppress | The ambient transaction context is suppressed when creating the scope. All operations within the scope are done without an ambient transaction context. |
When a transaction scope uses Required and joins an ambient transaction, it will participate in voting on the commit. If the inner scope does not call Complete, the vote will fail and the overall transaction will fail.
Check it out:
private static void DoTx4()
{
try
{
TransactionOptions txOpt = new TransactionOptions();
txOpt.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
txOpt.Timeout = new TimeSpan(0, 2, 0); // 2 minutes
using (TransactionScope ts
= new TransactionScope(TransactionScopeOption.Required,txOpt))
{
using (DbConnection conn = GetDbConnection())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into Course(Title) values('Underwater Breathing')";
cmd.CommandType = CommandType.Text;
conn.Open();
Console.WriteLine("Trying 1");
cmd.ExecuteNonQuery();
DoTx4a();
}
ts.Complete();
Console.WriteLine("Made it. 4");
}
}
catch (Exception e)
{
DescribeException(e, "in the course of business.");
}
Console.WriteLine("Hit enter to continue.");
Console.ReadLine();
}
private static void DoTx4a()
{
using (TransactionScope ts
= new TransactionScope(TransactionScopeOption.Required))
{
using (DbConnection conn = GetDbConnection())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into Course(Title) values('Underwater Breathing')";
cmd.CommandType = CommandType.Text;
conn.Open();
Console.WriteLine("Trying 2");
cmd.ExecuteNonQuery();
}
ts.Complete();
Console.WriteLine("Made it - 4a.");
}
}