What Are Transactions?

A transaction is a sequence of database operations that are treated as a single unit of work. Either all operations succeed together, or none of them take effect. There's no in-between state where some changes are saved and others aren't.

Imagine transferring money between bank accounts. You need to subtract $100 from Account A and add $100 to Account B. If the subtraction succeeds but the addition fails (due to a system crash or error), money disappears – a catastrophic problem. Transactions prevent this: both operations succeed together, or both are cancelled, keeping the data consistent.

In test automation, transactions serve a different but equally critical purpose: perfect isolation and cleanup. You can create test data, run your tests against it, then rollback the entire transaction – instantly undoing all changes and leaving the database exactly as it was before the test started.

Transactions follow the ACID properties (briefly reviewed from Lesson 1):

• Atomicity: All operations succeed or all fail – no partial completion
• Consistency: The database moves from one valid state to another valid state
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Once committed, changes are permanent (survive system failures)

Understanding transactions transforms how you approach test data management – from manual cleanup to automatic, guaranteed restoration.

BEGIN, COMMIT, and ROLLBACK

Every transaction starts with BEGIN TRANSACTION (or START TRANSACTION or just BEGIN, depending on your database), and ends with either COMMIT (make changes permanent) or ROLLBACK (undo all changes).

Here's a basic transaction that creates a user and an order together:

BEGIN TRANSACTION;

    -- Step 1: Create user
    INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive)
    VALUES ('[email protected]', 'New', 'User', CURRENT_TIMESTAMP, 1);

    -- Step 2: Get the generated UserId (SQL Server syntax)
    DECLARE @UserId INT = SCOPE_IDENTITY();

    -- Step 3: Create order for this user
    INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status)
    VALUES (@UserId, CURRENT_TIMESTAMP, 100.00, 'Pending');

COMMIT;  -- Both inserts succeed together

If any operation fails (the email already exists, the foreign key constraint fails, etc.), the entire transaction automatically rolls back. Neither the user nor the order is created – the database remains unchanged.

You can also explicitly rollback if your validation logic determines something is wrong:

BEGIN TRANSACTION;

    UPDATE Products
    SET Price = Price * 1.20
    WHERE CategoryId = 5;

    -- Verify the changes are reasonable
    IF (SELECT MAX(Price) FROM Products WHERE CategoryId = 5) > 1000
    BEGIN
        ROLLBACK;  -- Prices became too high, undo changes
        PRINT 'Transaction rolled back - prices exceeded limit';
    END
    ELSE
    BEGIN
        COMMIT;  -- Changes look good
        PRINT 'Transaction committed successfully';
    END

The key insight: until you COMMIT, all changes exist only in your session – other database connections can't see them, and they're not permanent.

Transactions for Test Data Isolation

Here's the pattern that revolutionizes test automation: wrap your entire test in a transaction and always rollback at the end:

BEGIN TRANSACTION;

    -- Setup: Create test data
    INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive)
    VALUES ('[email protected]', 'Test', 'User', CURRENT_TIMESTAMP, 1);

    DECLARE @UserId INT = SCOPE_IDENTITY();

    INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status)
    VALUES (@UserId, CURRENT_TIMESTAMP, 150.00, 'Processing');

    -- At this point, your C# test code would:
    -- 1. Query the database to verify order exists
    -- 2. Call your API endpoints
    -- 3. Run assertions
    -- 4. Verify expected state changes

    -- Cleanup: Always rollback (database returns to original state)
ROLLBACK;

When the transaction rolls back, the user and order vanish – completely and instantly. The database is exactly as it was before the transaction began. No cleanup queries, no orphaned data, no test pollution.

Here's how this looks in actual C# test code:

[Test]
public void TestOrderCreation()
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        // Begin transaction
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                // Setup: Insert test user
                var userId = connection.QuerySingle<int>(
                    "INSERT INTO Users (Email, FirstName, LastName, CreatedDate, IsActive) " +
                    "VALUES (@Email, @FirstName, @LastName, CURRENT_TIMESTAMP, 1); " +
                    "SELECT SCOPE_IDENTITY();",
                    new { Email = "[email protected]", FirstName = "Test", LastName = "User" },
                    transaction);

                // Act: Create order through your application code
                var response = orderService.CreateOrder(userId, productIds);

                // Assert: Verify order in database
                var order = connection.QuerySingleOrDefault(
                    "SELECT * FROM Orders WHERE UserId = @UserId",
                    new { UserId = userId },
                    transaction);

                Assert.IsNotNull(order);
                Assert.AreEqual("Processing", order.Status);
                Assert.AreEqual(150.00, order.TotalAmount);
            }
            finally
            {
                // Cleanup: Always rollback (even if test fails)
                transaction.Rollback();
            }
        }
    }
}

The finally block ensures rollback happens regardless of whether the test passes or fails. This pattern guarantees zero test data pollution.

This pattern works beautifully for individual test isolation, ensuring each test starts with a clean slate.

Transaction Isolation Levels

When multiple transactions run concurrently, isolation levels control how they interact and what data they can see from each other. Different isolation levels balance between consistency (accuracy of data) and performance (how many operations can run simultaneously).

The four standard isolation levels, from least to most isolated:

READ UNCOMMITTED

Transactions can see uncommitted changes from other transactions ("dirty reads"). Fastest but least consistent. Rarely used except for approximate counts or reports where precision doesn't matter.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
    SELECT COUNT(*) FROM Orders;  -- Might include uncommitted orders from other transactions
COMMIT;

READ COMMITTED (Default in most databases)

Transactions only see committed data from other transactions. Prevents dirty reads but allows "non-repeatable reads" (if you query the same row twice in your transaction, another transaction might change it between your reads).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT Price FROM Products WHERE ProductId = 123;  -- Returns $50
    -- Another transaction commits a price change to $60
    SELECT Price FROM Products WHERE ProductId = 123;  -- Now returns $60
COMMIT;

REPEATABLE READ

Once you read a row in your transaction, its values won't change even if other transactions modify it. Prevents non-repeatable reads but allows "phantom reads" (other transactions can insert new rows that match your query conditions).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT * FROM Products WHERE CategoryId = 5;  -- Returns 10 products
    -- Another transaction inserts a new product in category 5
    SELECT * FROM Products WHERE CategoryId = 5;  -- Might return 11 products (phantom)
COMMIT;

SERIALIZABLE

Strongest isolation. Transactions execute as if they were running one at a time, with no concurrency. Prevents all anomalies but has the highest performance cost. The database may lock entire tables to ensure this.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    SELECT * FROM Products WHERE CategoryId = 5;
    -- No other transaction can modify, insert, or delete products in category 5 until this commits
    UPDATE Products SET Price = Price * 1.10 WHERE CategoryId = 5;
COMMIT;

Choosing Isolation Levels for Tests

For test automation:

• READ COMMITTED (default): Usually sufficient for tests since you're typically the only user modifying test data
• SERIALIZABLE: Use when testing concurrent scenarios or when you need absolute consistency during complex multi-step test setups
• READ UNCOMMITTED: Rarely needed in tests; only for performance tests where approximate data is acceptable

Understanding isolation levels helps you reason about what your tests can and can't see when multiple tests or background processes run concurrently.

Transactions and Locks

When a transaction modifies data, the database typically places locks on those rows to prevent other transactions from modifying them simultaneously. This ensures consistency but can cause performance issues if locks are held too long.

-- Transaction 1 (in one session)
BEGIN TRANSACTION;
    UPDATE Products SET Price = 100.00 WHERE ProductId = 123;
    -- Lock is held on ProductId 123
    -- Transaction hasn't committed yet...
    WAITFOR DELAY '00:00:30';  -- Simulating a long operation
COMMIT;

-- Transaction 2 (in another session, running concurrently)
BEGIN TRANSACTION;
    UPDATE Products SET StockQuantity = 50 WHERE ProductId = 123;
    -- This will BLOCK, waiting for Transaction 1 to commit or rollback
COMMIT;

Transaction 2 can't proceed until Transaction 1 releases the lock. If Transaction 1 never commits (due to a bug or system issue), Transaction 2 waits indefinitely (or until a timeout).

Deadlocks

A deadlock occurs when two transactions are each waiting for locks held by the other:

-- Transaction 1
BEGIN TRANSACTION;
    UPDATE Products SET Price = 100.00 WHERE ProductId = 1;  -- Locks Product 1
    -- Now needs Product 2...
    UPDATE Products SET Price = 200.00 WHERE ProductId = 2;  -- Blocked by Transaction 2
COMMIT;

-- Transaction 2 (running simultaneously)
BEGIN TRANSACTION;
    UPDATE Products SET Price = 200.00 WHERE ProductId = 2;  -- Locks Product 2
    -- Now needs Product 1...
    UPDATE Products SET Price = 100.00 WHERE ProductId = 1;  -- Blocked by Transaction 1
COMMIT;

Transaction 1 holds a lock on Product 1 and waits for Product 2. Transaction 2 holds a lock on Product 2 and waits for Product 1. Neither can proceed – they're deadlocked.

Database systems detect deadlocks automatically and kill one of the transactions (usually the one that has done less work). Your application receives an error and should retry the operation.

Avoiding Lock Problems in Tests

• Keep transactions short: Don't hold locks while making API calls or performing lengthy calculations
• Commit or rollback quickly: Don't leave transactions open between test steps
• Access tables in consistent order: If multiple tests modify Products and Orders, always access Products first, then Orders
• Use isolated test data: Different tests should work with different users/orders to minimize lock contention

In test automation, lock issues usually arise when running parallel tests against a shared database – proper test data isolation prevents most problems.

When Not to Use Transactions

While transaction-based isolation is powerful, there are scenarios where it's not appropriate:

Testing actual commit behavior: If you need to verify that your application code commits transactions correctly, you can't wrap the test in a rollback – you need to let the commit happen and manually clean up afterward.

Testing cross-database operations: Distributed transactions across multiple databases are complex and may not be supported by your test infrastructure.

Integration tests with external systems: If your test calls external APIs that modify data elsewhere, rolling back your database transaction doesn't undo those external changes.

Performance testing: Transaction overhead can skew performance measurements. For load testing, you might want to commit data to measure realistic performance.

UI automation tests: When Selenium or other UI tools interact with your application, they trigger operations that commit to the database. You can't externally wrap those in a rollback transaction.

For these scenarios, fall back to traditional cleanup patterns:

[Test]
public void TestExternalAPIIntegration()
{
    var testUserId = 0;

    try
    {
        // Setup: Create test data (will be committed)
        testUserId = CreateTestUser("[email protected]");

        // Act: Call external API
        var result = externalService.ProcessUser(testUserId);

        // Assert
        Assert.IsTrue(result.Success);
    }
    finally
    {
        // Cleanup: Manually delete test data
        if (testUserId > 0)
        {
            DeleteTestUser(testUserId);
        }
    }
}

Use transaction-based isolation when possible for its speed and reliability, but recognize when manual cleanup is necessary.