Start Transaction

Starts a new database transaction for atomic operations. Transactions ensure that multiple database operations either all succeed or all fail together.

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

• Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).

Options

• Credentials - MySQL database credentials (optional). Use this if you want to start a transaction without a Connect node.
• Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output

• Transaction Id - A unique identifier for the started transaction. Use this ID in subsequent Query, Non Query, and Insert nodes to include them in the transaction.

How It Works

The Start Transaction node begins a new database transaction. When executed, the node:

1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
2. Calls BeginTransaction on the MySQL connection
3. Generates a unique transaction ID (GUID)
4. Stores the transaction in an internal transactions dictionary
5. Returns the transaction ID as output

Requirements

• Either: A valid connection ID from Connect node OR valid database credentials
• The connection must be open and active
• No other active transaction on the same connection (MySQL doesn't support nested transactions)

Error Handling

The node will return specific errors in the following cases:

• ErrInvalidArg - Neither connection ID nor credentials provided
• ErrNotFound - Connection ID not found

Transaction Basics

ACID Properties

Transactions provide:

• Atomicity - All operations succeed or all fail
• Consistency - Database remains in valid state
• Isolation - Transactions don't interfere with each other
• Durability - Committed changes persist

Transaction Lifecycle

Start Transaction
  ↓
Database Operations (using transaction ID)
  ↓
Commit (success) OR Rollback (error)
  ↓
Transaction Ends

Usage Examples

Basic Transaction

Connect
  ↓
Start Transaction (output: trx_id)
  ↓
Non Query (update inventory, use trx_id)
  ↓
Non Query (insert order, use trx_id)
  ↓
Commit (use trx_id)
  ↓
Disconnect

Transaction with Error Handling

Connect
  ↓
Try
  ├─ Start Transaction (output: trx_id)
  ├─ Non Query (deduct from account A, use trx_id)
  ├─ Non Query (add to account B, use trx_id)
  ├─ Commit (use trx_id)
Catch
  └─ Rollback would happen automatically (transaction not committed)
Finally
  └─ Disconnect

Multiple Operations in Transaction

Connect
  ↓
Start Transaction
  ↓
Insert Table (insert order headers)
  ↓
Query (get new order IDs)
  ↓
Insert Table (insert order details)
  ↓
Non Query (update inventory)
  ↓
Non Query (update customer balance)
  ↓
Commit
  ↓
Disconnect

Common Use Cases

1. Financial Transactions - Transfer money between accounts atomically
2. Order Processing - Create order, update inventory, charge customer as one unit
3. Data Integrity - Ensure related records are created/updated together
4. Batch Updates - Update multiple tables consistently
5. Rollback on Error - Undo all changes if any operation fails
6. Multi-Step Operations - Complex workflows requiring consistency

Working with Transactions

Pass Transaction ID to Operations

All database operations that should be part of the transaction must receive the transaction ID:

// Store transaction ID from Start Transaction output
const trxId = $item.trx_id;

// Pass to Query node
{
  "conn_id": "...",
  "trx_id": trxId,  // Include transaction ID
  "query": "SELECT * FROM accounts WHERE id = {{accountId}} FOR UPDATE"
}

// Pass to Non Query node
{
  "conn_id": "...",
  "trx_id": trxId,  // Include transaction ID
  "query": "UPDATE accounts SET balance = balance - {{amount}} WHERE id = {{accountId}}"
}

Commit or Rollback

• Commit - Makes all transaction changes permanent
• Rollback - Discards all transaction changes (happens automatically if transaction is not committed)

Transaction Isolation

MySQL default isolation level is REPEATABLE READ. This affects how transactions see changes from other transactions.

Locking Reads

Use FOR UPDATE to lock rows in a transaction:

-- Query node with transaction
SELECT * FROM inventory
WHERE ProductID = {{productId}}
FOR UPDATE

This prevents other transactions from modifying the row until your transaction commits.

Tips for Effective Use

• Keep Transactions Short - Hold locks for minimal time to avoid blocking other users
• Transaction ID Scope - Store and pass the transaction ID to all related operations
• Error Handling - Use Try-Catch to handle errors and rollback if needed
• Consistent Connection - Use the same connection ID for the transaction and all operations
• Avoid User Input - Don't wait for user input during a transaction
• One Transaction Per Connection - Don't start multiple transactions on the same connection
• Commit Explicitly - Always commit successful transactions; uncommitted transactions may rollback

Best Practices

Financial Transfer Pattern

Connect
  ↓
Start Transaction
  ↓
Query (check balance with FOR UPDATE)
  ↓
JavaScript (validate sufficient balance)
  ↓
Non Query (deduct from source account)
  ↓
Non Query (add to destination account)
  ↓
Non Query (log transaction)
  ↓
Commit
  ↓
Disconnect

Inventory Management Pattern

Connect
  ↓
Start Transaction
  ↓
Query (get current inventory FOR UPDATE)
  ↓
JavaScript (calculate new quantity)
  ↓
Non Query (update inventory)
  ↓
Insert Table (insert order)
  ↓
Non Query (update order count)
  ↓
Commit
  ↓
Disconnect

Error Recovery Pattern

Connect
  ↓
Try
  ├─ Start Transaction (trx_id)
  ├─ Database Operations (use trx_id)
  ├─ Commit (trx_id)
Catch
  ├─ Log Error
  └─ (Transaction auto-rollbacks if not committed)
Finally
  └─ Disconnect

Transaction Examples

Example 1: Order Processing

Start Transaction
  ↓
1. Insert into orders table (create order)
  ↓
2. Query to get new order ID
  ↓
3. Insert into order_items table (add items)
  ↓
4. Update products table (reduce inventory)
  ↓
5. Update customers table (add order count)
  ↓
Commit

If any step fails, all changes are rolled back automatically.

Example 2: Account Transfer

Start Transaction
  ↓
1. SELECT balance FROM accounts WHERE id = {{fromAccount}} FOR UPDATE
  ↓
2. Validate balance >= transfer amount
  ↓
3. UPDATE accounts SET balance = balance - {{amount}} WHERE id = {{fromAccount}}
  ↓
4. UPDATE accounts SET balance = balance + {{amount}} WHERE id = {{toAccount}}
  ↓
5. INSERT into transfers log
  ↓
Commit

Example 3: Data Migration

Start Transaction
  ↓
1. Insert new records into destination table
  ↓
2. Update status in source table
  ↓
3. Insert migration log
  ↓
Commit

Rollback Behavior

Automatic Rollback

Transaction rolls back automatically if:

• Transaction is not committed before connection closes
• An error occurs and transaction is not committed
• The flow ends without calling Commit

Manual Rollback

While there's no explicit Rollback node, you can achieve rollback by:

• Not calling Commit in the Catch block
• The transaction will automatically rollback when the connection closes

Performance Considerations

• Lock Duration - Transactions hold locks; keep them short
• Deadlocks - Can occur when transactions lock resources in different orders
• Isolation Impact - Higher isolation levels may reduce concurrency
• Transaction Size - Large transactions can impact performance and increase deadlock risk
• Indexed Columns - Ensure WHERE clause columns are indexed to minimize lock scope

Common Errors and Solutions

Connection Not Found

Error: ErrNotFound: Connection not found

Solutions:

• Ensure Connect node runs before Start Transaction
• Verify connection ID variable is correct and populated
• Or use the Credentials option to connect directly

Deadlock Detected

Error: Deadlock found when trying to get lock; try restarting transaction

Solutions:

• Keep transactions short
• Access tables in consistent order across all transactions
• Use appropriate isolation levels
• Implement retry logic for transient deadlock errors
• Reduce transaction scope

Transaction Already Active

Issue: Starting a new transaction while one is already active on the connection

Solutions:

• Commit or rollback the existing transaction first
• Use separate connections for concurrent transactions
• Ensure transaction lifecycle is properly managed

Lock Wait Timeout Exceeded

Error: Lock wait timeout exceeded; try restarting transaction

Solutions:

• Reduce transaction duration
• Identify and resolve blocking transactions
• Increase innodb_lock_wait_timeout (MySQL config)
• Use FOR UPDATE SKIP LOCKED for queue patterns

Debugging Transactions

Check Active Transactions

-- In MySQL client
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.innodb_trx;

Monitor Locks

SELECT * FROM performance_schema.data_locks;

Advanced Patterns

Pessimistic Locking

-- Lock rows for update
SELECT * FROM products
WHERE ProductID = {{productId}}
FOR UPDATE

Optimistic Locking

-- Check version before update
UPDATE products
SET Quantity = {{newQty}}, Version = Version + 1
WHERE ProductID = {{productId}}
  AND Version = {{expectedVersion}}

Queue Processing

-- Skip locked rows to process queue concurrently
SELECT * FROM job_queue
WHERE Status = 'pending'
ORDER BY CreatedAt
LIMIT 1
FOR UPDATE SKIP LOCKED

See Also

• Connect - Establish database connections
• Commit - Commit transactions
• Query - Execute SELECT queries in transactions
• Non Query - Execute updates in transactions
• Insert - Insert data in transactions