JDBC 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 ] );
}⚙️ Manual Transaction Control
For complex scenarios, use transaction BIFs directly:
if ( !isInTransaction() ) {
transactionBegin();
}
try {
transactionSetSavepoint( "beforeCriticalOperation" );
// Critical database operations
queryExecute( "UPDATE critical_data SET value = ?", [ newValue ] );
if ( someCondition ) {
transactionRollback( "beforeCriticalOperation" );
} else {
transactionCommit();
}
} catch ( any e ) {
transactionRollback();
rethrow;
}🔧 Transaction Attributes & Options
📋 Available Attributes
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
nested
Boolean
Whether this is a nested transaction (default: false)
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).
🗄️ 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, 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 fully supports nested or "child" transactions. Nested transactions use the same database connection as the parent transaction, which means queries will run on the same datasource as the parent, using the same connection parameters, and can be rolled back partially or in whole as the parent issues transactionRollback() statements.
To achieve all this, BoxLang transactions are savepoint-driven. All savepoints created (and referenced) within child transactions are prefixed within a unique ID to prevent collision. For example, executing transactionSetSavepoint( 'insert' ) within a child transaction will under the hood create a CHILD_{UUID}_insert savepoint. Furthermore, when child transaction begins a CHILD_{UUID}_BEGIN savepoint is created which will be used as a rollback point if transactionRollback() is called with no savepoint parameter.
📋 Nested Transaction Behaviors
Rolling back the child transaction will roll back to the
CHILD_{UUID}_BEGINsavepoint.A transaction commit in the child transaction does not commit the transaction, but instead creates a
CHILD_{UUID}_COMMITsavepoint.Rolling back the (entire) parent transaction will roll back the child transaction.
Rolling back the parent transaction to a pre-child savepoint will roll back the entire child transaction.
📚 Examples
Check out a few examples to hammer home the behaviors of a nested transaction:
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'Ford', 'Fusion' )", {} );
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'BMW', 'X3' )", {} );
transactionRollback();
}
}In this example, the 'BMW X3' insert is rolled back by the unqualified transactionRollback() call, but the 'Ford Fusion' insert in the parent transaction is still committed to the database when the parent transaction completes:
Ford
Fusion
Note that we would get the same result if the child transaction threw an exception instead of rolling back:
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'Ford', 'Fusion' )", {} );
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'BMW', 'X3' )", {} );
doSomethingThatThrows();
}
}Ford
Fusion
Let's run this same one again, but replace the child rollback with a commit, and add a rollback to the parent transaction:
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'Ford', 'Fusion' )", {} );
transaction{
queryExecute( "INSERT INTO vehicles ( make, model ) VALUES ( 'BMW', 'X3' )", {} );
transactionCommit();
}
transactionRollback();
}You can see that regardless of the transactionCommit() in the child transaction, both inserts are rolled back:
🧰 Transactional BIFs
See our list of transactional BIFs:
isInTransaction()- Check if currently inside a transactiontransactionBegin()- Start a new transaction manuallytransactionCommit()- Commit the current transactiontransactionRollback()- Roll back the current transactiontransactionSetSavepoint()- 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.
Last updated
Was this helpful?