Table of Contents

Transactions

Velocity provides full transaction support across all supported databases, allowing you to group multiple operations into an atomic unit of work that either succeeds completely or is rolled back entirely.

Overview

A transaction guarantees the ACID properties for the operations it contains:

  • Atomicity — All operations succeed, or none of them are applied
  • Consistency — The database moves from one valid state to another
  • Isolation — Concurrent transactions do not see each other's uncommitted changes (to a degree determined by the isolation level)
  • Durability — Once committed, changes survive system failure

Transactions are managed through Manager using three methods:

Basic Usage

The standard pattern wraps DML operations in a try/catch block, committing on success and rolling back on failure:

using (var manager = new Manager(connection))
{
    manager.BeginTransaction();

    try
    {
        Record order = new Record(ordersTable);
        order.SetFieldInteger("customer_id", 42);
        order.SetFieldDecimal("total", 149.99M);
        long orderId = manager.AddRecord(order);

        Record line = new Record(orderLinesTable);
        line.SetFieldLong("order_id", orderId);
        line.SetFieldInteger("product_id", 7);
        line.SetFieldInteger("quantity", 3);
        manager.AddRecordNoIdentity(line);

        manager.CommitTransaction();
    }
    catch
    {
        manager.RollbackTransaction();
        throw;
    }
}

Isolation Levels

Velocity supports standard ADO.NET isolation levels via an overload of BeginTransaction:

manager.BeginTransaction(IsolationLevel.Serializable);

The available levels are defined by System.Data.IsolationLevel:

Isolation Level Description
ReadCommitted Only committed data is visible; the default for most databases
ReadUncommitted Allows dirty reads — a transaction can see uncommitted changes from other transactions
RepeatableRead Rows read once cannot be changed by other transactions before the current transaction ends
Serializable Full isolation; transactions are executed as if they were serial

Per-Database Support

Not all databases support every isolation level. Attempting to use an unsupported level will throw an exception from the underlying ADO.NET provider.

Database ReadUncommitted ReadCommitted RepeatableRead Serializable
DB2 ✓ ✓ ✓ ✓
MySQL ✓ ✓ ✓ ✓
Oracle — ✓ — ✓
PostgreSQL ✓ ¹ ✓ ✓ ² ✓
SQL Server ✓ ✓ ✓ ✓
SQLite — ✓ ³ — ✓
Teradata ✓ ✓ ✓ ✓

¹ PostgreSQL accepts ReadUncommitted but treats it as ReadCommitted — dirty reads never occur. ² PostgreSQL treats RepeatableRead as Serializable internally. ³ SQLite operates in serialized mode by default; ReadUncommitted requires WAL journal mode.

DDL and Transactions

Database systems differ fundamentally in how they handle DDL statements (CREATE TABLE, CREATE INDEX, DROP TABLE, etc.) inside an active transaction. Velocity accounts for this automatically.

Transactional DDL Databases

SQL Server, PostgreSQL, and SQLite support DDL inside transactions. This means schema changes can be rolled back just like data changes:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("myschema");

    manager.BeginTransaction();

    try
    {
        // Create a new table inside the transaction
        Table staging = schema.CreateTable("staging_import");
        staging.CreateColumn("id", DataType.Integer, true, true);
        staging.CreateColumn("payload", DataType.VarChar, size: 500);
        manager.CreateTable(staging);

        // Insert data into the staging table
        Record r = new Record(staging);
        r.SetFieldVarChar("payload", "data");
        manager.AddRecord(r);

        manager.CommitTransaction();
        // Both the table creation and the insert are now permanent
    }
    catch
    {
        manager.RollbackTransaction();
        // Both the table creation and the insert are rolled back
        throw;
    }
}

Auto-Commit DDL Databases

MySQL, Oracle, DB2, and Teradata issue an implicit commit whenever a DDL statement is executed. This is a fundamental behaviour of the database engine itself — it cannot be suppressed at the driver level.

Warning

On MySQL, Oracle, DB2, and Teradata, executing any DDL statement (CREATE TABLE, CREATE INDEX, DROP TABLE, ALTER TABLE, etc.) will automatically commit any pending DML in the current transaction. Do not mix DDL and DML within the same transaction on these databases.

Velocity handles this safely by detecting the database type at runtime. DDL commands on auto-commit databases are always executed outside the active transaction, so Velocity's transaction object remains consistent. However, any DML that was pending before the DDL executes will have been implicitly committed by the database engine.

// ✗ Avoid on MySQL, Oracle, DB2, Teradata
using (var manager = new Manager(connection))
{
    manager.BeginTransaction();

    manager.AddRecord(importantRecord);     // pending in transaction
    manager.CreateIndex(newIndex);          // implicit commit fires here —
                                            // importantRecord is now committed
                                            // regardless of what follows
    manager.RollbackTransaction();          // nothing left to roll back
}

// ✓ Correct pattern: DDL first, then DML transaction
using (var manager = new Manager(connection))
{
    manager.CreateIndex(newIndex);          // DDL outside any transaction

    manager.BeginTransaction();
    manager.AddRecord(importantRecord);     // safely in transaction
    manager.CommitTransaction();
}

Full-Text Index Commands

Full-text index operations on SQL Server are a special case. Even though SQL Server supports transactional DDL in general, the following operations cannot participate in a transaction at all and will fail if one is active:

  • CREATE FULLTEXT CATALOG
  • CREATE FULLTEXT INDEX
  • DROP FULLTEXT INDEX
  • DROP FULLTEXT CATALOG
  • ALTER FULLTEXT INDEX (used by UpdateFullTextIndex)

Velocity handles this automatically — full-text index commands on SQL Server always execute on a separate connection that carries no transaction, regardless of whether BeginTransaction has been called. Any DML operations in the active transaction are unaffected and can still be committed or rolled back normally.

Warning

CREATE FULLTEXT INDEX requires a schema modification lock (SCH-M) on the target table. If the active transaction already holds row locks on that table (from a prior INSERT, UPDATE, or SELECT), the full-text operation will block waiting for those locks to be released and will eventually time out. Always perform full-text DDL before acquiring row locks on the same table.

using (var manager = new Manager(connection))
{
    // Schema and full-text index are built outside any transaction.
    // This must happen before any DML transaction touches the same table.
    manager.BuildSchema(schema, overwrite: true);
    manager.CreateFullTextIndex(table.FullTextIndex);

    manager.BeginTransaction();

    // DML is enrolled in the transaction as normal
    manager.AddRecord(articleRecord);

    manager.CommitTransaction();   // commits only the DML
}

Summary

The table below shows which DDL commands participate in an active transaction on each database:

Database DdlTransactions Regular DDL in Tx Full-Text DDL in Tx
DB2 — — —
MySQL — — —
Oracle — — —
PostgreSQL ✓ ✓ ✓
SQL Server ✓ ✓ — ¹
SQLite ✓ ✓ ✓
Teradata — — —

¹ SQL Server full-text operations (CreateFullTextIndex, DropFullTextIndex, UpdateFullTextIndex) always execute outside any active transaction.

Multiple Operations Example

The following example shows a typical pattern for transferring funds between accounts atomically. Velocity does not support in-place arithmetic in UPDATE statements, so the current balance is read first and the new value is calculated in C# before being written back — all within a single serializable transaction:

using (var manager = new Manager(connection))
{
    Table accounts = schema["accounts"];

    manager.BeginTransaction(IsolationLevel.Serializable);

    try
    {
        // Read and debit the source account
        decimal sourceBalance;
        using (ResultSet result = manager.Retrieve(
            new Query()
                .Select([accounts["balance"]])
                .From(accounts)
                .Where(new Criterion<int>(accounts["account_id"],
                    ConditionalType.Equals, sourceAccountId))))
        {
            sourceBalance = result[0].GetFieldDecimal("balance");
        }

        Update debit = new Update(accounts,
            new Criterion<int>(accounts["account_id"],
                ConditionalType.Equals, sourceAccountId));
        debit.SetFieldDecimal("balance", sourceBalance - 500M);
        manager.UpdateRecords(debit);

        // Read and credit the destination account
        decimal destBalance;
        using (ResultSet result = manager.Retrieve(
            new Query()
                .Select([accounts["balance"]])
                .From(accounts)
                .Where(new Criterion<int>(accounts["account_id"],
                    ConditionalType.Equals, destAccountId))))
        {
            destBalance = result[0].GetFieldDecimal("balance");
        }

        Update credit = new Update(accounts,
            new Criterion<int>(accounts["account_id"],
                ConditionalType.Equals, destAccountId));
        credit.SetFieldDecimal("balance", destBalance + 500M);
        manager.UpdateRecords(credit);

        manager.CommitTransaction();
    }
    catch
    {
        manager.RollbackTransaction();
        throw;
    }
}

Best Practices

  1. Always pair BeginTransaction with a CommitTransaction or RollbackTransaction — An uncommitted transaction holds locks and consumes resources until the connection closes.

  2. Use try/catch/throw — Wrap transactional blocks so that any exception causes a rollback and is still propagated to the caller.

  3. Keep transactions short — Long-running transactions hold locks that block other connections. Do as little work as possible between BeginTransaction and CommitTransaction.

  4. Do not mix DDL and DML on auto-commit databases — On MySQL, Oracle, DB2, and Teradata, DDL implicitly commits the transaction. Perform all schema changes before opening a data transaction.

  5. Use the appropriate isolation level — Higher isolation levels improve correctness but reduce concurrency. Use ReadCommitted (the default) unless you have a specific reason to escalate.

  6. Schema operations rarely need transactions — DDL is typically performed during schema setup, not during normal application operation. Reserve transactions for DML operations.

See Also