Groovy SQL Batch Processing – withBatch Performance Guide with 12 Examples

Groovy SQL batch processing with withBatch for high-performance bulk inserts. 12+ examples covering batch size tuning, performance benchmarks, large dataset handling, and combining batches with transactions.

“A database without transactions is like a bank without locks – everything works fine until two people reach for the same cash at the same time.”

Jim Gray, Transaction Processing Pioneer

Last Updated: March 2026 | Tested on: Groovy 5.x, Java 17+ | Difficulty: Intermediate to Advanced | Reading Time: 25 minutes

Inserting one row at a time into a database is fine for small datasets. But when you need to load 10,000 rows, 100,000 rows, or a million rows, individual inserts become painfully slow. That is where groovy sql batch processing comes in. Groovy’s withBatch method groups multiple SQL statements into a single network round-trip, and the performance difference is dramatic – often 10x to 50x faster than row-by-row inserts.

Our Groovy SQL Transactions post covers the fundamentals of withTransaction, rollback, savepoints, and isolation levels. This post focuses specifically on batch performance: how withBatch works under the hood, how to tune batch sizes, how to combine batches with transactions for safe bulk loading, and how to handle errors mid-batch without losing everything. We cover 12 practical examples using H2, with benchmark numbers showing the real-world speed gains.

If you are comfortable with basic Groovy SQL connections and queries, you are ready. If not, start with our Groovy SQL Database Connection post first.

What Are SQL Transactions in Groovy?

A transaction is a group of SQL operations that either all succeed or all fail together. Think of it like an “all-or-nothing” deal. If you’re transferring money between two bank accounts, you don’t want the debit to succeed and the credit to fail – that would mean money vanished into thin air. Transactions prevent exactly that.

In Groovy, the groovy.sql.Sql class provides the withTransaction closure for managing transactions, and the withBatch closure for grouping multiple SQL statements into a single batch for performance. Both are built on top of standard JDBC, but Groovy makes them dramatically cleaner to use.

According to the official Groovy database documentation, the Sql class handles connection management, auto-commit toggling, and rollback behavior automatically when you use these closures.

Key Points:

  • Transactions follow ACID properties: Atomicity, Consistency, Isolation, Durability
  • withTransaction automatically disables auto-commit, commits on success, and rolls back on exception
  • withBatch groups statements and sends them to the database in a single round trip
  • Both features work with any JDBC-compatible database (H2, PostgreSQL, MySQL, Oracle, etc.)

Why Use Transactions and Batch Processing?

Without transactions, every SQL statement commits immediately. That’s fine for a single INSERT, but the moment you need two or more related operations to succeed together, you need transactions. Here’s what you get:

  • Data integrity: Either all changes apply or none do – no half-finished state
  • Error recovery: If something fails, the database rolls back to the state before the transaction started
  • Concurrency control: Isolation levels prevent dirty reads, phantom reads, and other multi-user problems
  • Performance: Batch processing can be 10-100x faster than individual inserts because it reduces network round trips

Unlike raw JDBC where you manually manage connection.setAutoCommit(false), connection.commit(), and connection.rollback() in try-catch-finally blocks, Groovy’s withTransaction handles all of that in a single, elegant closure.

When to Use Transactions vs Batch Processing

Use transactions when:

  • Multiple related operations must succeed or fail together (bank transfers, order processing)
  • You need to maintain referential integrity across tables
  • You’re implementing business logic that requires atomicity

Use batch processing when:

  • Inserting or updating hundreds or thousands of rows
  • Loading data from CSV files, APIs, or other bulk sources
  • Performance matters more than individual row-level error handling

Combine both when: You need to bulk-load data AND guarantee that the entire load either completes or rolls back – like importing a financial report where partial data would be worse than no data.

How Transactions Work Under the Hood

When you call sql.withTransaction { ... }, here’s what Groovy does behind the scenes:

  1. Saves the current auto-commit state of the connection
  2. Disables auto-commit (connection.setAutoCommit(false))
  3. Executes all SQL statements inside the closure
  4. If the closure completes without exception: commits all changes
  5. If the closure throws an exception: rolls back all changes
  6. Restores the original auto-commit state

For withBatch, the process is different. Groovy creates a JDBC Statement or PreparedStatement, collects your SQL operations using addBatch(), and then calls executeBatch() either when the batch size limit is reached or at the end of the closure. This dramatically reduces the number of database round trips.

Syntax and Basic Usage

Transaction Syntax

Here’s the basic syntax for withTransaction in Groovy:

Transaction Syntax

// Basic transaction
sql.withTransaction {
    sql.execute("INSERT INTO ...")
    sql.execute("UPDATE ...")
    // Auto-commits if no exception
    // Auto-rolls back if exception thrown
}

// Cacheconnection variant
sql.cacheConnection { conn ->
    // Manual control via the connection object
    conn.autoCommit = false
    // ... operations ...
    conn.commit()
}

Batch Syntax

Here’s the basic syntax for withBatch in Groovy:

Batch Syntax

// Batch with size limit
sql.withBatch(100) { stmt ->
    stmt.addBatch("INSERT INTO ...")
    stmt.addBatch("INSERT INTO ...")
    // Executes every 100 statements
}

// Batch with PreparedStatement
sql.withBatch(100, "INSERT INTO users (name, age) VALUES (?, ?)") { ps ->
    ps.addBatch(['Alice', 30])
    ps.addBatch(['Bob', 25])
}

Key Methods

MethodDescriptionAuto-Commit
withTransaction { }Wraps closure in a transaction with auto-commit/rollbackDisabled
withBatch(size) { }Groups statements into batches of given sizeUnchanged
withBatch(size, sql) { }Batch with PreparedStatement for same SQLUnchanged
commit()Manual commit (when managing transactions yourself)N/A
rollback()Manual rollbackN/A

Practical Examples

All examples below use the H2 in-memory database. You’ll need the H2 driver on your classpath. If you’re using Groovy’s @Grab, it’s a one-liner. Here we go.

Example 1: Basic withTransaction – Commit on Success

What we’re doing: Wrapping two inserts in a transaction so both commit together.

Example 1: Basic Transaction

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb1', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE accounts (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        balance DECIMAL(10,2)
    )
''')

// Both inserts happen inside one transaction
sql.withTransaction {
    sql.execute("INSERT INTO accounts VALUES (1, 'Alice', 1000.00)")
    sql.execute("INSERT INTO accounts VALUES (2, 'Bob', 500.00)")
}

// Verify both rows are committed
sql.eachRow("SELECT * FROM accounts ORDER BY id") { row ->
    println "${row.name}: \$${row.balance}"
}

sql.close()

Output

Alice: $1000.00
Bob: $500.00

What happened here: The withTransaction closure turned off auto-commit, ran both inserts, and then committed them together. If either insert had failed, neither row would exist in the table. This is the foundation of everything else we’ll build on.

Example 2: Automatic Rollback on Exception

What we’re doing: Demonstrating that when an exception is thrown inside a transaction, all changes are rolled back.

Example 2: Rollback on Exception

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb2', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE products (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        price DECIMAL(10,2)
    )
''')

// Insert a product outside the transaction (auto-commits)
sql.execute("INSERT INTO products VALUES (1, 'Widget', 9.99)")

try {
    sql.withTransaction {
        sql.execute("INSERT INTO products VALUES (2, 'Gadget', 19.99)")
        sql.execute("INSERT INTO products VALUES (3, 'Gizmo', 29.99)")

        // Simulate an error after the inserts
        throw new RuntimeException("Something went wrong!")

        // This line never executes
        sql.execute("INSERT INTO products VALUES (4, 'Doohickey', 39.99)")
    }
} catch (Exception e) {
    println "Caught: ${e.message}"
}

// Only the pre-transaction row survives
def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM products").cnt
println "Rows in table: ${count}"

sql.eachRow("SELECT * FROM products") { row ->
    println "${row.name}: \$${row.price}"
}

sql.close()

Output

Caught: Something went wrong!
Rows in table: 1
Widget: $9.99

What happened here: The Gadget and Gizmo inserts were rolled back because the exception caused withTransaction to call rollback(). Only Widget survived because it was inserted before the transaction started (with auto-commit on). This is the entire point of transactions – all or nothing.

Example 3: Savepoints for Partial Rollback

What we’re doing: Using JDBC savepoints inside a Groovy transaction to roll back only part of a transaction while keeping earlier changes.

Example 3: Savepoints

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb3', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE orders (
        id INT PRIMARY KEY,
        item VARCHAR(50),
        status VARCHAR(20)
    )
''')

sql.cacheConnection { conn ->
    conn.autoCommit = false

    try {
        // Phase 1: Insert confirmed orders
        sql.execute("INSERT INTO orders VALUES (1, 'Laptop', 'confirmed')")
        sql.execute("INSERT INTO orders VALUES (2, 'Mouse', 'confirmed')")

        // Create a savepoint after phase 1
        def savepoint = conn.setSavepoint('after_confirmed')

        // Phase 2: Insert tentative orders
        sql.execute("INSERT INTO orders VALUES (3, 'Keyboard', 'tentative')")
        sql.execute("INSERT INTO orders VALUES (4, 'Monitor', 'tentative')")

        // Oops - phase 2 had a problem, roll back to savepoint
        conn.rollback(savepoint)

        // Phase 1 inserts are still intact - commit them
        conn.commit()
    } catch (Exception e) {
        conn.rollback()
        println "Full rollback: ${e.message}"
    }
}

println "Orders after partial rollback:"
sql.eachRow("SELECT * FROM orders ORDER BY id") { row ->
    println "  #${row.id} ${row.item} (${row.status})"
}

sql.close()

Output

Orders after partial rollback:
  #1 Laptop (confirmed)
  #2 Mouse (confirmed)

What happened here: We used cacheConnection to get direct access to the JDBC Connection object. The savepoint after Phase 1 marked a “checkpoint.” When we rolled back to that savepoint, only the Phase 2 inserts were discarded. Phase 1’s Laptop and Mouse survived the commit. Savepoints are invaluable for complex workflows where you need fine-grained rollback control.

Example 4: Transaction Isolation Levels

What we’re doing: Setting the transaction isolation level to control how concurrent transactions see each other’s changes.

Example 4: Isolation Levels

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql
import java.sql.Connection

def sql = Sql.newInstance('jdbc:h2:mem:testdb4', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE settings (
        key VARCHAR(50) PRIMARY KEY,
        value VARCHAR(100)
    )
''')

sql.execute("INSERT INTO settings VALUES ('theme', 'dark')")

// Map isolation level constants to names
def levelNames = [
    (Connection.TRANSACTION_READ_UNCOMMITTED): 'READ_UNCOMMITTED',
    (Connection.TRANSACTION_READ_COMMITTED):   'READ_COMMITTED',
    (Connection.TRANSACTION_REPEATABLE_READ):  'REPEATABLE_READ',
    (Connection.TRANSACTION_SERIALIZABLE):     'SERIALIZABLE'
]

// Show current default isolation level
sql.cacheConnection { conn ->
    def defaultLevel = conn.transactionIsolation
    println "Default isolation: ${levelNames[defaultLevel]} (${defaultLevel})"
}

// Run a transaction with SERIALIZABLE isolation
sql.cacheConnection { conn ->
    def originalLevel = conn.transactionIsolation
    conn.transactionIsolation = Connection.TRANSACTION_SERIALIZABLE
    conn.autoCommit = false

    try {
        println "Using isolation: ${levelNames[conn.transactionIsolation]}"
        sql.execute("UPDATE settings SET value = 'light' WHERE key = 'theme'")
        conn.commit()
    } catch (Exception e) {
        conn.rollback()
    } finally {
        conn.transactionIsolation = originalLevel
        conn.autoCommit = true
    }
}

def result = sql.firstRow("SELECT value FROM settings WHERE key = 'theme'")
println "Theme is now: ${result.value}"

sql.close()

Output

Default isolation: READ_COMMITTED (2)
Using isolation: SERIALIZABLE
Theme is now: light

What happened here: We set the isolation level to SERIALIZABLE, which is the strictest level – it prevents dirty reads, non-repeatable reads, and phantom reads. The trade-off is lower concurrency. In production, READ_COMMITTED is the most common default and works well for most applications. Only use SERIALIZABLE when absolute consistency is critical.

Example 5: Real-World Bank Transfer with Transaction

What we’re doing: Implementing a classic bank transfer – debit one account, credit another – with proper validation and transaction safety.

Example 5: Bank Transfer

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb5', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE bank_accounts (
        id INT PRIMARY KEY,
        holder VARCHAR(50),
        balance DECIMAL(10,2)
    )
''')

sql.execute("INSERT INTO bank_accounts VALUES (1, 'Alice', 1000.00)")
sql.execute("INSERT INTO bank_accounts VALUES (2, 'Bob', 250.00)")

def transfer = { fromId, toId, amount ->
    sql.withTransaction {
        // Check sender's balance first
        def sender = sql.firstRow(
            "SELECT holder, balance FROM bank_accounts WHERE id = ?", [fromId]
        )

        if (sender.balance < amount) {
            throw new RuntimeException(
                "Insufficient funds: ${sender.holder} has \$${sender.balance}, needs \$${amount}"
            )
        }

        // Debit sender
        sql.executeUpdate(
            "UPDATE bank_accounts SET balance = balance - ? WHERE id = ?",
            [amount, fromId]
        )

        // Credit receiver
        sql.executeUpdate(
            "UPDATE bank_accounts SET balance = balance + ? WHERE id = ?",
            [amount, toId]
        )

        println "Transferred \$${amount} from account #${fromId} to #${toId}"
    }
}

// Successful transfer
transfer(1, 2, 300.00)

// Show balances
println "\nBalances after transfer:"
sql.eachRow("SELECT * FROM bank_accounts ORDER BY id") { row ->
    println "  ${row.holder}: \$${row.balance}"
}

// Attempt transfer with insufficient funds
println ""
try {
    transfer(2, 1, 1000.00)
} catch (Exception e) {
    println "Transfer failed: ${e.message}"
}

// Balances unchanged after failed transfer
println "\nBalances after failed transfer:"
sql.eachRow("SELECT * FROM bank_accounts ORDER BY id") { row ->
    println "  ${row.holder}: \$${row.balance}"
}

sql.close()

Output

Transferred $300.00 from account #1 to #2
Balances after transfer:
  Alice: $700.00
  Bob: $550.00

Transfer failed: Insufficient funds: Bob has $550.00, needs $1000.00

Balances after failed transfer:
  Alice: $700.00
  Bob: $550.00

What happened here: The first transfer succeeded – Alice’s debit and Bob’s credit were committed atomically. The second transfer failed the balance check and threw an exception, which caused withTransaction to roll back. No money was lost or created. This is the textbook example of why transactions exist.

Pro Tip: In production, you’d also want to lock the sender’s row using SELECT ... FOR UPDATE to prevent race conditions when two transfers happen simultaneously on the same account.

Example 6: Basic withBatch for Batch Inserts

What we’re doing: Using withBatch to insert multiple rows efficiently in a single batch.

Example 6: Basic Batch Insert

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb6', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        department VARCHAR(50)
    )
''')

// Batch insert using withBatch and a PreparedStatement
sql.withBatch(5, "INSERT INTO employees (name, department) VALUES (?, ?)") { ps ->
    ps.addBatch(['Alice', 'Engineering'])
    ps.addBatch(['Bob', 'Marketing'])
    ps.addBatch(['Charlie', 'Engineering'])
    ps.addBatch(['Diana', 'Sales'])
    ps.addBatch(['Eve', 'Marketing'])
    ps.addBatch(['Frank', 'Engineering'])
    ps.addBatch(['Grace', 'Sales'])
}

def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM employees").cnt
println "Inserted ${count} employees"

println "\nAll employees:"
sql.eachRow("SELECT * FROM employees ORDER BY id") { row ->
    println "  #${row.id} ${row.name} (${row.department})"
}

sql.close()

Output

Inserted 7 employees
All employees:
  #1 Alice (Engineering)
  #2 Bob (Marketing)
  #3 Charlie (Engineering)
  #4 Diana (Sales)
  #5 Eve (Marketing)
  #6 Frank (Engineering)
  #7 Grace (Sales)

What happened here: We used withBatch(5, sql) which creates a PreparedStatement and flushes every 5 rows. With 7 rows, it flushed once at row 5 and once when the closure ended (for the remaining 2). The prepared statement version is faster and safer than string concatenation because it uses parameterized queries.

Example 7: Batch Updates with Statement

What we’re doing: Using withBatch with raw SQL statements for mixed operations (inserts, updates, deletes) in one batch.

Example 7: Batch Updates

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb7', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE inventory (
        id INT PRIMARY KEY,
        product VARCHAR(50),
        quantity INT
    )
''')

// Seed data
sql.execute("INSERT INTO inventory VALUES (1, 'Apples', 100)")
sql.execute("INSERT INTO inventory VALUES (2, 'Bananas', 50)")
sql.execute("INSERT INTO inventory VALUES (3, 'Cherries', 200)")

println "Before batch update:"
sql.eachRow("SELECT * FROM inventory ORDER BY id") { row ->
    println "  ${row.product}: ${row.quantity}"
}

// Mixed batch: insert, update, and delete in one go
sql.withBatch(10) { stmt ->
    stmt.addBatch("INSERT INTO inventory VALUES (4, 'Dates', 75)")
    stmt.addBatch("UPDATE inventory SET quantity = 150 WHERE id = 1")
    stmt.addBatch("UPDATE inventory SET quantity = quantity + 25 WHERE id = 2")
    stmt.addBatch("DELETE FROM inventory WHERE id = 3")
}

println "\nAfter batch update:"
sql.eachRow("SELECT * FROM inventory ORDER BY id") { row ->
    println "  ${row.product}: ${row.quantity}"
}

sql.close()

Output

Before batch update:
  Apples: 100
  Bananas: 50
  Cherries: 200

After batch update:
  Apples: 150
  Bananas: 75
  Dates: 75

What happened here: Unlike the prepared statement version, the plain withBatch(size) accepts arbitrary SQL strings. This lets you mix inserts, updates, and deletes in a single batch. We added Dates, increased Apples to 150, bumped Bananas by 25, and removed Cherries – all in one round trip to the database.

Example 8: Batch Size Tuning and Performance

What we’re doing: Comparing different batch sizes to see how they affect insert performance.

Example 8: Batch Size Tuning

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def totalRows = 10000

[1, 10, 100, 500, 1000, 5000].each { batchSize ->
    def sql = Sql.newInstance("jdbc:h2:mem:perftest_${batchSize}", 'sa', '', 'org.h2.Driver')

    sql.execute('''
        CREATE TABLE perf_test (
            id INT PRIMARY KEY,
            name VARCHAR(50),
            value DECIMAL(10,2)
        )
    ''')

    def start = System.currentTimeMillis()

    sql.withBatch(batchSize, "INSERT INTO perf_test VALUES (?, ?, ?)") { ps ->
        (1..totalRows).each { i ->
            ps.addBatch([i, "item_${i}", i * 1.5])
        }
    }

    def elapsed = System.currentTimeMillis() - start
    def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM perf_test").cnt

    println "Batch size ${String.valueOf(batchSize).padLeft(5)}: " +
            "${elapsed}ms for ${count} rows " +
            "(${String.format('%.0f', count / (elapsed / 1000.0))} rows/sec)"

    sql.close()
}

Output

Batch size     1: 312ms for 10000 rows (32051 rows/sec)
Batch size    10: 98ms for 10000 rows (102040 rows/sec)
Batch size   100: 45ms for 10000 rows (222222 rows/sec)
Batch size   500: 38ms for 10000 rows (263157 rows/sec)
Batch size  1000: 36ms for 10000 rows (277777 rows/sec)
Batch size  5000: 35ms for 10000 rows (285714 rows/sec)

What happened here: The results are dramatic. Batch size 1 (essentially no batching) is the slowest. Going from 1 to 100 gives roughly a 7x speedup. After that, improvements taper off. For most applications, a batch size between 100 and 1000 hits the sweet spot. Going higher adds memory pressure without proportional speed gains. Note: exact numbers vary by machine and database, but the relative pattern is consistent.

Pro Tip: For remote databases (not in-memory), the difference is even more dramatic because each batch flush is a network round trip. A batch size of 100-500 is a great starting point for PostgreSQL or MySQL.

Example 9: Combining Transactions with Batch Processing

What we’re doing: Wrapping a batch insert inside a transaction so the entire bulk load is atomic – all rows insert or none do.

Example 9: Transaction + Batch

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb9', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE audit_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        event VARCHAR(100),
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

// Successful batch inside transaction
sql.withTransaction {
    sql.withBatch(50, "INSERT INTO audit_log (event) VALUES (?)") { ps ->
        (1..100).each { i ->
            ps.addBatch(["Event #${i}"])
        }
    }
}

def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM audit_log").cnt
println "After successful batch: ${count} rows"

// Failed batch inside transaction - everything rolls back
try {
    sql.withTransaction {
        sql.withBatch(50, "INSERT INTO audit_log (event) VALUES (?)") { ps ->
            (101..150).each { i ->
                ps.addBatch(["Event #${i}"])
            }
        }
        // Force a rollback after batch completes
        throw new RuntimeException("Aborting the import!")
    }
} catch (Exception e) {
    println "Caught: ${e.message}"
}

def countAfter = sql.firstRow("SELECT COUNT(*) AS cnt FROM audit_log").cnt
println "After failed batch: ${countAfter} rows (unchanged)"

sql.close()

Output

After successful batch: 100 rows
Caught: Aborting the import!
After failed batch: 100 rows (unchanged)

What happened here: The first batch of 100 events was wrapped in a transaction and committed successfully. The second batch of 50 events was also wrapped in a transaction, but we threw an exception after the batch completed – causing the entire transaction (including the already-flushed batch data) to roll back. The count stayed at 100, proving the transaction rollback covered the batch operations inside it.

Example 10: Error Handling in Transactions

What we’re doing: Handling different error scenarios in transactions – constraint violations, data validation, and cleanup.

Example 10: Error Handling

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql
import java.sql.SQLException

def sql = Sql.newInstance('jdbc:h2:mem:testdb10', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE users (
        id INT PRIMARY KEY,
        email VARCHAR(100) UNIQUE NOT NULL,
        name VARCHAR(50)
    )
''')

// Helper to safely run a transaction
def safeTransaction = { String description, Closure action ->
    try {
        sql.withTransaction(action)
        println "[OK] ${description}"
    } catch (SQLException e) {
        println "[SQL ERROR] ${description}: ${e.message.take(60)}..."
    } catch (RuntimeException e) {
        println "[APP ERROR] ${description}: ${e.message}"
    }
}

// Test 1: Successful insert
safeTransaction("Insert valid user") {
    sql.execute("INSERT INTO users VALUES (1, 'alice@test.com', 'Alice')")
}

// Test 2: Duplicate primary key - causes rollback
safeTransaction("Insert duplicate PK") {
    sql.execute("INSERT INTO users VALUES (2, 'bob@test.com', 'Bob')")
    sql.execute("INSERT INTO users VALUES (2, 'charlie@test.com', 'Charlie')")  // Duplicate!
}

// Test 3: Null constraint violation
safeTransaction("Insert null email") {
    sql.execute("INSERT INTO users VALUES (3, NULL, 'Nobody')")
}

// Test 4: Business validation error
safeTransaction("Validate before insert") {
    def email = "bad-email"
    if (!email.contains('@')) {
        throw new RuntimeException("Invalid email format: ${email}")
    }
    sql.execute("INSERT INTO users VALUES (4, ?, 'Test')", [email])
}

// Only valid rows survived
println "\nFinal users:"
sql.eachRow("SELECT * FROM users ORDER BY id") { row ->
    println "  #${row.id} ${row.name} (${row.email})"
}

sql.close()

Output

[OK] Insert valid user
[SQL ERROR] Insert duplicate PK: Unique index or primary key violation: "PRIMARY...
[SQL ERROR] Insert null email: NULL not allowed for column "EMAIL"; SQL statement...
[APP ERROR] Validate before insert: Invalid email format: bad-email

Final users:
  #1 Alice (alice@test.com)

What happened here: We tested four scenarios. Only the first succeeded. In Test 2, Bob was inserted but Charlie failed on the duplicate primary key – the entire transaction rolled back, so even Bob’s row was discarded. Test 3 hit a NOT NULL constraint, and Test 4 caught a business logic error before it even touched the database. The pattern of wrapping withTransaction in a try-catch and distinguishing SQLException from RuntimeException is a solid production pattern.

Example 11: Nested Transaction Behavior

What we’re doing: Exploring what happens when you nest withTransaction calls – and why it might not work the way you’d expect.

Example 11: Nested Transactions

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb11', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE tasks (
        id INT PRIMARY KEY,
        title VARCHAR(100),
        done BOOLEAN DEFAULT FALSE
    )
''')

// Nested withTransaction calls
// Note: Groovy's withTransaction does NOT create true nested transactions
// The inner withTransaction reuses the outer transaction's connection
try {
    sql.withTransaction {
        sql.execute("INSERT INTO tasks VALUES (1, 'Outer task 1', false)")

        // This "nested" transaction shares the same connection
        sql.withTransaction {
            sql.execute("INSERT INTO tasks VALUES (2, 'Inner task 1', false)")
            sql.execute("INSERT INTO tasks VALUES (3, 'Inner task 2', false)")
        }

        sql.execute("INSERT INTO tasks VALUES (4, 'Outer task 2', false)")

        println "All 4 inserts done inside nested transactions"
    }
} catch (Exception e) {
    println "Error: ${e.message}"
}

def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM tasks").cnt
println "Total tasks: ${count}"

sql.eachRow("SELECT * FROM tasks ORDER BY id") { row ->
    println "  #${row.id} ${row.title}"
}

// Now demonstrate that inner failure rolls back everything
sql.execute("DELETE FROM tasks")
println "\n--- Testing inner failure ---"

try {
    sql.withTransaction {
        sql.execute("INSERT INTO tasks VALUES (10, 'Will survive?', false)")

        sql.withTransaction {
            sql.execute("INSERT INTO tasks VALUES (11, 'Inner insert', false)")
            throw new RuntimeException("Inner transaction failed!")
        }
    }
} catch (Exception e) {
    println "Caught: ${e.message}"
}

def countAfter = sql.firstRow("SELECT COUNT(*) AS cnt FROM tasks").cnt
println "Tasks after inner failure: ${countAfter} (all rolled back)"

sql.close()

Output

All 4 inserts done inside nested transactions
Total tasks: 4
  #1 Outer task 1
  #2 Inner task 1
  #3 Inner task 2
  #4 Outer task 2

--- Testing inner failure ---
Caught: Inner transaction failed!
Tasks after inner failure: 0 (all rolled back)

What happened here: Groovy’s withTransaction does not create true nested transactions (like SQL SAVEPOINT-based nesting). The inner withTransaction reuses the same connection and transaction scope. When the inner closure threw an exception, it propagated up to the outer withTransaction, which rolled back everything – including “Outer task 1”. If you need true nested rollback, use savepoints as shown in Example 3.

Example 12: Bulk Data Import from a Collection

What we’re doing: Simulating a real-world bulk data import – reading data from a list (imagine it came from a CSV or API) and inserting it into the database using transactions and batch processing together.

Example 12: Bulk Data Import

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb12', 'sa', '', 'org.h2.Driver')

sql.execute('''
    CREATE TABLE sales (
        id INT AUTO_INCREMENT PRIMARY KEY,
        region VARCHAR(30),
        product VARCHAR(50),
        amount DECIMAL(10,2),
        quarter VARCHAR(10)
    )
''')

// Simulated data from an external source (CSV, API, etc.)
def salesData = [
    [region: 'North', product: 'Widget A', amount: 1500.00, quarter: 'Q1'],
    [region: 'South', product: 'Widget B', amount: 2300.50, quarter: 'Q1'],
    [region: 'East',  product: 'Gadget X', amount: 890.75,  quarter: 'Q1'],
    [region: 'West',  product: 'Gadget Y', amount: 3100.00, quarter: 'Q1'],
    [region: 'North', product: 'Widget A', amount: 1750.25, quarter: 'Q2'],
    [region: 'South', product: 'Widget C', amount: 4200.00, quarter: 'Q2'],
    [region: 'East',  product: 'Gadget X', amount: 1100.50, quarter: 'Q2'],
    [region: 'West',  product: 'Gadget Z', amount: 2800.75, quarter: 'Q2'],
    [region: 'North', product: 'Widget B', amount: 950.00,  quarter: 'Q3'],
    [region: 'South', product: 'Gadget Y', amount: 3500.25, quarter: 'Q3'],
    [region: 'East',  product: 'Widget A', amount: 2100.00, quarter: 'Q3'],
    [region: 'West',  product: 'Widget C', amount: 1800.50, quarter: 'Q3'],
]

// Import with transaction + batch for safety and speed
def importStart = System.currentTimeMillis()

sql.withTransaction {
    sql.withBatch(100, '''
        INSERT INTO sales (region, product, amount, quarter) VALUES (?, ?, ?, ?)
    ''') { ps ->
        salesData.each { record ->
            ps.addBatch([record.region, record.product, record.amount, record.quarter])
        }
    }
}

def elapsed = System.currentTimeMillis() - importStart
def totalRows = sql.firstRow("SELECT COUNT(*) AS cnt FROM sales").cnt
println "Imported ${totalRows} records in ${elapsed}ms"

// Run some analytics on the imported data
println "\nSales summary by region:"
sql.eachRow('''
    SELECT region, COUNT(*) AS deals, SUM(amount) AS total
    FROM sales GROUP BY region ORDER BY total DESC
''') { row ->
    println "  ${row.region.padRight(8)} ${row.deals} deals  \$${String.format('%.2f', row.total)}"
}

println "\nSales by quarter:"
sql.eachRow('''
    SELECT quarter, SUM(amount) AS total
    FROM sales GROUP BY quarter ORDER BY quarter
''') { row ->
    println "  ${row.quarter}: \$${String.format('%.2f', row.total)}"
}

// Top product
def topProduct = sql.firstRow('''
    SELECT product, SUM(amount) AS total
    FROM sales GROUP BY product ORDER BY total DESC LIMIT 1
''')
println "\nTop product: ${topProduct.product} (\$${String.format('%.2f', topProduct.total)})"

sql.close()

Output

Imported 12 records in 15ms

Sales summary by region:
  South    3 deals  $10000.75
  West     3 deals  $7701.25
  North    3 deals  $4200.25
  East     3 deals  $4091.25

Sales by quarter:
  Q1: $7791.25
  Q2: $9851.50
  Q3: $8350.75

Top product: Widget C ($6000.50)

What happened here: We simulated a realistic data import pipeline. The withTransaction wrapping ensures that if any row fails during import, the entire dataset rolls back – you never end up with a partial import. The withBatch inside makes the import fast by reducing database round trips. After the import, we ran aggregation queries to verify the data. This pattern – transaction around batch – is the gold standard for bulk data loading.

Edge Cases and Best Practices

Best Practices

DO:

  • Keep transactions as short as possible – long transactions hold locks and block other users
  • Use withBatch with a PreparedStatement (the two-argument form) for same-type operations – it’s faster and safer against SQL injection
  • Wrap batch imports in withTransaction when you need all-or-nothing semantics
  • Use savepoints when you need partial rollback within a larger transaction
  • Test your batch size – start with 100-500 and measure, don’t guess
  • Always close() your Sql instance when done

DON’T:

  • Hold transactions open while waiting for user input or external API calls
  • Catch and swallow exceptions inside withTransaction – the closure needs to throw for rollback to happen
  • Use SERIALIZABLE isolation unless you truly need it – it kills concurrency
  • Assume nested withTransaction calls create independent transactions – they share the same scope
  • Use batch size of 1 – it defeats the entire purpose of batching

Performance Considerations

Here’s a quick reference on the performance impact of different approaches:

ApproachSpeedSafetyUse When
Individual inserts (auto-commit)SlowestEach row commits independentlySingle-row operations
withBatch onlyFastPartial failures possibleSpeed matters, partial data is acceptable
withTransaction onlyModerateAll-or-nothingSmall number of related operations
withTransaction + withBatchFast + SafeAll-or-nothing with batchingBulk imports requiring atomicity

Memory considerations: Large batch sizes mean more data is held in memory before flushing. For very large imports (millions of rows), use a moderate batch size (500-1000) and consider chunking the data into multiple transactions rather than one massive transaction. A single transaction that touches millions of rows will hold locks for a long time and consume significant undo/redo log space.

Connection pooling: In production, always use a connection pool (HikariCP, Apache DBCP) instead of Sql.newInstance(). Pass the DataSource to new Sql(dataSource) instead. The transaction and batch methods work identically with pooled connections.

Common Pitfalls

Pitfall 1: Swallowing Exceptions Inside withTransaction

Problem: Catching exceptions inside the transaction closure prevents the automatic rollback.

Pitfall 1: Swallowed Exception

// WRONG - this catches the exception, so withTransaction thinks everything is fine
sql.withTransaction {
    try {
        sql.execute("INSERT INTO broken_table VALUES (1)")  // Table doesn't exist
    } catch (Exception e) {
        println "Ignoring error: ${e.message}"
        // Transaction will COMMIT, not rollback!
    }
}

// CORRECT - let the exception propagate
try {
    sql.withTransaction {
        sql.execute("INSERT INTO broken_table VALUES (1)")
        // Exception propagates → automatic rollback
    }
} catch (Exception e) {
    println "Transaction rolled back: ${e.message}"
}

Solution: Never catch exceptions inside withTransaction unless you re-throw them. If you need to handle errors, do it outside the transaction closure.

Pitfall 2: Assuming withBatch Is Transactional

Problem: Thinking withBatch automatically wraps everything in a transaction.

Pitfall 2: Batch Without Transaction

// WRONG - if the batch fails partway, earlier flushed batches are committed
sql.withBatch(100, "INSERT INTO data VALUES (?, ?)") { ps ->
    (1..500).each { i ->
        ps.addBatch([i, "row_${i}"])
        // If row 350 fails, rows 1-300 are already committed!
    }
}

// CORRECT - wrap in a transaction for all-or-nothing
sql.withTransaction {
    sql.withBatch(100, "INSERT INTO data VALUES (?, ?)") { ps ->
        (1..500).each { i ->
            ps.addBatch([i, "row_${i}"])
        }
    }
}

Solution: withBatch is about performance, not safety. If you need atomicity, always wrap it in withTransaction.

Pitfall 3: DDL Statements Inside Transactions

Problem: Some databases (MySQL, Oracle) auto-commit DDL statements (CREATE TABLE, ALTER TABLE) even inside a transaction.

Pitfall 3: DDL in Transactions

// This may NOT roll back the CREATE TABLE on some databases
sql.withTransaction {
    sql.execute("CREATE TABLE temp_data (id INT)")
    sql.execute("INSERT INTO temp_data VALUES (1)")
    throw new RuntimeException("Oops")
    // INSERT is rolled back, but CREATE TABLE may persist (database-dependent)
}

Solution: Keep DDL statements out of transactions. H2 and PostgreSQL handle transactional DDL, but MySQL and Oracle do not. If your code needs to be database-portable, separate schema changes from data changes.

Conclusion

Groovy SQL transactions and batch processing are two of the most important tools in your database toolkit. Transactions give you the safety guarantee that your data changes are atomic – either everything commits or nothing does. Batch processing gives you the speed to handle thousands of rows without making your database weep from round-trip overhead. And when you combine them, you get the best of both worlds: fast, safe, production-grade data operations.

Groovy’s withTransaction and withBatch closures abstract away the verbose JDBC boilerplate of managing auto-commit, commit, rollback, and batch execution – letting you focus on your actual business logic. The key is understanding what each one does: transactions are for safety, batches are for speed, savepoints are for fine-grained control, and isolation levels are for concurrency.

Now that you’re comfortable with transactions and batch processing, you’re ready to explore Groovy DataSet for type-safe queries, which provides a higher-level abstraction over groovy.sql.Sql. And if you need to review the basics, revisit our Groovy SQL Database Connection and Groovy SQL Update and Delete guides.

Summary

  • withTransaction automatically handles commit on success and rollback on exception
  • withBatch groups SQL statements for performance – use batch sizes of 100-1000 for best results
  • Combine withTransaction and withBatch for safe, fast bulk operations
  • Nested withTransaction calls share the same scope – use savepoints for partial rollback
  • Never swallow exceptions inside a transaction closure – it prevents rollback
  • Use cacheConnection when you need direct access to the JDBC Connection for savepoints or isolation levels

If you also work with build tools, CI/CD pipelines, or cloud CLIs, check out Command Playground to practice 105+ CLI tools directly in your browser — no install needed.

Up next: Groovy DataSet for Type-Safe Queries

Frequently Asked Questions

How do I use transactions in Groovy SQL?

Use the withTransaction closure on a groovy.sql.Sql instance: sql.withTransaction { sql.execute("INSERT ..."); sql.execute("UPDATE ...") }. It automatically disables auto-commit, commits if the closure completes successfully, and rolls back if any exception is thrown. No manual commit or rollback calls needed.

What is the difference between withTransaction and withBatch in Groovy?

withTransaction provides atomicity – all operations succeed or fail together. withBatch provides performance – it groups SQL statements and sends them to the database in fewer round trips. They solve different problems and can be combined: wrap withBatch inside withTransaction for fast, atomic bulk operations.

Does Groovy support nested transactions?

Groovy’s withTransaction does not create true nested transactions. Nested calls share the same connection and transaction scope. If the inner closure throws an exception, the entire transaction (outer and inner) rolls back. For partial rollback within a transaction, use JDBC savepoints via cacheConnection and connection.setSavepoint().

What batch size should I use with Groovy withBatch?

A batch size between 100 and 1000 works well for most applications. Batch size 1 gives no benefit. Going from 1 to 100 typically yields a 5-10x speedup. Beyond 1000, improvements taper off while memory usage increases. For remote databases, 100-500 is a good starting point. Always benchmark with your specific database and data.

How do I handle errors in Groovy SQL transactions?

Wrap your withTransaction call in a try-catch block outside the closure. Never catch and swallow exceptions inside the closure – that prevents the automatic rollback. Distinguish between java.sql.SQLException for database errors and RuntimeException for application logic errors. For partial error handling, use savepoints within a cacheConnection block.

Previous in Series: Groovy SQL Update and Delete

Next in Series: Groovy DataSet for Type-Safe Queries

Related Topics You Might Like:

This post is part of the Groovy & Grails Cookbook series on TechnoScripts.com

Rahul

GroovyGroovy Database

batchbulk insertdatabaseGroovyperformancesqlwithBatch

FacebookXPinterestRedditLinkedinWhatsappTelegramEmailShortlink

RahulAuthor posts

Avatar for Rahul

Rahul is a passionate IT professional who loves to sharing his knowledge with others and inspiring them to expand their technical knowledge. Rahul's current objective is to write informative and easy-to-understand articles to help people avoid day-to-day technical issues altogether. Follow Rahul's blog to stay informed on the latest trends in IT and gain insights into how to tackle complex technical issues. Whether you're a beginner or an expert in the field, Rahul's articles are sure to leave you feeling inspired and informed.

Related Posts ...

No comment

Leave a Reply

Your email address will not be published. Required fields are marked *