Skip to main content

Start Transaction

Starts a new database transaction for atomic operations 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​

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

Options​

  • Credentials - SQL Server database credentials (optional). Use this if you want to start a transaction without a Connect node.

Output​

  • Transaction Id - A unique identifier for the started transaction. Store this value to use in subsequent database operations within 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 SqlConnection
  3. Generates a unique transaction ID (GUID)
  4. Stores the transaction in an internal transactions dictionary
  5. Returns the transaction ID as output

All subsequent database operations that use this transaction ID will be part of the same transaction and can be committed or rolled back together.

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

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Neither connection ID nor credentials provided
  • ErrNotFound - Connection ID not found
  • ErrConnection - Cannot connect to SQL Server (when using credentials mode)
  • ErrRuntime - Cannot start transaction (connection closed, transaction already active, etc.)

Usage Examples​

Basic Transaction Pattern​

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

Transaction with Error Handling​

Try:
  Connect
    ↓
  Start Transaction
    ↓
  Non Query 1 (UPDATE orders)
    ↓
  Non Query 2 (UPDATE inventory)
    ↓
  Commit Transaction
Catch:
  Rollback Transaction
Finally:
  Disconnect

Multiple Operations in Transaction​

Connect
  ↓
Start Transaction
  ↓
Insert Table (new customer)
  ↓
Query (get new customer ID)
  ↓
Insert Table (customer orders)
  ↓
Non Query (update statistics)
  ↓
Commit Transaction
  ↓
Disconnect

Common Use Cases​

  1. Data Consistency - Ensure multiple related operations succeed or fail together
  2. Financial Transactions - Process payments, transfers, and accounting entries atomically
  3. Order Processing - Create orders, update inventory, and log activities as one unit
  4. Batch Updates - Update multiple tables and ensure all changes are applied or none
  5. Data Migration - Move data between tables with rollback capability
  6. Complex Business Logic - Execute multi-step business processes with atomicity
  7. Error Recovery - Roll back changes when errors occur mid-process

Transaction Isolation Levels​

SQL Server transactions use READ COMMITTED isolation level by default. This means:

  • Reads acquire shared locks and release them immediately after reading
  • Writes acquire exclusive locks and hold them until transaction ends
  • Prevents dirty reads
  • Allows non-repeatable reads and phantom reads

For different isolation levels, use Non Query before Start Transaction:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  -- Dirty reads allowed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED    -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   -- Prevents non-repeatable reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE      -- Full isolation, prevents phantoms
SET TRANSACTION ISOLATION LEVEL SNAPSHOT          -- Uses row versioning

Tips for Effective Use​

  • Keep Transactions Short - Minimize the time between Start and Commit to reduce locking
  • Transaction ID - Always store the transaction ID in a variable for use in subsequent operations
  • Error Handling - Use Try-Catch-Finally with Rollback in Catch block
  • Connection Reuse - Start transaction on an existing connection, not a new one
  • Explicit Commit - Always explicitly commit or rollback; don't leave transactions hanging
  • Avoid User Input - Don't wait for user input during an active transaction
  • One Transaction Per Connection - Don't start multiple transactions on the same connection
  • Direct Credentials - Can use credentials mode for quick transactional operations

Best Practices​

Pattern 1: Simple Transaction​

Connect
  ↓
Start Transaction
  ↓
Database Operations
  ↓
Commit Transaction
  ↓
Disconnect

Pattern 2: Transaction with Rollback​

Try:
  Start Transaction
  Operations
  Commit
Catch:
  Rollback

Pattern 3: Conditional Commit​

Start Transaction
  ↓
Operations
  ↓
JavaScript (validate results)
  ↓
If valid: Commit
If invalid: Rollback

Pattern 4: Nested Business Logic​

Connect
  ↓
Start Transaction
  ↓
Insert Customer
  ↓
Query (verify insert)
  ↓
If verified:
  Insert Orders
  Commit
Else:
  Rollback
  ↓
Disconnect

Transaction Scope​

What Should Be in a Transaction​

Include:

  • Related INSERT, UPDATE, DELETE operations
  • Operations that must succeed or fail together
  • Multi-table updates that must be consistent
  • Critical business logic requiring atomicity

Exclude:

  • Long-running queries that don't modify data
  • Operations on unrelated tables
  • File system operations
  • External API calls
  • User interaction or input

Common Errors and Solutions​

Cannot Start Transaction​

Error: Connection must be open and valid to start a transaction

Solutions:

  • Ensure Connect node runs before Start Transaction
  • Verify connection is still open
  • Check that connection wasn't closed or timed out

Transaction Already Active​

Error: A transaction is already active on this connection

Solutions:

  • Ensure previous transaction was committed or rolled back
  • Don't start multiple transactions on same connection
  • Use separate connections for concurrent transactions

Connection Not Found​

Error: ErrNotFound: Connection not found

Solutions:

  • Verify Connect node executed successfully
  • Ensure connection ID is correct
  • Or use the Credentials option to connect directly

Transaction Lifecycle​

  1. Start Transaction - Begins transaction, returns transaction ID
  2. Execute Operations - Run queries, inserts, updates using transaction ID
  3. Commit or Rollback - Finalize or undo all changes
  4. Transaction Closed - Transaction ID no longer valid

ACID Properties​

Transactions in SQL Server guarantee ACID properties:

  • Atomicity - All operations succeed or all fail
  • Consistency - Database remains in valid state
  • Isolation - Concurrent transactions don't interfere
  • Durability - Committed changes are permanent

Locking Behavior​

Locks Acquired​

When you start a transaction, SQL Server acquires locks as operations execute:

  • Shared Locks - Acquired during SELECT operations
  • Exclusive Locks - Acquired during INSERT, UPDATE, DELETE
  • Update Locks - Acquired during UPDATE searches

Lock Duration​

  • Locks are held until transaction commits or rolls back
  • Long transactions can cause blocking
  • Other users may wait for locks to release

Avoiding Deadlocks​

Best Practices:

  • Access tables in consistent order across all transactions
  • Keep transactions as short as possible
  • Use appropriate isolation level
  • Implement retry logic for deadlock victims
// Retry logic example
let retries = 3;
let success = false;

while (retries > 0 && !success) {
  try {
    // Execute transaction
    success = true;
  } catch (error) {
    if (error.includes('deadlock')) {
      retries--;
      // Wait before retry
      await new Promise(resolve => setTimeout(resolve, 1000));
    } else {
      throw error;
    }
  }
}

Advanced Examples​

Multi-Table Insert Transaction​

Connect
  ↓
Start Transaction
  ↓
Non Query: INSERT INTO Orders (CustomerID, OrderDate) VALUES ({{customerId}}, GETDATE())
  ↓
Query: SELECT SCOPE_IDENTITY() AS OrderID
  ↓
JavaScript: Store order ID
  ↓
Non Query: INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES ({{orderId}}, {{productId}}, {{quantity}})
  ↓
Non Query: UPDATE Products SET Stock = Stock - {{quantity}} WHERE ProductID = {{productId}}
  ↓
Commit Transaction
  ↓
Disconnect

Financial Transaction​

Connect
  ↓
Start Transaction
  ↓
Query: SELECT Balance FROM Accounts WHERE AccountID = {{fromAccount}} WITH (UPDLOCK)
  ↓
JavaScript: Verify sufficient balance
  ↓
Non Query: UPDATE Accounts SET Balance = Balance - {{amount}} WHERE AccountID = {{fromAccount}}
  ↓
Non Query: UPDATE Accounts SET Balance = Balance + {{amount}} WHERE AccountID = {{toAccount}}
  ↓
Non Query: INSERT INTO TransactionLog (FromAccount, ToAccount, Amount, Timestamp) VALUES ({{fromAccount}}, {{toAccount}}, {{amount}}, GETDATE())
  ↓
Commit Transaction
  ↓
Disconnect

Conditional Transaction​

// After some operations in transaction
const result = $item.queryResult;

if (result.rows.length > 0 && result.rows[0].Total > 1000) {
  // Continue to Commit
  return { shouldCommit: true, ...result };
} else {
  // Continue to Rollback
  return { shouldCommit: false, ...result };
}

Batch Processing with Transaction​

Connect
  ↓
Loop (over batches):
  Start Transaction
    ↓
  Insert Table (batch data)
    ↓
  Query (verify insert count)
    ↓
  JavaScript (check if count matches expected)
    ↓
  If valid: Commit
  If invalid: Rollback and log error
  ↓
Disconnect

Transaction Savepoints​

SQL Server supports savepoints within transactions for partial rollbacks:

Start Transaction
  ↓
Non Query: SAVE TRANSACTION Savepoint1
  ↓
Operations 1
  ↓
Non Query: SAVE TRANSACTION Savepoint2
  ↓
Operations 2
  ↓
If error in Operations 2:
  Non Query: ROLLBACK TRANSACTION Savepoint2
  (Only Operations 2 are rolled back)
  ↓
Commit Transaction

Monitoring Transactions​

Check Active Transactions​

-- In Query node
SELECT
  s.session_id,
  s.login_name,
  t.transaction_begin_time,
  t.transaction_type,
  t.transaction_state
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
WHERE s.is_user_process = 1

Check Locks​

-- In Query node
SELECT
  request_session_id AS SessionID,
  resource_type AS ResourceType,
  resource_database_id AS DatabaseID,
  resource_description AS Resource,
  request_mode AS LockMode,
  request_status AS Status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

Performance Considerations​

Transaction Size​

  • Small Transactions - Faster, less locking, better concurrency
  • Large Transactions - More data consistency, but higher risk of blocking

Transaction Duration​

  • Aim for transactions under 1 second when possible
  • Long transactions increase lock contention
  • Consider breaking into smaller transactions if appropriate

Read vs Write​

  • Read-only queries don't usually need transactions
  • Use transactions when data consistency across reads is critical
  • Consider READ COMMITTED SNAPSHOT for read-heavy workloads

Using Direct Credentials (Without Connect)​

Start Transaction Node:
  - Credentials: [Select Database Credential]
  - Connection Id: (leave empty)

The transaction will use a new connection created from credentials. Ensure subsequent operations use the same transaction ID.

See Also​

  • Connect - Establish database connections
  • Commit - Commit transactions
  • Query - Execute SELECT queries within transaction
  • Non Query - Execute INSERT, UPDATE, DELETE within transaction
  • Insert - Bulk insert within transaction