What is a Transaction in SQL?

A Transaction in SQL is a sequence of one or more database operations executed as a single logical unit of work.

In simple words:

A transaction ensures that multiple SQL operations are completed successfully together or completely rolled back if any failure occurs.

Why Transactions are Important

In real-world applications:

Multiple database operations happen together
Data consistency is critical
Failures must not corrupt data

Transactions help:

Maintain data integrity
Prevent partial updates
Ensure reliable database operations
Handle failures safely

Simple Real-Life Example

Think about:

Online money transfer

Suppose:

₹5000 transferred from Account A to Account B

Steps Involved

Deduct ₹5000 from Account A
Add ₹5000 to Account B

Problem Scenario

If system crashes after:

Deducting money from Account A

but before:

Adding money to Account B

then:

Data becomes inconsistent

Solution

Use:

Transaction

Transaction Internal Architecture

Start Transaction
        |
        v
Execute Multiple SQL Operations
        |
        v
All Successful?
   /          \
 Yes          No
  |             |
  v             v
COMMIT       ROLLBACK

What Happens in Transaction?

All operations succeed → COMMIT
Any operation fails → ROLLBACK

Transaction Syntax

START TRANSACTION;

SQL statements;

COMMIT;

Banking Transaction Example

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;

COMMIT;

What Happens Internally?

Database:

Executes both updates together
Saves changes only after COMMIT

If Error Occurs?

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;

-- Error occurs here

ROLLBACK;

Result

No money deducted
Database restored to previous state

Transaction Query Flow

START TRANSACTION
        |
        v
Execute Queries
        |
        v
Check for Errors
        |
   --------------
   |            |
Success       Failure
   |            |
   v            v
COMMIT      ROLLBACK

Main Transaction Commands

START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT

1. START TRANSACTION

Begins:

Transaction block

Example

START TRANSACTION;

2. COMMIT

Permanently saves:

All transaction changes

Example

COMMIT;

3. ROLLBACK

Reverts:

All uncommitted changes

Example

ROLLBACK;

4. SAVEPOINT

Creates:

Partial rollback point

Example

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1;

SAVEPOINT deduct_done;

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;

ROLLBACK TO deduct_done;

COMMIT;

What Happens?

Rollback occurs only after:

SAVEPOINT

ACID Properties of Transactions

Transactions follow:

ACID Properties

ACID Means

Atomicity
Consistency
Isolation
Durability

1. Atomicity

Atomicity means:

Either all operations succeed or none succeed.

Example

Money transfer:

Both debit and credit happen together

2. Consistency

Consistency means:

Database remains valid before and after transaction.

Example

Total balance remains correct after transfer.

3. Isolation

Isolation means:

Transactions do not interfere with each other.

Example

Two users updating same account simultaneously should not corrupt data.

4. Durability

Durability means:

Committed data remains permanent even after system crash.

Example

After COMMIT:

Data survives power failure

Transaction States

Active
Partially Committed
Committed
Failed
Aborted

Transaction State Flow

Active
   |
   v
Partially Committed
   |
   v
Committed

OR

Failed
   |
   v
Aborted

Transaction Isolation Levels

Read Uncommitted
Read Committed
Repeatable Read
Serializable

1. Read Uncommitted

Allows:

Dirty reads

2. Read Committed

Prevents:

Dirty reads

3. Repeatable Read

Ensures:

Same query returns same result during transaction

4. Serializable

Highest isolation level:

Maximum consistency

Common Transaction Problems

Dirty Read
Non-Repeatable Read
Phantom Read

1. Dirty Read

Reading:

Uncommitted data

2. Non-Repeatable Read

Same query returns:

Different results inside same transaction

3. Phantom Read

New rows appear:

During transaction execution

Real-Time Banking Example

Banking systems use transactions for:

Money transfers
Balance updates
ATM withdrawals

Why Transactions are Critical?

To prevent:

Financial inconsistency

Real-Time E-Commerce Example

E-commerce platforms use transactions for:

Order placement
Inventory updates
Payment processing

Example

When order placed:

Payment deducted
Stock reduced
Order created

All operations must succeed together.

Real-Time Learning Platform Example

Learning platforms use transactions for:

Course enrollment
Payment confirmation
Certificate generation

Transactions in Microservices

Microservices use transactions for:

Distributed operations
Data consistency
Saga patterns

Example

Payment Service + Order Service + Inventory Service

Advantages of Transactions

Maintains data integrity
Ensures consistency
Supports rollback recovery
Improves reliability

Disadvantages of Transactions

Locking overhead
Performance impact
Deadlock possibility

What is Deadlock?

Deadlock occurs when:

Two transactions wait for each other indefinitely

Performance Consideration

Long-running transactions may:

Block other transactions
Reduce concurrency

Best Practices

Keep transactions short
Commit quickly
Use proper isolation levels
Handle rollback carefully

Common Interview Mistake

Many developers think:

COMMIT happens automatically always

Reality

Explicit transaction management is important in enterprise systems.

Professional Interview Answer

A Transaction in SQL is a sequence of one or more database operations executed as a single logical unit of work. Transactions ensure data integrity and consistency by following ACID properties: Atomicity, Consistency, Isolation, and Durability. If all operations succeed, the transaction is committed permanently using COMMIT. If any operation fails, all changes are reverted using ROLLBACK. Transactions are widely used in banking systems, e-commerce applications, financial platforms, learning management systems, and microservices architectures to ensure reliable and consistent database operations.

Why Interviewers Like This Answer

Clearly explains ACID properties
Includes COMMIT and ROLLBACK concepts
Shows enterprise-level understanding
Provides real-world examples
Explains transaction internals and consistency

Frequently Asked Questions