SQL Transactions, COMMIT/ROLLBACK, and Autocommit Mode

• SQL transaction – a unit of work bounded by BEGIN/START TRANSACTION and ending with COMMIT (make permanent) or ROLLBACK (discard everything).
• Autocommit mode – the default on a fresh connection in MySQL and PostgreSQL where each statement is automatically wrapped in its own implicit transaction and committed the instant it finishes.
• Partial completion danger – under autocommit, the first statement commits permanently; if the second fails, there is no automatic undo of the first.
• Fix – explicitly begin a transaction so both statements share one atomic boundary.
• JDBC – connection.setAutoCommit(false) + connection.commit() or connection.rollback().
• Spring – @Transactional annotation starts a transaction around the method.

What is a SQL transaction?

A transaction has a clear lifecycle. You start it with BEGIN or START TRANSACTION, run your DML statements – inserts, updates, deletes – and then end it with COMMIT to persist all the changes atomically, or ROLLBACK to undo every change made since the start.

BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
COMMIT;  -- both changes are now permanent

Between BEGIN and COMMIT, the changes are visible only inside your own session, protected by the database's isolation rules.

What is autocommit mode?

Autocommit is the mode where there is no explicit transaction wrapping. Each statement you send is treated as its own transaction: the engine implicitly begins, runs the statement, and commits it immediately.

-- Autocommit ON (default)
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
-- This is immediately committed. No way to undo.

UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
-- This is also immediately committed.

In MySQL and PostgreSQL, autocommit is the default for a fresh connection. It is convenient for one-off statements but dangerous for multi-step logic.

Why is autocommit dangerous for multi-step work?

The danger is partial completion. Consider debit then credit as two separate autocommitted statements.

Step 1: UPDATE debit  → COMMITTED (permanent)
Step 2: UPDATE credit  → FAILS (constraint, crash, deadlock)

Result: Rajesh's money is gone. Priya never received it.
The database is internally consistent per statement but logically broken.

There is no automatic undo of the already-committed debit.

How do you fix it with an explicit transaction?

Explicitly begin a transaction so both statements share one atomic boundary. A failure in the credit lets you ROLLBACK, which also undoes the debit.

BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
-- If anything fails here:
ROLLBACK;  -- both updates are undone
-- If everything succeeds:
COMMIT;    -- both updates are permanent

How do you manage transactions in JDBC?

In JDBC, you turn off autocommit, execute your statements, and then commit or rollback.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class TransferService {

    private final Connection connection;

    public TransferService(Connection connection) {
        this.connection = connection;
    }

    public void transfer(String from, String to, BigDecimal amount) throws SQLException {
        connection.setAutoCommit(false);
        try {
            try (PreparedStatement debit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE account_id = ?")) {
                debit.setBigDecimal(1, amount);
                debit.setString(2, from);
                debit.executeUpdate();
            }
            try (PreparedStatement credit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE account_id = ?")) {
                credit.setBigDecimal(1, amount);
                credit.setString(2, to);
                credit.executeUpdate();
            }
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }
}

How do you manage transactions in Spring?

The @Transactional annotation starts a transaction around the method and commits on normal return or rolls back on a runtime exception.

package com.bank.service;

import java.math.BigDecimal;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class TransferService {

    private final AccountRepository accountRepository;

    public TransferService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    @Transactional
    public void transfer(String from, String to, BigDecimal amount) {
        accountRepository.debit(from, amount);
        accountRepository.credit(to, amount);
        // If credit throws RuntimeException, debit is rolled back automatically
    }
}

Why should you keep transactions short?

A transaction holds locks from BEGIN until COMMIT. Long transactions block other sessions and inflate contention. You begin as late as possible, do the minimum work, and commit promptly.

Wrong: BEGIN → read user input → wait 30 seconds → UPDATE → COMMIT
       (locks held for 30 seconds, blocking other transactions)

Right: read user input → validate → BEGIN → UPDATE → COMMIT
       (locks held for milliseconds)

Never let a transaction span a user think-time or an external network call that might hang.

What are the differences between databases?

| Feature | PostgreSQL | MySQL | Oracle |
|---------|-----------|-------|--------|
| Default mode | Autocommit ON | Autocommit ON | Autocommit OFF |
| Begin syntax | BEGIN | START TRANSACTION | Implicit on first DML |
| DDL in transaction | Yes (can rollback CREATE TABLE) | No (DDL auto-commits) | No |

Oracle's implicit transaction model means every DML starts a transaction automatically, which is closer to what you want for multi-step work.

What are common mistakes?

The most common mistake is forgetting to turn off autocommit in JDBC code.

// Wrong: autocommit is ON, each statement commits independently
connection.prepareStatement("UPDATE accounts SET balance = balance - 50000 WHERE account_id = ?").executeUpdate();
// Already committed!
connection.prepareStatement("UPDATE accounts SET balance = balance + 50000 WHERE account_id = ?").executeUpdate();
// If this fails, the first update is permanent and money is lost.

// Correct: turn off autocommit first
connection.setAutoCommit(false);
// ... both updates ...
connection.commit();

Another mistake is catching exceptions without rolling back, leaving the connection in a half-committed state.

What are common interview follow-up questions?

• "What happens if you forget to COMMIT?" The transaction stays open, holding locks. Eventually the connection times out or is closed, and the database rolls back.
• "Can DDL be rolled back?" In PostgreSQL yes. In MySQL and Oracle, DDL implicitly commits the current transaction.
• "What is the difference between ROLLBACK and just closing the connection?" Both undo uncommitted changes, but explicit ROLLBACK is cleaner and does not lose the connection.