Transactions

Database transactions are one of the most critical features for ensuring data integrity and consistency in your applications. BoxLang provides comprehensive transaction support through both a modern transaction{} block syntax and the underlying bx:transaction component.

🚀 Why Transactions Matter

Transactions ensure the ACID properties of database operations:

• ⚛️ Atomicity: All operations succeed together or fail together - no partial updates

• 🔒 Consistency: Database remains in a valid state before and after the transaction

• 🏝️ Isolation: Concurrent transactions don't interfere with each other

• 💾 Durability: Committed changes persist even after system failures

💡 Real-World Examples

// ❌ Without transactions - DANGEROUS!
queryExecute( "UPDATE accounts SET balance = balance - 100 WHERE id = 1" );
// 💥 What if the application crashes here?
queryExecute( "UPDATE accounts SET balance = balance + 100 WHERE id = 2" );

// ✅ With transactions - SAFE!
transaction {
    queryExecute( "UPDATE accounts SET balance = balance - 100 WHERE id = 1" );
    queryExecute( "UPDATE accounts SET balance = balance + 100 WHERE id = 2" );
    // Both updates succeed together or both are rolled back
}

📝 Transaction Syntax

BoxLang offers multiple ways to work with transactions:

🎯 Block Syntax (Recommended)

The modern transaction{} block syntax provides automatic transaction management:

// Basic transaction block
transaction {
    queryExecute( "INSERT INTO orders (customer_id, total) VALUES (?, ?)", [ 123, 99.99 ] );
    queryExecute( "INSERT INTO order_items (order_id, product_id) VALUES (?, ?)", [ orderId, 456 ] );
}

// Transaction with specific datasource
transaction datasource="shopDB" {
    queryExecute( "UPDATE inventory SET quantity = quantity - 1 WHERE product_id = ?", [ 456 ] );
    queryExecute( "INSERT INTO sales_log (product_id, sold_at) VALUES (?, NOW())", [ 456 ] );
}

// Transaction with isolation level
transaction isolation="repeatable_read" {
    var currentBalance = queryExecute( "SELECT balance FROM accounts WHERE id = ?", [ accountId ] );
    queryExecute( "UPDATE accounts SET balance = ? WHERE id = ?", [ currentBalance.balance + 100, accountId ] );
}

🔧 Transaction Attributes & Options

📋 Available Attributes

Attribute	Values	Description
action	begin, commit, rollback, setsavepoint	Action to perform on the transaction
isolation	read_uncommitted, read_committed, repeatable_read, serializable	Transaction isolation level
savepoint	String	Name of the savepoint to create or rollback to
datasource	String	Specific datasource name for the transaction

🔒 Isolation Levels Explained

Choose the right isolation level based on your concurrency and consistency needs:

// 🚨 READ_UNCOMMITTED - Lowest isolation, highest performance
transaction isolation="read_uncommitted" {
    // Can read uncommitted data from other transactions
    // Risk: Dirty reads, non-repeatable reads, phantom reads
}

// 📖 READ_COMMITTED - Default for most databases
transaction isolation="read_committed" {
    // Only reads committed data
    // Risk: Non-repeatable reads, phantom reads
}

// 🔄 REPEATABLE_READ - Consistent reads within transaction
transaction isolation="repeatable_read" {
    // Same data read multiple times returns identical results
    // Risk: Phantom reads (new rows may appear)
}

// 🛡️ SERIALIZABLE - Highest isolation, lowest performance
transaction isolation="serializable" {
    // Complete isolation from other transactions
    // Risk: Potential deadlocks, reduced concurrency
}

🎯 Transaction Behavior

🛜 Connections

In BoxLang transactions, no connection is acquired until the first JDBC query is executed. Consider this transaction block:

transaction{
    transactionSetSavepoint( 'beginning' );
    transactionRollback( 'beginning' );
    transactionCommit();
}

This transaction is a no-op. It begins, tries to set a savepoint, then roll back to the savepoint, then commit... but never ran any JDBC queries. Hence, every transactional BIF called above does exactly nothing (besides emit events).

📢Events

BoxLang emits events during the lifecycle of a JDBC transaction which can be used to react to various transaction points in your app:

• onTransactionBegin

• onTransactionEnd

• onTransactionCommit

• onTransactionRollback

• onTransactionSetSavepoint

• onTransactionAcquire

• onTransactionRelease

Read more about these events in transaction events.

🗄️ Datasources

In BoxLang, transactions are inherently single-connection concepts. You can't have a transaction that spans multiple connections or datasources.

Hence, despite containing TWO queries this transaction has only a single query that executes within a transaction context:

transaction{
    // As the first query within the transaction, the datasource obtained for this query will be set as the transaction datasource.
    queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'Ford', 'Fusion' )", {} );
    // This query will execute outside of any transactional context and cannot be rolled back.
    queryExecute( "UPDATE users SET datedModifies=GETDATE() )", {}, { datasource : "adminDB" } );
}

In Adobe and Lucee Server, the first query executed within the datasource determines the transactional datasource; that is, the first query to run sets the datasource to use for that transaction. Any queries which specify a different datasource will execute outside the context of the transaction.

To improve expectations around this behavior, BoxLang supports a datasource attribute on the transaction block:

transaction datasource="carDB" {
    // this query will run inside the transaction because its datasource matches the transaction 'datasource' attribute
    queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'Ford', 'Fusion' )", { datasource : "carDB" } );
    // this query will NOT run inside the transaction because its datasource does NOT match the transaction 'datasource' attribute
    queryExecute( "UPDATE users SET datedModifies=GETDATE() )", {}, { datasource : "adminDB" } );
}

Setting the datasource at the transaction block makes it much more obvious which datasource the transaction will operate upon.

💾 Savepoints

Savepoints allow you to create checkpoints within a transaction for partial rollbacks:

transaction {
    queryExecute( "INSERT INTO orders (id, customer_id) VALUES (1, 123)" );

    transactionSetSavepoint( "afterOrder" );

    try {
        queryExecute( "INSERT INTO order_items (order_id, product_id) VALUES (1, 456)" );
        queryExecute( "UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456" );

        // If inventory goes negative, rollback just the items/inventory changes
        var inventory = queryExecute( "SELECT quantity FROM inventory WHERE product_id = 456" );
        if ( inventory.quantity < 0 ) {
            transactionRollback( "afterOrder" );
            throw "Insufficient inventory";
        }

    } catch ( any e ) {
        transactionRollback( "afterOrder" );
        rethrow;
    }

    // Order remains even if items failed
}

🚨 Exception Handling

Transactions automatically roll back when exceptions occur:

transaction {
    try {
        queryExecute( "INSERT INTO users (name) VALUES (?)", [ "John" ] );

        // This will cause the entire transaction to roll back
        queryExecute( "INSERT INTO invalid_table (data) VALUES (?)", [ "test" ] );

    } catch ( database e ) {
        // Transaction is already rolled back automatically
        writeLog( "Database error: " & e.message );

        // Could start a new transaction for error logging
        transaction {
            queryExecute( "INSERT INTO error_log (error, occurred_at) VALUES (?, NOW())", [ e.message ] );
        }
    }
}

📊 Performance Considerations

⚡ Best Practices

// ✅ Good: Keep transactions short
transaction {
    queryExecute( "UPDATE account SET balance = balance - 100 WHERE id = ?", [ fromAccount ] );
    queryExecute( "UPDATE account SET balance = balance + 100 WHERE id = ?", [ toAccount ] );
} // Transaction ends quickly

// ❌ Bad: Long-running transaction
transaction {
    queryExecute( "SELECT * FROM large_table" ); // Could take minutes

    // Process thousands of records...
    for ( record in largeDataset ) {
        queryExecute( "INSERT INTO processed_data VALUES (?)", [ record ] );
        sleep( 100 ); // Never sleep in transactions!
    }
} // Holds locks for too long

// ✅ Better: Batch processing with smaller transactions
for ( batch in batches ) {
    transaction {
        for ( record in batch ) {
            queryExecute( "INSERT INTO processed_data VALUES (?)", [ record ] );
        }
    }
}

🔄 Deadlock Prevention

// ✅ Good: Consistent order prevents deadlocks
transaction {
    // Always update accounts in ID order
    var accounts = [ fromAccountId, toAccountId ].sort( "numeric" );

    queryExecute( "UPDATE accounts SET balance = balance - 100 WHERE id = ?", [ accounts[1] ] );
    queryExecute( "UPDATE accounts SET balance = balance + 100 WHERE id = ?", [ accounts[2] ] );
}

// ❌ Bad: Inconsistent order can cause deadlocks
transaction {
    queryExecute( "UPDATE accounts SET balance = balance - 100 WHERE id = ?", [ fromAccountId ] );
    queryExecute( "UPDATE accounts SET balance = balance + 100 WHERE id = ?", [ toAccountId ] );
}

🔄 Transaction Events

See transaction events for a list of events that are triggered during transaction lifecycles such as begin, commit, rollback, and savepoint operations.

🏗️ Nested Transactions

BoxLang does not support true nested transactions. If you call transaction{} blocks within another transaction{} block, they will share the same transaction context. This means that if an inner block rolls back, it will roll back the entire transaction, including any operations performed in the outer block.

transaction {
    queryExecute( "INSERT INTO orders (customer_id) VALUES (?)", [ 123 ] );

    transaction {
        queryExecute( "INSERT INTO order_items (order_id, product_id) VALUES (?, ?)", [ orderId, 456 ] );
        transactionRollback(); // This will roll back the entire transaction, including the parent transaction's order insert
    }
}

🧰 Transactional BIFs

See our list of transactional BIFs:

• isInTransaction() - Check if currently inside a transaction

• transactionCommit() - Commit the current transaction

• transactionRollback() - Roll back the current transaction

• transactionSetSavepoint() - Create a savepoint for partial rollbacks

🎨 Common Patterns

💰 Financial Transfers

function transferMoney( fromAccount, toAccount, amount ) {
    transaction isolation="serializable" {
        // Verify sufficient funds
        var fromBalance = queryExecute(
            "SELECT balance FROM accounts WHERE id = ? FOR UPDATE",
            [ fromAccount ]
        );

        if ( fromBalance.balance < amount ) {
            throw "Insufficient funds";
        }

        // Perform transfer
        queryExecute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            [ amount, fromAccount ]
        );

        queryExecute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            [ amount, toAccount ]
        );

        // Log the transaction
        queryExecute(
            "INSERT INTO transfer_log (from_account, to_account, amount, transferred_at) VALUES (?, ?, ?, NOW())",
            [ fromAccount, toAccount, amount ]
        );
    }
}

🛒 E-commerce Order Processing

function processOrder( customerId, items ) {
    transaction {
        // Create order
        var orderResult = queryExecute(
            "INSERT INTO orders (customer_id, status, created_at) VALUES (?, 'pending', NOW())",
            [ customerId ],
            { result: "order" }
        );

        var orderId = order.generatedKey;
        var totalAmount = 0;

        transactionSetSavepoint( "beforeItems" );

        try {
            // Process each item
            for ( item in items ) {
                // Check inventory
                var inventory = queryExecute(
                    "SELECT quantity FROM inventory WHERE product_id = ? FOR UPDATE",
                    [ item.productId ]
                );

                if ( inventory.quantity < item.quantity ) {
                    throw "Insufficient inventory for product " & item.productId;
                }

                // Reserve inventory
                queryExecute(
                    "UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?",
                    [ item.quantity, item.productId ]
                );

                // Add order item
                queryExecute(
                    "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)",
                    [ orderId, item.productId, item.quantity, item.price ]
                );

                totalAmount += item.price * item.quantity;
            }

            // Update order total and mark as confirmed
            queryExecute(
                "UPDATE orders SET total_amount = ?, status = 'confirmed' WHERE id = ?",
                [ totalAmount, orderId ]
            );

        } catch ( any e ) {
            transactionRollback( "beforeItems" );

            // Mark order as failed but keep the order record
            queryExecute(
                "UPDATE orders SET status = 'failed', error_message = ? WHERE id = ?",
                [ e.message, orderId ]
            );

            rethrow;
        }

        return orderId;
    }
}

🔄 Batch Data Processing

function processBatchUpdates( updates ) {
    var batchSize = 100;
    var processed = 0;

    for ( var i = 1; i <= arrayLen( updates ); i += batchSize ) {
        transaction {
            var batch = arraySlice( updates, i, min( batchSize, arrayLen( updates ) - i + 1 ) );

            for ( update in batch ) {
                queryExecute(
                    "UPDATE products SET price = ? WHERE id = ?",
                    [ update.price, update.id ]
                );
                processed++;
            }

            // Log progress
            queryExecute(
                "UPDATE batch_jobs SET processed_count = ? WHERE id = ?",
                [ processed, batchJobId ]
            );
        }
    }
}

💡 Pro Tip: The transaction{} block syntax automatically handles transaction lifecycle management, making it the preferred approach for most use cases. It maps directly to the bx:transaction component but provides cleaner, more readable code.

⚠️ Warning: Always keep transactions as short as possible to avoid blocking other operations. Long-running transactions can cause performance issues and deadlocks in high-concurrency applications.

🔗 Related: For optimal performance, consider using connection pooling and properly configured query caching alongside your transaction management strategy.