Home / Database / Transactions

Transaction Management

Overview

A transaction is a sequence of database operations that are executed as a single unit of work. Transactions ensure that either all operations succeed (commit) or all operations fail (rollback), maintaining database consistency.

Transaction management is crucial for maintaining data integrity in multi-user database environments, ensuring that concurrent operations don't corrupt data.

What is a Transaction?

A transaction is a logical unit of work that contains one or more database operations. It follows the ACID properties to ensure data integrity.

Transaction Example

Bank Transfer Transaction:
1. Debit $100 from Account A
2. Credit $100 to Account B

Both operations must succeed together, or both must fail.
Cannot have partial completion!

ACID Properties

Transactions must satisfy the ACID properties:

1. Atomicity

Atomicity ensures that all operations in a transaction are completed successfully, or none are completed at all.

Atomicity Example

Transfer $100 from Account A to Account B:

Either:
✓ Debit $100 from A AND Credit $100 to B (both succeed)

Or:
✗ Neither operation occurs (both fail)

Never:
✗ Debit succeeds but credit fails (partial completion)

2. Consistency

Consistency ensures that a transaction brings the database from one valid state to another, maintaining all integrity constraints.

Consistency Example

Before Transaction:
- Total amount in all accounts: $10,000
- All constraints satisfied

After Transaction:
- Total amount in all accounts: $10,000 (unchanged)
- All constraints still satisfied
- Database in valid state

3. Isolation

Isolation ensures that concurrent transactions don't interfere with each other. Each transaction sees a consistent view of the database.

Isolation Example

Transaction 1: Transfer $100 (A → B)
Transaction 2: Check balance of Account A

Isolation ensures:
- Transaction 2 sees either old balance or new balance
- Never sees inconsistent intermediate state
- Transactions don't see each other's uncommitted changes

4. Durability

Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.

Durability Example

After COMMIT:
- Changes written to disk
- Transaction log records changes
- Even if system crashes, changes are preserved
- Cannot be undone (except through another transaction)

Transaction States

A transaction goes through various states during its lifecycle:

Transaction Lifecycle

1. Active
   └─ Transaction executing

2. Partially Committed
   └─ All operations completed, not yet committed

3. Committed
   └─ Changes permanently saved

4. Failed
   └─ Error occurred, cannot continue

5. Aborted
   └─ Transaction rolled back, changes undone

6. Terminated
   └─ Transaction finished (committed or aborted)

Transaction Commands

BEGIN / START TRANSACTION

Starts a new transaction:

Start Transaction

BEGIN TRANSACTION;
-- Or
START TRANSACTION;

COMMIT

Permanently saves all changes made in the transaction:

Commit Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 
WHERE AccountID = 'A';

UPDATE Accounts SET Balance = Balance + 100 
WHERE AccountID = 'B';

COMMIT;  -- Make changes permanent

ROLLBACK

Undoes all changes made in the transaction:

Rollback Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 
WHERE AccountID = 'A';

-- Error occurs or check fails
ROLLBACK;  -- Undo all changes, restore original state

SAVEPOINT

Creates a point within a transaction that you can roll back to:

Savepoint Example

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 
WHERE AccountID = 'A';

SAVEPOINT sp1;

UPDATE Accounts SET Balance = Balance + 100 
WHERE AccountID = 'B';

-- If error occurs here:
ROLLBACK TO sp1;  -- Undo only the last update

-- Continue with transaction or commit

Transaction Examples

Example 1: Simple Transfer

Money Transfer

BEGIN TRANSACTION;

-- Step 1: Debit from source account
UPDATE Accounts 
SET Balance = Balance - 500 
WHERE AccountID = 'ACC001';

-- Step 2: Credit to destination account
UPDATE Accounts 
SET Balance = Balance + 500 
WHERE AccountID = 'ACC002';

-- Verify both succeeded
IF @@ERROR = 0
    COMMIT;  -- Success: make changes permanent
ELSE
    ROLLBACK;  -- Error: undo all changes

Example 2: Order Processing

Complete Order

BEGIN TRANSACTION;

-- Create order
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (1001, 501, CURRENT_DATE, 299.99);

-- Reduce inventory
UPDATE Products 
SET StockQuantity = StockQuantity - 1 
WHERE ProductID = 201;

-- Record payment
INSERT INTO Payments (PaymentID, OrderID, Amount)
VALUES (301, 1001, 299.99);

-- All must succeed together
COMMIT;

Transaction Control in SQL

Different databases have different default behaviors:

Auto-commit mode: Each SQL statement is automatically a transaction (default in many databases)
Explicit transactions: Must use BEGIN/COMMIT/ROLLBACK

Nested Transactions

Some databases support nested transactions (transactions within transactions). Inner transactions can be committed independently, but outer transaction still controls overall commit/rollback.

Distributed Transactions

Transactions that span multiple databases or systems. Require special coordination to maintain ACID properties across systems.

Transaction Best Practices

Keep transactions short: Reduce lock time
Commit frequently: Don't hold locks unnecessarily
Handle errors: Always use try-catch and rollback on error
Avoid long-running transactions: Can cause blocking
Test failure scenarios: Ensure rollback works correctly
Use appropriate isolation levels: Balance consistency vs performance

Common Mistakes

Forgetting to commit: Changes not saved
Long transactions: Blocking other users
Not handling errors: Leaving database in inconsistent state
Nested transaction confusion: Not understanding commit behavior

Next Steps

Learn about managing concurrent transactions with Concurrency Control, or explore improving speed with Performance Tuning.

← Indexes Concurrency →