ADO - ADO.NET Transactions & Distributed

1. What is a transaction

A transaction groups multiple database operations so that they follow ACID rules:

Atomicity – all succeed or all fail
Consistency – DB stays valid
Isolation – concurrent operations don’t interfere
Durability – committed data survives crashes

2. Local (Single-Database) Transactions

Used when all operations are on one database and one connection.

How it works

Transaction is managed inside the database
Fast, simple, low overhead

Example (SqlTransaction)

using (SqlConnection conn = new SqlConnection(cs))
{
    conn.Open();
    SqlTransaction tx = conn.BeginTransaction();

    try
    {
        SqlCommand cmd1 = new SqlCommand(
            "UPDATE Accounts SET Balance = Balance - 500 WHERE Id=1",
            conn, tx);

        SqlCommand cmd2 = new SqlCommand(
            "UPDATE Accounts SET Balance = Balance + 500 WHERE Id=2",
            conn, tx);

        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();

        tx.Commit();
    }
    catch
    {
        tx.Rollback();
        throw;
    }
}

Key facts

Transaction is tied to one connection
Every command must use the same transaction object
Rollback restores DB state

3. Isolation Levels (important)

Isolation controls how visible changes are to other transactions.

Level	Meaning
ReadUncommitted	Dirty reads allowed
ReadCommitted	Default (safe)
RepeatableRead	Prevents re-reads
Serializable	Strongest, slowest
Snapshot	Version-based, no locks

Higher isolation = more locking = less concurrency

4. Distributed Transactions (Multi-Resource)

Used when operations span:

Multiple databases
Multiple servers
Database + another transactional resource

Handled via TransactionScope.

5. TransactionScope (Distributed or Local)

Why it exists

Simplifies transaction handling
Automatically escalates to distributed if needed

Example

What happens internally

One resource → local transaction
Two different resources → distributed transaction
Uses two-phase commit

6. Two-Phase Commit (2PC)

Phase 1 – Prepare

Ask all resources: “Can you commit?”

Phase 2 – Commit

If all say yes → commit everywhere
If any say no → rollback everywhere

✔ Guarantees consistency
❌ Slower, network-heavy

7. Automatic Escalation (critical detail)

TransactionScope starts as lightweight
Escalates to distributed when:

Second DB connection is opened
Different connection string is used
Different server is involved

This surprises many developers.

8. Performance & Risk Facts

Aspect	Local	Distributed
Speed	Fast	Slow
Complexity	Low	High
Failure risk	Low	Higher
Network dependency	No	Yes

Rule:

Avoid distributed transactions unless absolutely required.

9. Common mistakes (real-world)

❌ Forgetting to call Complete() → auto rollback
❌ Mixing connection strings → unwanted escalation
❌ Long-running transactions → deadlocks
❌ Assuming GC will rollback safely

10. When to use what

Single DB, single server → SqlTransaction
Multiple DBs/resources → TransactionScope
High throughput systems → avoid distributed; redesign logic

One-line summary

Local transactions = fast, simple, one database
Distributed transactions = coordinated commits across resources, slower, higher risk
TransactionScope makes it easy—but can silently escalate