Transaction Management: setAutoCommit(false), commit(), and rollback()

Advanced16 min read•Updated: 2025-01-15

💳 Transaction Management: setAutoCommit(false), commit(), and rollback()

Introduction

By default, JDBC operates in auto-commit mode, meaning every individual SQL statement is treated as an independent transaction and committed to disk immediately.

In enterprise operations involving multi-step modifications (such as financial bank transfers where money is deducted from Account A and credited to Account B), auto-commit is disastrous: if a failure occurs halfway through, the system is left in a corrupted state.

🏛️ The ACID Guarantee in JDBC

Atomicity: All operations succeed together or all are rolled back.
Consistency: The database transitions from one valid state to another.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Committed transactions persist permanently across crashes.

💻 Transactional Balance Transfer Example

1package com.mskinstitute.banking;
2 
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.SQLException;
6 
7public class BankingService {
8 
9    public static void transferFunds(Connection conn, int senderId, int receiverId, double amount) 
10            throws SQLException {
11        
12        String withdrawSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
13        String depositSql  = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
14 
15        // STEP 1: Disable Auto-Commit to begin manual transaction boundary
16        conn.setAutoCommit(false);
17 
18        try (PreparedStatement withdrawStmt = conn.prepareStatement(withdrawSql);
19             PreparedStatement depositStmt  = conn.prepareStatement(depositSql)) {
20 
21            // Step 2: Debit sender account
22            withdrawStmt.setDouble(1, amount);
23            withdrawStmt.setInt(2, senderId);
24            withdrawStmt.setDouble(3, amount);
25            int rowsDebited = withdrawStmt.executeUpdate();
26 
27            if (rowsDebited == 0) {
28                throw new SQLException("Insufficient funds or sender account not found!");
29            }
30 
31            // Step 3: Credit receiver account
32            depositStmt.setDouble(1, amount);
33            depositStmt.setInt(2, receiverId);
34            int rowsCredited = depositStmt.executeUpdate();
35 
36            if (rowsCredited == 0) {
37                throw new SQLException("Receiver account not found!");
38            }
39 
40            // STEP 4: Both operations succeeded - Commit permanently!
41            conn.commit();
42            System.out.println("✅ Transaction successful: ₹" + amount + " transferred.");
43 
44        } catch (Exception ex) {
45            // STEP 5: Failure occurred! Rollback entire transaction to pristine state
46            System.err.println("❌ Error detected, rolling back changes: " + ex.getMessage());
47            conn.rollback();
48            throw ex;
49        } finally {
50            // STEP 6: Always restore default auto-commit mode
51            conn.setAutoCommit(true);
52        }
53    }
54}

Next Lesson

Connection Pooling Concepts with HikariCP

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
ResultSet Navigation and Mapping Rows to Java Domain Models	Connection Pooling Concepts with HikariCP

Practice Quiz

Test your understanding of this lesson with 1 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Introduction

By default, JDBC operates in auto-commit mode, meaning every individual SQL statement is treated as an independent transaction and committed to disk immediately.

💻 Transactional Balance Transfer Example

1package com.mskinstitute.banking;
2 
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.SQLException;
6 
7public class BankingService {
8 
9    public static void transferFunds(Connection conn, int senderId, int receiverId, double amount) 
10            throws SQLException {
11        
12        String withdrawSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
13        String depositSql  = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
14 
15        // STEP 1: Disable Auto-Commit to begin manual transaction boundary
16        conn.setAutoCommit(false);
17 
18        try (PreparedStatement withdrawStmt = conn.prepareStatement(withdrawSql);
19             PreparedStatement depositStmt  = conn.prepareStatement(depositSql)) {
20 
21            // Step 2: Debit sender account
22            withdrawStmt.setDouble(1, amount);
23            withdrawStmt.setInt(2, senderId);
24            withdrawStmt.setDouble(3, amount);
25            int rowsDebited = withdrawStmt.executeUpdate();
26 
27            if (rowsDebited == 0) {
28                throw new SQLException("Insufficient funds or sender account not found!");
29            }
30 
31            // Step 3: Credit receiver account
32            depositStmt.setDouble(1, amount);
33            depositStmt.setInt(2, receiverId);
34            int rowsCredited = depositStmt.executeUpdate();
35 
36            if (rowsCredited == 0) {
37                throw new SQLException("Receiver account not found!");
38            }
39 
40            // STEP 4: Both operations succeeded - Commit permanently!
41            conn.commit();
42            System.out.println("✅ Transaction successful: ₹" + amount + " transferred.");
43 
44        } catch (Exception ex) {
45            // STEP 5: Failure occurred! Rollback entire transaction to pristine state
46            System.err.println("❌ Error detected, rolling back changes: " + ex.getMessage());
47            conn.rollback();
48            throw ex;
49        } finally {
50            // STEP 6: Always restore default auto-commit mode
51            conn.setAutoCommit(true);
52        }
53    }
54}

Previous Lesson

Next Lesson

ResultSet Navigation and Mapping Rows to Java Domain Models