Backend interview prep

Start

MySQL Transactions Interview Questions

Master MySQL Transactions with interview-focused questions covering ACID properties, COMMIT, ROLLBACK, SAVEPOINT, Auto Commit, Isolation Levels, MVCC, Read Views, Deadlocks, Spring Boot transactions, and production best practices.

Introduction

Transactions are one of the most important features of relational databases.

A transaction ensures that multiple SQL statements execute as a single logical unit.

Either

  • All operations succeed

OR

  • Everything rolls back

Transactions are heavily used in

  • Banking
  • Payment Systems
  • Inventory
  • Order Processing
  • Airline Booking
  • Healthcare Applications

In MySQL, transactions are supported by the InnoDB Storage Engine.

MySQL Transaction Architecture

flowchart LR

Application --> JDBC --> MySQL --> Transaction

Transaction --> SQL1

Transaction --> SQL2

Transaction --> SQL3

SQL3 --> CommitOrRollback

1. What is a Transaction?

Answer

A Transaction is a group of SQL statements executed as one logical unit.

Either

All Statements Commit

or

Everything Rolls Back

2. Why are Transactions needed?

Transactions ensure

  • Data Integrity
  • Data Consistency
  • Reliability
  • Atomic Updates

Without transactions

↓

Partial updates

↓

Corrupted data.

3. What are ACID Properties?

ACID stands for

  • Atomicity
  • Consistency
  • Isolation
  • Durability

ACID Flow

flowchart LR

Start --> Operations --> Commit

Commit --> Success

Commit --> Rollback

4. What is Atomicity?

Atomicity means

Everything

or

Nothing.

Example

Money Transfer

Debit succeeds

Credit fails

↓

Entire transaction rolls back.

5. What is Consistency?

Database always moves

from

one valid state

to

another valid state.

Constraints remain valid.

6. What is Isolation?

Transactions should not interfere with one another.

Each transaction behaves as if it is executing alone.

7. What is Durability?

Once committed

↓

Data survives

  • Server Restart
  • Power Failure
  • Crash

using Redo Logs.

8. Which Storage Engine supports Transactions?

InnoDB

supports transactions.

MyISAM

does not.

9. How do you start a transaction?

START TRANSACTION;

or

BEGIN;

10. How do you commit a transaction?

COMMIT;

Changes become permanent.

11. How do you rollback a transaction?

ROLLBACK;

All uncommitted changes are discarded.

Transaction Lifecycle

flowchart LR

Begin --> ExecuteSQL --> Commit

Commit --> Success

Commit --> Rollback

12. What is Auto Commit?

By default

MySQL commits

every SQL statement automatically.

Check

SELECT @@autocommit;

Disable

SET autocommit=0;

13. What is SAVEPOINT?

Creates checkpoints inside a transaction.

SAVEPOINT sp1;

14. Why use SAVEPOINT?

Rollback to a specific point

instead of

rolling back the entire transaction.

SAVEPOINT

flowchart LR

Begin --> Savepoint1 --> Savepoint2 --> Commit

15. How do you rollback to SAVEPOINT?

ROLLBACK TO sp1;

16. What are Transaction Isolation Levels?

MySQL supports

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Isolation Levels

flowchart TD

READUNCOMMITTED --> READCOMMITTED --> REPEATABLEREAD --> SERIALIZABLE

17. What is READ UNCOMMITTED?

Lowest isolation.

Allows

Dirty Reads.

Fastest

but least consistent.

18. What is READ COMMITTED?

Reads only committed data.

Prevents

Dirty Reads.

19. What is REPEATABLE READ?

Default isolation level in MySQL.

Prevents

  • Dirty Reads
  • Non-repeatable Reads

Uses MVCC.

20. What is SERIALIZABLE?

Highest isolation.

Transactions execute

one after another.

Highest consistency

↓

Lowest concurrency.

21. What is a Dirty Read?

Reading

uncommitted data.

Example

Transaction A

updates salary

↓

Transaction B reads it

↓

Transaction A rolls back.

Transaction B saw invalid data.

22. What is a Non-repeatable Read?

Reading

same row twice

↓

Different values.

Another transaction committed changes.

23. What is a Phantom Read?

Running

same query twice

↓

New rows appear.

Example

Salary > 50000

returns

10 rows

↓

Another transaction inserts row

↓

Second execution

11 rows.

24. What is MVCC?

MVCC

Multi-Version Concurrency Control

Allows

Readers

and

Writers

to work simultaneously.

MVCC

flowchart LR

Reader --> OldVersion

Writer --> NewVersion

25. What is a Read View?

Read View provides

a consistent snapshot

of committed data.

Used by

REPEATABLE READ.

26. What are Undo Logs?

Undo Logs store

old row versions.

Used for

  • Rollback
  • MVCC

27. What are Redo Logs?

Redo Logs record

committed changes.

Used during crash recovery.

28. Banking Example

Transfer

Debit Account

↓

Credit Account

↓

COMMIT

If credit fails

↓

ROLLBACK

Banking Transaction

flowchart LR

Debit --> Credit --> Commit

Commit --> Success

Commit --> Rollback

29. E-Commerce Example

Customer Order

↓

Create Order

↓

Reduce Stock

↓

Create Invoice

↓

COMMIT

30. HR Example

Employee Promotion

↓

Update Salary

↓

Update Department

↓

Update Audit Log

↓

Commit

31. Spring Boot Example

@Transactional

public void transfer(){

    accountRepository.debit();

    accountRepository.credit();

}

Spring automatically commits

or rolls back.

32. Common Transaction Problems

  • Long-running Transactions
  • Deadlocks
  • Lock Contention
  • Missing Indexes
  • Large Batch Updates

33. Enterprise Best Practices

  • Keep transactions short.
  • Commit immediately after work completes.
  • Avoid user interaction inside transactions.
  • Use proper indexes.
  • Choose the correct isolation level.
  • Handle deadlocks gracefully.
  • Retry transient failures.
  • Monitor long-running transactions.
  • Avoid unnecessary locks.
  • Test rollback scenarios.

Transaction Workflow

flowchart LR

Begin --> ExecuteStatements --> Commit

Commit --> Success

Commit --> Rollback

Quick Revision

Topic Key Point
Transaction Logical Unit of Work
BEGIN Start Transaction
COMMIT Save Changes
ROLLBACK Undo Changes
SAVEPOINT Partial Rollback
Auto Commit Enabled by Default
MVCC Multi-Version Reads
Undo Log Rollback & MVCC
Redo Log Crash Recovery
Default Isolation REPEATABLE READ

Interview Tips

Interviewers frequently ask

  • What is a transaction?
  • Explain ACID properties.
  • Difference between COMMIT and ROLLBACK.
  • What is SAVEPOINT?
  • Explain Auto Commit.
  • What are isolation levels?
  • Explain Dirty Read.
  • Explain Phantom Read.
  • What is MVCC?
  • Give a banking transaction example.

Always explain that MySQL transactions are supported by the InnoDB storage engine and rely on MVCC, Undo Logs, Redo Logs, and isolation levels to provide reliable, concurrent, and crash-safe data processing.

Summary

MySQL transactions ensure reliable execution of multiple SQL statements as a single unit while maintaining ACID guarantees. Features such as COMMIT, ROLLBACK, SAVEPOINT, MVCC, Undo Logs, Redo Logs, and configurable Isolation Levels allow MySQL to support high-concurrency enterprise applications.

Understanding transaction lifecycles, consistency models, rollback mechanisms, MVCC, and Spring Boot transaction management is essential for Backend Developers, Database Engineers, and Solution Architects working with MySQL.