Skip to main content

Commit Transaction

Commits a database transaction to make all changes permanent. All operations performed within the transaction become durable and visible to other database users.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Transaction Id - The unique identifier of the transaction to commit. This is the transaction ID returned by the Start Transaction node.

Options​

  • None

Output​

  • None

How It Works​

The Commit Transaction node finalizes a database transaction. When executed, the node:

  1. Validates the provided transaction ID is not empty
  2. Retrieves the transaction from the internal transactions dictionary
  3. Calls Commit on the MySqlTransaction object
  4. Makes all transaction changes permanent in the database
  5. Removes the transaction from the transactions dictionary
  6. Releases all locks held by the transaction

Requirements​

  • A valid transaction ID from a Start Transaction node
  • The transaction must be active (not already committed or rolled back)
  • All operations in the transaction must have completed successfully

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Transaction ID is empty or not provided
  • ErrNotFound - Transaction not found (already committed, rolled back, or invalid ID)

Transaction Lifecycle​

Start Transaction (create trx_id)
  ↓
Database Operations (use trx_id)
  ↓
Commit (finalize trx_id) ← You are here
  ↓
Changes are permanent

Usage Examples​

Basic Commit​

Start Transaction (output: trx_id)
  ↓
Query (use trx_id)
  ↓
Non Query (use trx_id)
  ↓
Commit (use trx_id) ← Makes changes permanent

Commit with Error Handling​

Connect
  ↓
Try
  ├─ Start Transaction (trx_id)
  ├─ Database Operations (use trx_id)
  ├─ Commit (trx_id) ← Only if no errors
Catch
  └─ Log Error (transaction auto-rollbacks)
Finally
  └─ Disconnect

Multiple Operations then Commit​

Start Transaction
  ↓
Insert Table (new orders)
  ↓
Non Query (update inventory)
  ↓
Non Query (update customer balance)
  ↓
Insert Table (log entries)
  ↓
Commit ← All changes committed together

Common Use Cases​

  1. Complete Financial Transactions - Commit after successful money transfer
  2. Finalize Order Processing - Commit after order creation and inventory update
  3. Batch Operations - Commit after processing a batch of related updates
  4. Data Migration - Commit after successfully migrating a set of records
  5. Multi-Table Updates - Commit after updating related tables consistently
  6. Workflow Completion - Commit at the end of multi-step business processes

What Happens on Commit​

Before Commit​

  • Changes are visible only within the transaction
  • Rows modified are locked (other transactions wait)
  • Changes can be rolled back
  • No other users see the changes

After Commit​

  • Changes become permanent in the database
  • All locks are released
  • Changes are visible to all users
  • Changes cannot be rolled back
  • Transaction ends

Transaction Patterns​

Success Path​

Start Transaction
  ↓
Operation 1 ✓
  ↓
Operation 2 ✓
  ↓
Operation 3 ✓
  ↓
Commit ← All changes saved

Error Path (Auto-Rollback)​

Start Transaction
  ↓
Operation 1 ✓
  ↓
Operation 2 ✗ Error!
  ↓
(Skip Commit)
  ↓
Disconnect ← Changes automatically rolled back

Tips for Effective Use​

  • Always Commit Success - Call Commit after successful transaction operations
  • Use Try-Catch - Place Commit in the Try block; errors automatically rollback
  • Validate Before Commit - Verify all operations succeeded before committing
  • Transaction Scope - Only commit after all related operations complete
  • Error Recovery - Don't commit if any operation failed
  • Idempotency - Design transactions to be safely retryable
  • Connection Management - Commit before disconnecting to save changes

Best Practices​

Standard Transaction Pattern​

Connect
  ↓
Start Transaction (trx_id)
  ↓
Try
  ├─ Operation 1 (use trx_id)
  ├─ Operation 2 (use trx_id)
  ├─ Operation 3 (use trx_id)
  ├─ Validate Results
  └─ Commit (trx_id)
Catch
  └─ Log Error (no commit = auto-rollback)
Finally
  └─ Disconnect

Validation Before Commit​

// JavaScript node before Commit
const results = $item.operationResults;

// Validate all operations succeeded
if (results.operation1.success &&
    results.operation2.success &&
    results.operation3.success) {
  return { shouldCommit: true };
} else {
  throw new Error('Operation failed, rolling back transaction');
}
Validate Results
  ↓
If (shouldCommit)
  └─ Commit
Else
  └─ Throw Error (auto-rollback)

Multiple Transactions​

For separate logical units:

Transaction 1:
  Start → Operations → Commit

Transaction 2:
  Start → Operations → Commit

(Separate transactions, can commit independently)

Nested Logic (Conditional Commit)​

Start Transaction
  ↓
Main Operations
  ↓
If (condition met)
  ├─ Additional Operations
  └─ Commit
Else
  └─ Don't Commit (rollback)

Transaction Examples​

Example 1: Financial Transfer​

Connect
  ↓
Start Transaction (trx_id)
  ↓
Query: Check source balance (use trx_id, FOR UPDATE)
  ↓
JavaScript: Validate sufficient balance
  ↓
Non Query: Deduct from source (use trx_id)
  ↓
Non Query: Add to destination (use trx_id)
  ↓
Non Query: Log transaction (use trx_id)
  ↓
Commit (trx_id) ← Money transfer complete
  ↓
Disconnect

If any operation fails, the entire transfer is rolled back.

Example 2: Order with Inventory​

Start Transaction (trx_id)
  ↓
Insert: Create order header (use trx_id)
  ↓
Query: Get new order ID (use trx_id)
  ↓
Insert: Create order items (use trx_id)
  ↓
Non Query: Reduce inventory (use trx_id)
  ↓
JavaScript: Verify inventory didn't go negative
  ↓
Commit (trx_id) ← Order confirmed

Example 3: Batch Processing​

Start Transaction (trx_id)
  ↓
Loop: Process 1000 records
  ├─ Non Query: Update record (use trx_id)
  └─ Next record
  ↓
Commit (trx_id) ← Batch committed

Rollback vs Commit​

Commit (Success)​

// All operations successful
Start Transaction → Op1 ✓ → Op2 ✓ → Op3 ✓ → Commit
// Result: All changes saved

Rollback (Failure)​

// Any operation fails
Start Transaction → Op1 ✓ → Op2 ✗ → (skip Commit)
// Result: All changes discarded

Performance Considerations​

  • Commit Overhead - Committing writes to disk; has performance cost
  • Lock Release - Commit releases locks; other transactions can proceed
  • Group Operations - Batch multiple operations in one transaction, commit once
  • Avoid Long Transactions - Commit as soon as logical unit is complete
  • Write Amplification - Each commit triggers disk write; balance transaction size

Common Errors and Solutions​

Transaction Not Found​

Error: ErrNotFound: Transaction not found

Solutions:

  • Verify transaction ID is correct and matches Start Transaction output
  • Ensure Start Transaction executed successfully
  • Check that you haven't already committed this transaction
  • Confirm the transaction ID variable hasn't been overwritten

Transaction ID Cannot Be Empty​

Error: ErrInvalidArg: Transaction ID cannot be empty

Solutions:

  • Ensure the Transaction Id input field is populated
  • Verify the variable containing the transaction ID has a value
  • Check that Start Transaction's output is properly connected

Transaction Already Committed​

Issue: Attempting to commit a transaction twice

Solutions:

  • Use a boolean flag to track if transaction was committed
  • Implement conditional logic to prevent double commits
  • Review flow to ensure Commit is only called once per transaction

Connection Closed Before Commit​

Issue: Connection closed, transaction rolled back instead of committed

Solutions:

  • Always commit before disconnecting
  • Place Commit before Disconnect in the flow
  • Use Finally block to ensure proper order

Debugging Transactions​

Verify Commit Happened​

After committing, verify changes:

Commit (trx_id)
  ↓
Query (without trx_id): SELECT * FROM table WHERE ...
  ↓
Validate: Check that changes are visible

Transaction Timing​

// Track transaction duration
const startTime = Date.now();

// ... transaction operations ...

// Before commit
const duration = Date.now() - startTime;
console.log(`Transaction duration: ${duration}ms`);

// Commit

Verify Operation Count​

// Count operations in transaction
let operationCount = 0;

// Increment for each operation
operationCount++;

// Before commit
console.log(`Committing ${operationCount} operations`);

Advanced Scenarios​

Savepoints (Not Supported)​

MySQL supports savepoints, but the MySQL package doesn't expose them. Each transaction is all-or-nothing.

Distributed Transactions​

For transactions across multiple databases, commit each separately:

DB1: Start Transaction → Operations → Commit
DB2: Start Transaction → Operations → Commit

(No two-phase commit; implement application-level coordination if needed)

Retry Pattern​

Try Attempt 1
  ├─ Start Transaction
  ├─ Operations
  └─ Commit
Catch (e.g., deadlock)
  └─ Try Attempt 2
      ├─ Start Transaction
      ├─ Operations
      └─ Commit

Conditional Commit Based on Results​

Start Transaction
  ↓
Query: Calculate total
  ↓
JavaScript: Check if total meets criteria
  ↓
If (criteria met)
  └─ Commit
Else
  └─ Don't commit (rollback)

Transaction Durability​

After commit:

  • Changes are written to MySQL's redo log
  • Changes survive server crashes (with default innodb_flush_log_at_trx_commit=1)
  • Changes are permanent and cannot be undone (except by new transactions)

Best Practices Summary​

  1. Always Pair with Start Transaction - Every Commit should have a matching Start Transaction
  2. Use in Try Block - Place Commit in Try; Catch handles rollback
  3. Commit Before Disconnect - Ensure changes are saved before closing connection
  4. Validate Before Commit - Check all operations succeeded
  5. One Commit Per Transaction - Don't commit the same transaction twice
  6. Short Transactions - Commit as soon as the logical unit of work completes
  7. Error Handling - If any operation fails, don't commit (let it rollback)

See Also​