Database Transactions Basics Interview Questions

Master Database Transactions with interview-focused questions covering transaction lifecycle, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, Auto Commit, transaction states, internal working, WAL, production scenarios, and enterprise best practices.

Introduction

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

A transaction ensures that either

All operations succeed

None of them succeed.

Transactions are critical for maintaining data consistency in enterprise applications such as

Banking
E-Commerce
Healthcare
Airline Reservation
Stock Trading
Insurance

Every enterprise backend application relies on transactions.

Transaction Architecture

flowchart LR

Application --> BeginTransactionDatabaseOperations["Begin Transaction --> Database Operations --> Commit / Rollback --> Database"]

Banking Example

Transfer ₹1000

Account A

↓

Debit ₹1000

↓

Account B

↓

Credit ₹1000

↓

Commit

If any step fails,

the entire transaction is rolled back.

1. What is a Database Transaction?

Answer

A transaction is a logical unit of work consisting of one or more SQL statements that execute together.

It guarantees

Data Consistency
Reliability
Atomic Execution

Transaction Workflow

BEGIN

↓

SQL Statements

↓

COMMIT

or

ROLLBACK

2. Why are Transactions needed?

Transactions ensure

Data Integrity
Data Consistency
Recovery from Failures
Safe Concurrent Access

Without transactions,

partial updates could corrupt business data.

3. What is a Logical Unit of Work?

A logical unit of work is a set of operations that must succeed or fail together.

Example

Money Transfer

Debit

+

Credit

=

One Transaction

4. What happens without Transactions?

Suppose

Debit ₹1000

↓

Server Crash

↓

Credit Not Executed

Money disappears.

Transactions prevent this problem.

5. What are the basic Transaction commands?

Common commands

BEGIN
COMMIT
ROLLBACK
SAVEPOINT

Transaction Commands

BEGIN

↓

Operations

↓

COMMIT

or

ROLLBACK

6. What is BEGIN?

BEGIN starts a new transaction.

Example

BEGIN;

UPDATE Accounts
SET balance = balance - 1000
WHERE id = 1;

7. What is COMMIT?

COMMIT permanently saves all changes.

Example

COMMIT;

After COMMIT,

changes cannot be rolled back.

COMMIT Workflow

flowchart LR

Transaction --> Commit --> PermanentStorage["Permanent Storage"]

8. What is ROLLBACK?

ROLLBACK cancels all uncommitted changes.

Example

ROLLBACK;

Database returns to its previous consistent state.

ROLLBACK Workflow

flowchart LR

Transaction --> Error --> Rollback --> OriginalState["Original State"]

9. What is SAVEPOINT?

SAVEPOINT creates a checkpoint inside a transaction.

Instead of rolling back the entire transaction,

you can roll back to a savepoint.

SAVEPOINT Example

BEGIN;

UPDATE Accounts
SET balance = balance - 500
WHERE id = 1;

SAVEPOINT debit_done;

UPDATE Accounts
SET balance = balance + 500
WHERE id = 2;

ROLLBACK TO SAVEPOINT debit_done;

COMMIT;

SAVEPOINT Workflow

BEGIN

↓

Operation 1

↓

SAVEPOINT

↓

Operation 2

↓

Rollback to Savepoint

10. What is Auto Commit?

Auto Commit automatically commits every SQL statement.

Example

UPDATE Employee

↓

Automatically COMMIT

Many databases enable Auto Commit by default.

11. When should Auto Commit be disabled?

Disable Auto Commit for

Banking Transactions
Bulk Updates
Batch Processing
Financial Applications

12. What are Transaction States?

A transaction moves through several states.

Active
Partially Committed
Committed
Failed
Aborted

Transaction States

Active

↓

Partially Committed

↓

Committed

or

Failed

↓

Rollback

↓

Aborted

13. What is an Active Transaction?

The transaction has started

and SQL statements are executing.

14. What is a Partially Committed Transaction?

All SQL statements executed successfully,

but the transaction has not yet been permanently committed.

15. What is a Committed Transaction?

The transaction has completed successfully,

and all changes are permanently stored.

16. What is a Failed Transaction?

An error occurred during execution.

Examples

Constraint Violation
Deadlock
System Failure

17. What is an Aborted Transaction?

After a rollback,

the transaction enters the aborted state.

Database returns to the previous consistent state.

18. How does a Transaction work internally?

Typical workflow

BEGIN

↓

Execute SQL

↓

Write Changes

↓

Transaction Log (WAL/Redo Log)

↓

COMMIT

↓

Persist Data

Internal Transaction Flow

flowchart LR

BEGIN --> ExecuteSqlTransactionLog["Execute SQL --> Transaction Log --> Commit --> DataFiles["Data Files"]"]

19. What is a Transaction Log?

A Transaction Log records every database change.

Used for

Recovery
Rollback
Crash Recovery
Replication

Different databases use different implementations, such as Write-Ahead Logging (WAL) in PostgreSQL or Redo Logs in Oracle and MySQL.

20. Can Transactions contain multiple SQL statements?

Yes.

Example

BEGIN;

INSERT INTO Orders VALUES (...);

UPDATE Inventory
SET quantity = quantity - 1
WHERE product_id = 10;

INSERT INTO AuditLog VALUES (...);

COMMIT;

21. Banking Example

Money Transfer

Debit Account

↓

Credit Account

↓

Commit

22. E-Commerce Example

Order Placement

Create Order

↓

Reduce Inventory

↓

Create Payment

↓

Commit

23. Healthcare Example

Patient Registration

Patient

↓

Appointment

↓

Billing

↓

Commit

24. Airline Example

Ticket Booking

Reserve Seat

↓

Payment

↓

Issue Ticket

↓

Commit

25. Stock Trading Example

Buy Shares

↓

Update Portfolio

↓

Debit Balance

↓

Commit

26. Production Example

Salary Processing

Update Salary

↓

Tax Deduction

↓

PF Contribution

↓

Audit Entry

↓

Commit

27. Advantages of Transactions

Data Consistency
Reliability
Atomic Execution
Recovery Support
Concurrent Access
Business Integrity

28. Common Mistakes

Long-running transactions
Forgetting COMMIT
Excessive locking
Keeping transactions open during user interaction
Mixing unrelated business operations in one transaction

29. Performance Tips

Keep transactions short.
Commit as soon as possible.
Avoid user input inside transactions.
Batch database operations.
Use appropriate isolation levels.
Monitor transaction duration.
Avoid unnecessary locks.

30. What are the best practices?

Keep transactions as short as possible.
Commit immediately after successful processing.
Roll back on every failure.
Use SAVEPOINT for complex workflows.
Avoid long-running locks.
Disable Auto Commit only when needed.
Log transaction failures.
Monitor transaction performance.
Test rollback scenarios.
Document business transaction boundaries.

Transaction Lifecycle

flowchart LR

BEGIN --> ExecuteSqlSaveTo["Execute SQL --> Save to Transaction Log --> COMMIT --> PermanentStorage["Permanent Storage"]"]

Enterprise Best Practices

Design transactions around a single business operation.
Keep transaction scope as small as possible.
Use transactions only when consistency is required.
Commit quickly to reduce lock contention.
Roll back immediately on failures.
Monitor long-running transactions.
Use transaction logs for recovery and auditing.
Choose the appropriate isolation level for the workload.
Test failure and rollback scenarios regularly.
Continuously monitor transaction throughput in production.

Quick Revision

Topic	Key Point
Transaction	Logical Unit of Work
BEGIN	Start Transaction
COMMIT	Save Changes Permanently
ROLLBACK	Undo Changes
SAVEPOINT	Partial Rollback
Auto Commit	Automatic Commit
Active	Transaction Running
Committed	Successfully Completed
Failed	Error Occurred
Transaction Log	Recovery Information

Interview Tips

Interviewers frequently ask

What is a Transaction?
Why are Transactions required?
Explain BEGIN, COMMIT, and ROLLBACK.
What is SAVEPOINT?
What is Auto Commit?
Explain the Transaction Lifecycle.
What happens during COMMIT?
What happens during ROLLBACK?
Why should transactions be short?
Give a real-world transaction example.

A strong interview explanation is:

"A transaction is a logical unit of work that groups one or more SQL operations into a single atomic operation. It begins with BEGIN, executes one or more SQL statements, and ends with either COMMIT, which permanently saves the changes, or ROLLBACK, which restores the database to its previous consistent state. Transactions ensure business operations such as money transfers, order processing, and inventory updates either complete entirely or not at all, maintaining data consistency and reliability."

Summary

Database transactions are the foundation of reliable enterprise applications. They ensure that multiple SQL operations execute as a single logical unit while preserving data consistency and integrity. Understanding BEGIN, COMMIT, ROLLBACK, SAVEPOINT, Auto Commit, transaction states, transaction logs, and internal execution flow is essential for Backend Developers, Database Engineers, Java Developers, and Solution Architects.

Mastering transaction basics provides the foundation for advanced topics such as ACID Properties, Isolation Levels, Locking, Deadlocks, MVCC, and Distributed Transactions.