Transactions

Some operations only make sense as a group. Think of transferring money between two bank accounts: you debit one account and credit another. If only the debit happens and the credit fails, money simply vanishes. A transaction is how JDBC lets you say "run these statements as one all-or-nothing unit."

What Is a Transaction?

A transaction is a group of one or more database operations that must either all succeed together or all fail together — there is no in-between state where only some of them took effect. The classic example is a bank transfer:

If step 1 succeeds but step 2 fails (say, the connection drops), the bank has just deleted money out of thin air. Wrapping both steps in a single transaction guarantees that either both changes are saved, or neither is.

Auto-Commit Is On by Default

Every JDBC Connection starts in auto-commit mode — conn.setAutoCommit(true) is the default. In this mode, every single statement you run is treated as its own tiny transaction and committed to the database immediately after it executes. That's convenient for one-off statements, but it means two related statements (like the two halves of a transfer) are not linked — one can succeed while the other fails.

// This is the default - you don't need to call this explicitly
conn.setAutoCommit(true);

Turning Off Auto-Commit for Manual Control

To group statements into one real transaction, turn auto-commit off first. From that point on, nothing is saved to the database until you explicitly call conn.commit().

conn.setAutoCommit(false); // I'll decide when to save

Full Example: A Bank Transfer

Here is the full pattern: turn off auto-commit, run both halves of the transfer, commit if both succeeded, and roll back if anything threw an exception along the way.

import java.sql.*;

public class TransferMoney {
  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/coding_with_sonu", "root", "");
      conn.setAutoCommit(false); // start manual transaction

      PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
      debit.setDouble(1, 500.00);
      debit.setInt(2, 101); // Account A
      debit.executeUpdate();

      PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
      credit.setDouble(1, 500.00);
      credit.setInt(2, 102); // Account B
      credit.executeUpdate();

      conn.commit(); // both succeeded - save for real
      System.out.println("Transfer complete.");

    } catch (SQLException e) {
      try {
        if (conn != null) conn.rollback(); // undo everything since last commit
        System.out.println("Transfer failed, rolled back: " + e.getMessage());
      } catch (SQLException rollbackEx) {
        rollbackEx.printStackTrace();
      }
    } finally {
      try {
        if (conn != null) {
          conn.setAutoCommit(true); // restore default before returning connection
          conn.close();
        }
      } catch (SQLException closeEx) {
        closeEx.printStackTrace();
      }
    }
  }
}