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.

 
conn.BeginTransaction(IsolationLevel.ReadCommitted);
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

 
using (TransactionScope scope = new TransactionScope()) { using (SqlConnection c1 = new SqlConnection(cs1)) { c1.Open(); // DB operation 1 } using (SqlConnection c2 = new SqlConnection(cs2)) { c2.Open(); // DB operation 2 } scope.Complete(); }

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