Transactions

Group multiple operations into atomic units. Either all succeed or all fail together.

Why Use Transactions?

• Atomicity - All or nothing execution
• Consistency - Database stays valid
• Isolation - Operations don't interfere
• Performance - Batch writes are faster

Basic Transaction

db.begin_transaction

db.insert_into ("orders")
    .value ("user_id", 1)
    .value ("total", 99.99)
    .execute

db.insert_into ("order_items")
    .value ("order_id", db.last_insert_rowid)
    .value ("product_id", 42)
    .value ("quantity", 2)
    .execute

db.commit  -- Make changes permanent

Rollback on Error

db.begin_transaction

db.insert_into ("accounts").value ("balance", 100).execute
local
    success: BOOLEAN
do
    success := process_payment

    if success then
        db.commit
    else
        db.rollback  -- Undo all changes
    end
end

Check Transaction Status

if db.in_transaction then
    print ("Transaction is active%N")
end

Money Transfer Example

transfer_funds (a_from, a_to: INTEGER; a_amount: REAL_64)
    local
        from_balance: REAL_64
        result: SIMPLE_SQL_RESULT
    do
        db.begin_transaction

        -- Check source has enough funds
        result := db.select_from ("accounts")
            .columns ("balance")
            .where ("id = ?", a_from)
            .execute

        if attached result.first as row then
            from_balance := row.real_value ("balance")

            if from_balance >= a_amount then
                -- Debit source
                db.update ("accounts")
                    .set_raw ("balance", "balance - " + a_amount.out)
                    .where ("id = ?", a_from)
                    .execute

                -- Credit destination
                db.update ("accounts")
                    .set_raw ("balance", "balance + " + a_amount.out)
                    .where ("id = ?", a_to)
                    .execute

                db.commit
                print ("Transfer successful%N")
            else
                db.rollback
                print ("Insufficient funds%N")
            end
        else
            db.rollback
            print ("Account not found%N")
        end
    end

Bulk Insert Performance

Transactions dramatically improve bulk insert performance.

insert_many_records (a_records: ARRAYED_LIST [TUPLE [name: STRING; value: INTEGER]])
    do
        -- Without transaction: slow (each insert is a separate transaction)
        -- With transaction: fast (all inserts in one transaction)

        db.begin_transaction
        across a_records as r loop
            db.insert_into ("data")
                .value ("name", r.name)
                .value ("value", r.value)
                .execute
        end
        db.commit
    end

Savepoints (Nested Transactions)

SQLite supports savepoints for partial rollback within a transaction.

db.begin_transaction

db.execute ("SAVEPOINT before_risky_operation")

-- Try something that might fail
if risky_operation_failed then
    db.execute ("ROLLBACK TO before_risky_operation")
    -- Continue with rest of transaction
end

db.commit

Next Steps

• Soft Deletes - Safe deletion patterns
• Eager Loading - Prevent N+1 queries
• API Reference - Transaction features