Transactions, migrations & recovery

The transaction owns its commands#

Begin with Set transaction = Await db.BeginTransactionAsync(), then create every command through transaction.CreateCommand. While the transaction is active, ordinary connection commands are blocked. Using db.CreateCommand for the second half of a transaction does not place it inside that transaction.

A successful statement does not commit the group, and a failed statement does not automatically end it. Commit deliberately on success; roll back or dispose an unfinished transaction on failure.

Transfer with an observable rollback#

DataWorkbench starts two practice accounts at 100 units each. The handler moves 10 units and lets a checkbox choose rollback instead of commit. Copy the complete lesson for the declarations, controls, migration and RefreshRows helper; the code below is the exact complete handler.

Private Async Sub cmdTransfer_Click()
    Dim transaction As DbTransaction
    Dim command As DbCommand
    Dim changed As LongLong
    If mBusy Then Exit Sub
    SetBusy True
    Try
        Set transaction = Await mDb.BeginTransactionAsync()
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance - @amount WHERE ID = 1 AND Balance >= @amount")
        command.AddParameter "@amount", 10
        changed = Await command.ExecuteAsync()
        If changed <> 1 Then Err.Raise 513, "DataWorkbench", "The first account has fewer than 10 units."
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance + @amount WHERE ID = 2")
        command.AddParameter "@amount", 10
        Await command.ExecuteAsync()
        If chkRollback.Value Then
            Await transaction.RollbackAsync()
            lblStatus.Caption = "Rolled back: neither account changed."
        Else
            Await transaction.CommitAsync()
            lblStatus.Caption = "Committed: 10 units moved between accounts."
        End If
    Catch failure As McException
        lblStatus.Caption = "Transfer failed: " & failure.Message
    Finally
        If Not transaction Is Nothing Then transaction.Dispose
        SetBusy False
    End Try
    Await RefreshRows()
End Sub

Test the invariant, not just the label#

1. With rollback off, transfer once: expect balances 90 and 110.
2. With rollback on, transfer again: both balances must remain unchanged.
3. Repeat transfers until the first account cannot supply 10; expect a failure without a partial credit.
4. After each operation, inspect the reloaded data. The total must remain 200.

A label saying “Committed” is not the evidence. The refreshed records are. Try the complete transaction lesson.

Evolve the schema with new migration IDs#

DesktopApp projects can declare up to 64 migration files, each with a unique positive ID and nonempty SQL of at most 256 KiB. ApplyMigrationsAsync(App.Migrations) applies pending changes together and records their hashes. Running the same unchanged set again does not reapply it.

Do not edit a migration already applied to a user database. Its stored hash is part of the history; changing it is rejected. Add a new migration instead, test both a fresh database and an upgrade from the previous schema, and back up important data before the upgrade.

Approval and execution are different#

An AI stage can propose a migration file. Approving that addition is not the same as running it against your database. Review the SQL, preserve the previous data and test the application’s startup migration path separately. Use the database reference for backup and schema inspection operations.