A transaction treats a series of operations as a single unit of work, ensurign the ACID properties:

• Atomicity: All operations within a transaction must succeed or fail as a whole.
• Consistency: The system remains in a consistent state after the transaction completes.
• Isolation: Transactions are isolated from one another until completion.
• Durability: Once committed, changes persist permanently.

In .NET, there are five common mechanisms for managing transactions:

• SQL and stored procedure-level transactions (Database Transactions)
• ADO.NET transactions
• ASP.NET page-level transactions
• Enterprise Services (COM+) transactions
• System.Transactions-based transactions

Data base Transactions

Database transactions serve as the foundation for other transaction models. Different database systems have their own rules when creating transactions:

• SQL Server: Operates in auto-commit mode by default.
• Oracle: Requires an explicit commit statement.

Here is an example of explicitly defining a transaction in SQL Server T-SQL:

DECLARE @TranName VARCHAR(20);
SET @TranName = 'MyTransaction';

BEGIN TRANSACTION @TranName;

USE AdventureWorks;
DELETE FROM AdventureWorks.HumanResources.JobCandidate WHERE JobCandidateID = 13;

COMMIT TRANSACTION MyTransaction;

Or within a stored procedure:

CREATE PROCEDURE Tran1
AS
BEGIN TRAN;
SET XACT_ABORT ON; -- Automatically rolls back if an error occurs.
INSERT INTO P_Category (CategoryId, Name) VALUES ('1', 'test1');
INSERT INTO P_Category (CategoryId, Name) VALUES ('2', 'test2');
COMMIT TRAN;

ADO.NET Transactions

ADO.NET transactions use the DbTransaction class and its derivatives. These are local transactions tied to a single connection.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString))
{
    conn.Open();
    using (var tran = conn.BeginTransaction())
    {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.Transaction = tran;
            cmd.CommandType = CommandType.Text;

            try
            {
                cmd.CommandText = "INSERT INTO TranTable(Priority) VALUES(1)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TranTable(Priority) VALUES(256)";
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
            }
        }
    }
}

System.EnterpriseServices Transactions

This approach automatically manages transactions without requiring explicit coding. However, it relies on the COM+ hosting model.

[Transaction(TransactionOption.Required)]
public class OrderControl : ServicedComponent
{
    [AutoComplete]
    public void CreateOrder(Order order)
    {
        using (var data = new OrderData())
        {
            data.Insert(order);
        }
    }
}

System.Transactions

The System.Transactions namespace provides both explicit and implicit transaction management models. The recommended approach is using TransactionScope for simplicity.

Explicit Transaction Example

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString))
{
    using (var ct = new CommittableTransaction())
    {
        conn.Open();
        conn.EnlistTransaction(ct);

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            try
            {
                cmd.CommandText = "INSERT INTO TranTable(Priority) VALUES(1)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TranTable(Priority) VALUES(256)";
                cmd.ExecuteNonQuery();
                ct.Commit();
            }
            catch (SqlException)
            {
                ct.Rollback();
            }
        }
    }
}

Implicit Transaction with TransactionScope

using (var conn = new SqlConnection("Data Source=.; Initial Catalog=TestDb; Integrated Security=SSPI;"))
{
    using (var scope = new TransactionScope())
    {
        conn.Open();
        try
        {
            var cmd = new SqlCommand("INSERT INTO Test(Name, Value) VALUES ('Test1', '1')", conn);
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Test(Name, Value) VALUES ('Test2', '2')";
            cmd.ExecuteNonQuery();

            scope.Complete();
        }
        catch (SqlException)
        {
        }
    }
}

Transaction Isolation Levels

The isolation level determines how transactions interact with each other. Options include:

• Serializable: Highest isolation level, preventing any modifications.
• RepeatableRead: Prevents reading uncommitted data but allows inserts.
• ReadCommitted: Prevents dirty reads but allows non-repeatable reads.
• ReadUncommitted: Allows reading uncommitted data.

Nested Transactions

using (var outer = new TransactionScope())
{
    using (var inner = new TransactionScope(TransactionScopeOption.Required))
    {
        // Perform transactional work
        inner.Complete();
    }
    // More transactional work
    outer.Complete();
}

Dependent Transactions

To manage transactions across multiple threads, use dependent transactions:

void Main()
{
    using (var scope = new TransactionScope())
    {
        var dTx = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
        var thread = new Thread(ThreadMethod);
        thread.Start(dTx);
        scope.Complete();
    }
}

void ThreadMethod(object transaction)
{
    var dTx = (DependentTransaction)transaction;
    try
    {
        using (var ts = new TransactionScope(dTx))
        {
            // Perform transactional work
            ts.Complete();
        }
    }
    finally
    {
        dTx.Complete();
    }
}