Transactions 💳 — Keeping Database Operations Consistent

A transaction is a group of database operations treated as a single unit of work. Either all required operations succeed, or the transaction is rolled back.

sequenceDiagram
    participant A as Application
    participant DB as Database

    A->>DB: BEGIN TRANSACTION
    A->>DB: Operation 1
    A->>DB: Operation 2
    A->>DB: COMMIT
    DB-->>A: Success

1. Why Do We Need Transactions?

Consider transferring ₹1,000 from Account A to Account B. Two operations are required:

1. Debit ₹1,000 from Account A.
2. Credit ₹1,000 to Account B.

These operations should not leave the database in a partially completed state.

flowchart LR
    T["Transaction"] --> D["Debit Account A"]
    T --> C["Credit Account B"]

    D --> S["Commit"]
    C --> S

    D -. "Failure" .-> R["Rollback"]
    C -. "Failure" .-> R

2. Commit

COMMIT permanently applies the changes made by the transaction.

BEGIN TRANSACTION

UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountId = 1;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountId = 2;

COMMIT;

3. Rollback

If an operation fails, the transaction can be rolled back so the changes made within the transaction are undone.

sequenceDiagram
    participant A as Application
    participant DB as Database

    A->>DB: BEGIN TRANSACTION
    A->>DB: Debit Account A
    A->>DB: Credit Account B
    DB-->>A: Error

    A->>DB: ROLLBACK
    DB-->>A: Changes Reverted

4. Transaction Lifecycle

stateDiagram-v2
    [*] --> Active
    Active --> PartiallyCommitted: Operations Complete
    Active --> Failed: Error
    PartiallyCommitted --> Committed: COMMIT
    Failed --> Aborted: ROLLBACK
    Committed --> [*]
    Aborted --> [*]

5. Transaction Example — Order Creation

Creating an order may involve multiple database changes.

flowchart LR
    T["Transaction"] --> O["Create Order"]
    T --> I["Reduce Inventory"]
    T --> P["Create Payment Record"]

    O --> C["Commit"]
    I --> C
    P --> C

    O -. "Failure" .-> R["Rollback"]
    I -. "Failure" .-> R
    P -. "Failure" .-> R

Whether all of these operations should belong to one transaction depends on the system's consistency requirements and architecture.

6. Local vs Distributed Transactions

In microservices, services often own separate databases, so a single database transaction cannot automatically cover all services.

flowchart LR
    O["Order Service"] --> DB1["Order DB"]
    P["Payment Service"] --> DB2["Payment DB"]

    O -. "Separate Transaction" .-> DB1
    P -. "Separate Transaction" .-> DB2

Distributed workflows may instead use patterns such as Saga and compensating actions, depending on the business requirements.

7. Transactions and Concurrency

When multiple users modify the same data concurrently, transaction isolation and locking determine how those operations interact.

flowchart LR
    A["Transaction A"] --> DB["Database"]
    B["Transaction B"] --> DB

    DB --> I["Isolation / Concurrency Control"]

This leads directly to concepts such as isolation levels, locks, and deadlocks.

Important Interview Questions

• What is a database transaction?
• Why do we need transactions?
• What is COMMIT?
• What is ROLLBACK?
• What happens when one operation inside a transaction fails?
• Local transaction vs distributed transaction?
• How do transactions work in microservices?
• What is a Saga?
• How do transactions handle concurrent operations?

Key Takeaways

• A transaction groups related database operations into one unit of work.
• COMMIT makes the transaction's changes permanent.
• ROLLBACK reverses changes made by the transaction.
• Transactions help maintain consistent database state.
• Microservices with separate databases require different approaches for cross-service workflows.
• Transaction isolation controls how concurrent transactions interact.

Conclusion

For interviews, remember: BEGIN → Operations → COMMIT, or Failure → ROLLBACK.