Skip to main content

Commit Transaction

Commits a database transaction to make all changes permanent in Microsoft SQL Server.

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 transaction identifier returned by the Start Transaction node. This identifies which transaction to commit.

Options​

  • None

Output​

  • None - The transaction is committed, but no results are returned.

How It Works​

The Commit Transaction node finalizes a database transaction, making all changes permanent. When executed, the node:

  1. Validates the transaction ID is provided and not empty
  2. Retrieves the transaction from the internal transactions dictionary
  3. Calls Commit() on the SqlTransaction
  4. Removes the transaction from the dictionary
  5. All changes made during the transaction are permanently saved to the database
  6. All locks held by the transaction are released

Requirements​

  • Valid transaction ID from a Start Transaction node
  • The transaction must still be active (not already committed or rolled back)
  • All operations within 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)
  • ErrRuntime - Commit failed (connection closed, constraint violation detected at commit, etc.)

Usage Examples​

Basic Commit​

Connect
  ↓
Start Transaction (output: trx_id)
  ↓
Non Query (UPDATE, use trx_id)
  ↓
Non Query (INSERT, use trx_id)
  ↓
Commit (use trx_id)
  ↓
Disconnect

Commit with Verification​

Start Transaction
  ↓
Insert Table (new records)
  ↓
Query (SELECT COUNT to verify inserts)
  ↓
JavaScript (check count matches expected)
  ↓
If valid: Commit
If invalid: Rollback

Commit in Try-Catch​

Try:
  Start Transaction
    ↓
  Database Operations
    ↓
  Commit Transaction
Catch:
  Rollback Transaction
  Log Error
Finally:
  Disconnect

Common Use Cases​

  1. Finalizing Updates - Permanently save database changes after verification
  2. Order Processing - Commit completed order and inventory updates
  3. Financial Transactions - Make money transfers permanent after validation
  4. Batch Operations - Finalize bulk data modifications
  5. Multi-Step Workflows - Complete complex business processes
  6. Data Migration - Finalize data moves after successful transfer
  7. Audit Trail - Commit changes along with audit log entries

What Happens When You Commit​

Database Changes​

  • All INSERT, UPDATE, DELETE operations become permanent
  • Changes are visible to other database users/connections
  • Transaction log is updated with committed changes
  • Database state reflects all modifications

Locks Released​

  • All shared locks acquired during reads are released
  • All exclusive locks acquired during writes are released
  • Other transactions can now access the affected data
  • Blocked queries can proceed

Transaction Closed​

  • Transaction ID becomes invalid
  • Cannot perform more operations using this transaction ID
  • New transaction needed for additional operations

Tips for Effective Use​

  • Verify Before Commit - Query and validate data before committing
  • Transaction ID - Ensure correct transaction ID is used
  • Error Handling - Always have rollback option in error scenarios
  • Timing - Commit as soon as verification is complete to release locks
  • One Commit Per Transaction - Each transaction can only be committed once
  • Logging - Log successful commits for audit purposes
  • Validation - Validate business rules before committing

Best Practices​

Pattern 1: Verify Before Commit​

Start Transaction
  ↓
Operations
  ↓
Query (verify results)
  ↓
JavaScript (validate)
  ↓
If valid:
  Commit
Else:
  Rollback

Pattern 2: Commit with Logging​

Start Transaction
  ↓
Database Operations
  ↓
Non Query (INSERT INTO AuditLog)
  ↓
Commit
  ↓
JavaScript (log success to external system)

Pattern 3: Conditional Commit​

// JavaScript node to decide commit or rollback
const totalAmount = $item.queryResult.rows[0].TotalAmount;
const expectedAmount = $item.expectedTotal;

if (Math.abs(totalAmount - expectedAmount) < 0.01) {
  // Continue to Commit node
  return { action: 'commit', ...result };
} else {
  // Continue to Rollback node
  return { action: 'rollback', error: 'Amount mismatch', ...result };
}

Pattern 4: Multiple Validations​

Start Transaction
  ↓
Operations
  ↓
Query 1 (check constraint 1)
  ↓
Query 2 (check constraint 2)
  ↓
JavaScript (validate all results)
  ↓
All valid: Commit
Any invalid: Rollback

Common Errors and Solutions​

Transaction ID Cannot Be Empty​

Error: ErrInvalidArg: Transaction ID cannot be empty

Solutions:

  • Ensure Start Transaction node runs before Commit
  • Verify transaction ID variable is correctly passed
  • Check that the transaction ID output variable name matches the input

Transaction Not Found​

Error: ErrNotFound: Transaction not found

Solutions:

  • Verify Start Transaction executed successfully
  • Check that transaction wasn't already committed or rolled back
  • Ensure transaction ID is correct and hasn't been modified
  • Don't commit the same transaction twice

Commit Failed​

Error: Commit failed due to constraint violation or connection error

Solutions:

  • Check that all constraints are satisfied (foreign keys, unique, check)
  • Verify connection is still open
  • Ensure no deferred constraints are violated
  • Check database log for specific error details

Transaction States​

Before Commit​

  • Transaction is active
  • Changes are visible only to current transaction
  • Locks are held
  • Can still rollback

During Commit​

  • SQL Server validates all constraints
  • Writes changes to transaction log
  • Writes changes to data files
  • Cannot be interrupted or rolled back

After Commit​

  • Changes are permanent and durable
  • Changes visible to all users
  • Locks released
  • Transaction ID invalid
  • Cannot rollback

Deferred Constraints​

SQL Server evaluates some constraints at commit time:

  • Check constraints
  • Foreign key constraints
  • Unique constraints

If any constraint fails at commit time, the entire transaction is rolled back.

Start Transaction
  ↓
Insert (may violate constraint)
  ↓
Commit (constraint checked here)
  ↓
If constraint fails: automatic rollback

Commit vs Rollback Decision​

When to Commit​

  • All operations completed successfully
  • All validations passed
  • Business rules satisfied
  • Data integrity confirmed
  • No errors occurred

When to Rollback​

  • Any operation failed
  • Validation check failed
  • Business rule violated
  • Error occurred during processing
  • User cancellation
  • Unexpected data encountered
// Decision logic
if ($item.operationsSucceeded &&
    $item.validationPassed &&
    $item.recordCount === $item.expectedCount) {
  // Commit
  return { shouldCommit: true };
} else {
  // Rollback
  return { shouldCommit: false };
}

Advanced Examples​

Multi-Stage Commit Verification​

Start Transaction
  ↓
Insert Customer
  ↓
Query: SELECT COUNT(*) FROM Customers WHERE Email = '{{email}}'
  ↓
JavaScript: Verify count is 1
  ↓
Insert Orders
  ↓
Query: SELECT SUM(Amount) FROM Orders WHERE CustomerID = {{customerId}}
  ↓
JavaScript: Verify total matches expected
  ↓
Query: Check inventory levels
  ↓
JavaScript: Verify all items in stock
  ↓
All checks pass: Commit
Any check fails: Rollback

Commit with Audit Trail​

Start Transaction
  ↓
Update Orders SET Status = 'Completed'
  ↓
Query: SELECT @@ROWCOUNT AS UpdatedRows
  ↓
Non Query: INSERT INTO AuditLog (Action, RecordCount, Timestamp, User)
           VALUES ('Order Completion', {{updatedRows}}, GETDATE(), '{{user}}')
  ↓
Commit

Conditional Batch Commit​

// Process multiple batches
const batches = $item.batches;
let successCount = 0;

for (const batch of batches) {
  // Each batch in its own transaction
  // Start Transaction → Process → Verify → Commit or Rollback
  if (batch.success) {
    successCount++;
  }
}

return { successCount, totalBatches: batches.length };

Commit with External Notification​

Start Transaction
  ↓
Database Operations
  ↓
Commit
  ↓
HTTP Request (notify external system of successful commit)
  ↓
JavaScript (log completion)

Monitoring and Debugging​

Log Commit Success​

// JavaScript node after Commit
const startTime = $item.transactionStartTime;
const endTime = new Date();
const duration = endTime - startTime;

console.log(`Transaction committed successfully`);
console.log(`Duration: ${duration}ms`);
console.log(`Records affected: ${$item.recordCount}`);
console.log(`Transaction ID: ${$item.trx_id}`);

Track Transaction Metrics​

// Store transaction metrics
return {
  committed: true,
  timestamp: new Date().toISOString(),
  duration: $item.duration,
  operations: $item.operationCount,
  recordsAffected: $item.recordCount
};

Performance Considerations​

Commit Timing​

  • Commit as soon as possible after validation
  • Don't delay commit unnecessarily
  • Long transactions increase lock contention
  • Quick commits improve concurrency

Transaction Size​

  • Smaller transactions commit faster
  • Large transactions take longer to commit
  • Consider breaking very large operations into smaller transactions
  • Balance atomicity needs with performance

Lock Duration​

Bad:
  Start Transaction
  Long Processing
  External API Call
  User Input
  Commit

Good:
  Prepare Data
  Start Transaction
  Database Operations
  Commit (release locks quickly)
  Post-Processing

Commit Durability​

SQL Server guarantees durability (the D in ACID):

  • Committed changes survive crashes
  • Changes written to transaction log
  • Log flushed to disk at commit
  • Database can recover committed transactions after crash

Delayed Durability (SQL Server 2014+)​

For high-throughput scenarios, you can enable delayed durability:

-- In Non Query before Start Transaction
ALTER DATABASE CURRENT SET DELAYED_DURABILITY = ALLOWED

-- Then in commit, there's a small risk of data loss on crash
-- but significantly improved throughput

Use with caution: Only for scenarios where slight data loss is acceptable.

Commit vs Auto-Commit​

Explicit Transaction (with Start/Commit)​

  • You control when to commit
  • Can rollback if needed
  • All operations are atomic
  • Better for complex logic

Auto-Commit (without transaction)​

  • Each statement commits automatically
  • Cannot rollback
  • Simpler for single operations
  • Less control

See Also​