Transactions & Batch Operations

Level: Intermediate

ℹ️ Where This Fits

Transactions ensure multiple operations succeed or fail together. Essential for maintaining data integrity when multiple related updates must happen atomically (e.g., create student + create default fee account).

ℹ️ What You'll Learn

• Transaction purpose: Group multiple operations (all succeed or all fail together)
• Atomicity: Either all operations complete or none do (no partial updates)
• BeginTransaction: var trans = conn.BeginTransaction() starts transaction
• Commit: trans.Commit() saves all changes if no errors
• Rollback: trans.Rollback() undoes all changes on error
• Using statement: using (var trans = conn.BeginTransaction()) auto-rollback on exception
• IsolationLevel.ReadCommitted: Default (prevents dirty reads)
• IsolationLevel.RepeatableRead: Prevents non-repeatable reads
• IsolationLevel.Serializable: Strictest (prevents phantoms but slow)
• IsolationLevel.ReadUncommitted: Dirty reads allowed (fastest but risky)
• Nested transactions: Not supported in SQL Server (inner commits are logical only)
• School Management example: Create student + auto-generate roll number + create default fee account in one transaction
• Batch operations: Multiple INSERT/UPDATE in single transaction (more efficient)
• ExecuteNonQuery for batch: Execute multiple commands in loop within transaction
• Deadlock prevention: Keep transactions short, access tables in same order
• Error handling: Catch exception, call Rollback in catch block
• Common mistakes: Not rolling back on error, forgetting to dispose transaction, long-running transactions

Basic Transaction Pattern

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "INSERT INTO Students (Name, ClassName, Status) VALUES (@name, @className, @status)";
                cmd.Parameters.AddWithValue("@name", "Ravi Kumar");
                cmd.Parameters.AddWithValue("@className", "10-A");
                cmd.Parameters.AddWithValue("@status", "Active");
                
                var rowsAffected = await cmd.ExecuteNonQueryAsync();
                if (rowsAffected == 0)
                    throw new Exception("Insert failed");
            }
            
            // Create default fee account for new student
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "INSERT INTO FeeAccount (StudentId, TotalFees, Status) VALUES (@studentId, @fees, @status)";
                cmd.Parameters.AddWithValue("@studentId", 101);
                cmd.Parameters.AddWithValue("@fees", 50000);
                cmd.Parameters.AddWithValue("@status", "Pending");
                
                await cmd.ExecuteNonQueryAsync();
            }
            
            trans.Commit();  // Both operations succeeded
        }
        catch (Exception ex)
        {
            trans.Rollback();  // Undo both operations
            throw;
        }
    }
}

Isolation Levels

using (var trans = conn.BeginTransaction(IsolationLevel.RepeatableRead))
{
    // Use transaction
    trans.Commit();
}

Level	Dirty Read	Non-Repeatable	Phantom	Speed
ReadUncommitted	✓	✓	✓	Fast
ReadCommitted	✗	✓	✓	Normal
RepeatableRead	✗	✗	✓	Slower
Serializable	✗	✗	✗	Slowest

Batch Insert Operations

Insert multiple students in one transaction:

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            var students = new[]
            {
                ("Ravi Kumar", "10-A"),
                ("Priya Sharma", "10-A"),
                ("Arjun Reddy", "10-B")
            };
            
            foreach (var (name, className) in students)
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = "INSERT INTO Students (Name, ClassName, Status) VALUES (@name, @className, @status)";
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@className", className);
                    cmd.Parameters.AddWithValue("@status", "Active");
                    
                    await cmd.ExecuteNonQueryAsync();
                }
            }
            
            trans.Commit();  // All 3 students inserted atomically
        }
        catch
        {
            trans.Rollback();  // All 3 rolled back
            throw;
        }
    }
}

School Management - Enrollment Transaction

public async Task EnrollStudentAsync(string name, string className)
{
    using (var conn = new SqlConnection(connString))
    {
        await conn.OpenAsync();
        
        using (var trans = conn.BeginTransaction())
        {
            try
            {
                // Step 1: Create student record
                int studentId;
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = @"
                        INSERT INTO Students (Name, ClassName, Status, CreatedAt) 
                        VALUES (@name, @className, @status, GETDATE());
                        SELECT SCOPE_IDENTITY();";
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@className", className);
                    cmd.Parameters.AddWithValue("@status", "Active");
                    
                    studentId = (int)await cmd.ExecuteScalarAsync();
                }
                
                // Step 2: Create fee account
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = @"
                        INSERT INTO FeeAccount (StudentId, TotalFees, PaidAmount, Status, DueDate)
                        VALUES (@studentId, @fees, 0, @status, DATEADD(month, 3, GETDATE()))";
                    cmd.Parameters.AddWithValue("@studentId", studentId);
                    cmd.Parameters.AddWithValue("@fees", 50000);
                    cmd.Parameters.AddWithValue("@status", "Pending");
                    
                    await cmd.ExecuteNonQueryAsync();
                }
                
                // Step 3: Create attendance record
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = @"
                        INSERT INTO Attendance (StudentId, Date, IsPresent, MarkedAt)
                        VALUES (@studentId, GETDATE(), 1, GETDATE())";
                    cmd.Parameters.AddWithValue("@studentId", studentId);
                    
                    await cmd.ExecuteNonQueryAsync();
                }
                
                trans.Commit();  // All 3 operations succeed
                return studentId;
            }
            catch (Exception ex)
            {
                trans.Rollback();  // Undo all 3 operations
                throw new Exception("Enrollment failed", ex);
            }
        }
    }
}

Best Practices

✓ Keep transactions short (release locks quickly) ✓ Use async methods with transactions ✓ Always wrap transaction in try-catch ✓ Catch specific exceptions when possible ✓ Use appropriate isolation level

⚠️ Deadlock Risk

Long transactions holding locks on multiple tables can cause deadlocks. Keep transactions as short as possible and access tables in consistent order.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Transactions & Batch Operations. Try these prompts:

• "What is transaction atomicity and why does it matter?"
• "Show me how to rollback a transaction on error."
• "What's the difference between isolation levels?"
• "How do you batch insert multiple rows in a single transaction?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 07. Stored Procedures