Transactions

1. What is a Transaction?

A transaction is a group of one or more database operations that are treated as a single unit — either all of them succeed together, or none of them are applied at all, keeping the data consistent.

2. Why is it used?

Some operations depend on each other — like transferring money, which involves both deducting from one account and adding to another. A transaction ensures that if one part fails, the other part doesn't get applied either, avoiding inconsistent data.

3. Real-Life Example

Think of transferring money between two bank accounts. If money is deducted from one account but the system crashes before adding it to the other, the money would simply vanish. Transactions prevent this by ensuring both steps happen together, or neither does.

4. Syntax

java
conn.setAutoCommit(false);
try {
    // multiple related operations
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
}

5. Example Program

java
import java.sql.*;

public class TransactionDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/bankdb", "root", "password")) {

            conn.setAutoCommit(false);

            PreparedStatement debit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - 500 WHERE id = 1");
            PreparedStatement credit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + 500 WHERE id = 2");

            debit.executeUpdate();
            credit.executeUpdate();

            conn.commit();
            System.out.println("Transaction completed successfully.");

        } catch (SQLException e) {
            System.out.println("Transaction failed, changes rolled back.");
        }
    }
}

Output:

Transaction completed successfully.

6. Key Points to Remember

• setAutoCommit(false) must be set first to manually control when changes are actually saved.
• commit() permanently saves all changes made during the transaction; rollback() undoes them if something goes wrong.
• Transactions are essential for keeping data consistent whenever multiple related database changes must succeed or fail together.