Transactions, ACID and TCL

#Introduction

One of the most important topics for a Java backend developer. In 5+ years Java/Spring Boot interviews, expect questions like: What is a transaction? What does @Transactional do? What happens if an exception occurs? COMMIT vs ROLLBACK? What is ACID? What is auto-commit?

This chapter lays the foundation; isolation levels, locks, MVCC and deadlocks build on it.

#What is a Transaction?

A transaction is a group of SQL statements that execute as one logical unit of work. Either everything succeeds or everything fails — there is no partial success.

Real Example

Transferring 5,000:

If the debit succeeds but the credit fails, money is lost. A transaction prevents this.

#Why Transactions Matter

A user books a ticket in a ticket booking application:

If payment fails but the seat remains reserved, the database becomes inconsistent. Instead: everything succeeds, or everything rolls back.

#ACID Properties

Every interview asks this.

A — Atomicity

All or nothing. If the debit succeeds and the credit fails, everything rolls back — nothing is permanently changed.

C — Consistency

The database must remain valid — rules and constraints are preserved.

I — Isolation

Two users book the last ticket simultaneously. Without isolation, both get "Booked" — one seat sold twice. Isolation ensures concurrent transactions don't interfere in undesirable ways.

D — Durability

Once committed, data survives crashes: COMMIT → power failure → restart database → the data is still there.

#COMMIT

START TRANSACTION;

UPDATE employees
SET salary = salary + 5000
WHERE id = 1;

COMMIT;   -- the change is now permanent

#ROLLBACK

START TRANSACTION;

UPDATE employees
SET salary = 100000
WHERE id = 1;

-- Oops... wrong employee. Undo everything:
ROLLBACK;

The original salary is restored.

#SAVEPOINT

Sometimes you don't want to roll back the entire transaction:

START TRANSACTION;

INSERT INTO users (...);
SAVEPOINT user_created;

INSERT INTO orders (...);
-- Oops... wrong order. Roll back only to the savepoint:
ROLLBACK TO user_created;

COMMIT;

The user remains; the order is undone.

#Auto-Commit

By default, MySQL runs in auto-commit mode — each statement is committed immediately after it executes:

UPDATE employees
SET salary = 60000
WHERE id = 2;      -- automatically committed

Disable it, and you control when changes are committed:

SET autocommit = 0;
-- multiple queries...
COMMIT;

SET autocommit = 1;   -- enable again

(START TRANSACTION also suspends auto-commit for just that one transaction.)

#SQL Command Categories

#Spring Boot and @Transactional

One of the most common interview topics.

public void transferMoney() {
    debit();
    credit();
}

If the debit succeeds and the credit fails, without a transaction money is lost. Using Spring:

@Transactional
public void transferMoney() {
    debit();
    credit();
}

How It Works

Spring starts and ends the database transaction for you.

#Real Production Example — Ticket Booking

This prevents inconsistent data.

#Hands-On Assignment

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    balance DECIMAL(10,2)
);

1. Start a transaction.
2. Transfer 2,000 from Rahul to Amit.
3. Commit the transaction.
4. Repeat the transfer but roll it back.
5. Create a savepoint after debiting Rahul.
6. Credit Amit.
7. Roll back only to the savepoint.
8. Commit the remaining changes.
9. Observe how the balances change after each step.

Challenge Problems

1. Simulate an ATM withdrawal using transactions.
2. Simulate an online shopping checkout with order, payment and inventory update.
3. Demonstrate the difference between auto-commit enabled and disabled.
4. Show how a failure after one SQL statement can be safely handled with ROLLBACK.
5. Explain how @Transactional helps in a Spring Boot service method that updates multiple tables.