Transaction Processing

This document details the transaction processing features of the cDataBase class, including transaction start, commit, rollback, etc.

---

Table of Contents

• Transaction Overview
• Transaction Methods
• Basic Usage
• Error Handling
• Nested Transactions
• Best Practices

---

Transaction Overview

What is a Transaction

A transaction is a set of database operations that either all succeed or all fail. Transactions have the following characteristics (ACID):

• Atomicity - All operations in a transaction either execute completely or not at all
• Consistency - Database maintains consistent state before and after transaction execution
• Isolation - Concurrent transactions are isolated from each other
• Durability - After transaction commit, data is permanently saved

Transaction States

Start Transaction
    │
    ▼
Execute Operation 1
    │
    ▼
Execute Operation 2
    │
    ▼
Execute Operation 3
    │
    ▼
Commit Transaction ──→ Success: All operations take effect
    │
    └──→ Failure: Auto rollback, all operations reverted

---

Transaction Methods

TransBegin Method

The TransBegin method starts a new transaction.

Syntax

Function TransBegin() As Boolean

Return Value

• True - Transaction started successfully
• False - Transaction start failed (view error via LastErr)

Example

' Start transaction
If db.TransBegin Then
    Debug.Print "Transaction started"
Else
    Debug.Print "Transaction start failed: " & db.LastErr
End If

TransCommit Method

The TransCommit method commits a transaction.

Syntax

Function TransCommit() As Boolean

Functionality

• Commit all operations in the transaction
• If commit fails, auto rollback
• Clear transaction flag

Return Value

• True - Commit successful
• False - Commit failed (already auto rolled back)

Example

' Commit transaction
If db.TransCommit Then
    Debug.Print "Transaction committed successfully"
Else
    Debug.Print "Transaction commit failed, auto rolled back: " & db.LastErr
End If

TransRollback Method

The TransRollback method rolls back a transaction.

Syntax

Function TransRollback() As Boolean

Functionality

• Revert all operations in the transaction
• Clear transaction flag

Return Value

• True - Rollback successful
• False - Rollback failed (view error via LastErr)

Example

' Rollback transaction
If db.TransRollback Then
    Debug.Print "Transaction rolled back"
Else
    Debug.Print "Rollback failed: " & db.LastErr
End If

---

Basic Usage

Example 1: Simple Transaction

' Start transaction
If db.TransBegin Then
    ' Execute operation 1
    If db.Sql("INSERT INTO users (name) VALUES ('John')").Exec Then
        ' Execute operation 2
        If db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec Then
            ' Commit transaction
            If db.TransCommit Then
                Debug.Print "Transaction committed successfully"
            Else
                Debug.Print "Commit failed, auto rolled back"
            End If
        Else
            ' Operation 2 failed, rollback
            db.TransRollback
        End If
    Else
        ' Operation 1 failed, rollback
        db.TransRollback
    End If
End If

Example 2: Using Error Handling

On Error GoTo ErrHandler

' Start transaction
If Not db.TransBegin Then
    Debug.Print "Transaction start failed: " & db.LastErr
    Exit Sub
End If

' Execute multiple operations
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("INSERT INTO users (name) VALUES ('Mary')").Exec
db.Sql("UPDATE users SET status = 'active' WHERE name = 'John'").Exec

' Commit transaction
If db.TransCommit Then
    Debug.Print "All operations successful"
Else
    Debug.Print "Commit failed: " & db.LastErr
End If

Exit Sub

ErrHandler:
' Error occurred, rollback transaction
db.TransRollback
Debug.Print "Error occurred, transaction rolled back: " & Err.Description

Example 3: Transfer Operation

' Transfer operation: Transfer 100 from account A to account B
Function TransferMoney(lFromAccount As Long, lToAccount As Long, dAmount As Double) As Boolean
    On Error GoTo ErrHandler

    ' Start transaction
    If Not db.TransBegin Then
        TransferMoney = False
        Exit Function
    End If

    ' Deduct amount from account A
    Dim sSql As String
    sSql = "UPDATE accounts SET balance = balance - " & dAmount & _
           " WHERE id = " & lFromAccount & " AND balance >= " & dAmount
    If Not db.Sql(sSql).Exec Then
        db.TransRollback
        TransferMoney = False
        Exit Function
    End If

    ' Check if records were updated
    Dim lAffected As Long
    If db.Sql("SELECT @@ROWCOUNT").Query Then
        If db.Rs.EOF Or db.Rs(0) = 0 Then
            db.TransRollback
            TransferMoney = False
            Exit Function
        End If
    End If

    ' Add amount to account B
    sSql = "UPDATE accounts SET balance = balance + " & dAmount & _
           " WHERE id = " & lToAccount
    If Not db.Sql(sSql).Exec Then
        db.TransRollback
        TransferMoney = False
        Exit Function
    End If

    ' Log transfer
    sSql = "INSERT INTO transfers (from_account, to_account, amount) VALUES (" & _
           lFromAccount & ", " & lToAccount & ", " & dAmount & ")"
    If Not db.Sql(sSql).Exec Then
        db.TransRollback
        TransferMoney = False
        Exit Function
    End If

    ' Commit transaction
    If db.TransCommit Then
        TransferMoney = True
    Else
        TransferMoney = False
    End If

    Exit Function

ErrHandler:
    db.TransRollback
    TransferMoney = False
End Function

---

Error Handling

Auto Rollback Mechanism

The library provides an auto rollback mechanism:

1. Auto rollback on commit failure - TransCommit automatically calls TransRollback when failed
2. Auto rollback on disconnect - Disconnect automatically rolls back incomplete transactions

' Auto rollback on commit failure
If db.TransBegin Then
    db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
    ' If commit fails, auto rollback
    If Not db.TransCommit Then
        Debug.Print "Commit failed, auto rolled back: " & db.LastErr
    End If
End If

Error Handling Example

Function ExecuteTransaction() As Boolean
    On Error GoTo ErrHandler

    ' Start transaction
    If Not db.TransBegin Then
        Debug.Print "Transaction start failed: " & db.LastErr
        ExecuteTransaction = False
        Exit Function
    End If

    ' Execute operation
    If Not db.Sql("INSERT INTO users (name) VALUES ('John')").Exec Then
        db.TransRollback
        Debug.Print "Operation failed: " & db.LastErr
        ExecuteTransaction = False
        Exit Function
    End If

    ' Commit transaction
    If db.TransCommit Then
        ExecuteTransaction = True
    Else
        Debug.Print "Commit failed: " & db.LastErr
        ExecuteTransaction = False
    End If

    Exit Function

ErrHandler:
    ' Exception occurred, rollback transaction
    db.TransRollback
    Debug.Print "Exception occurred, rolled back: " & Err.Description
    ExecuteTransaction = False
End Function

---

Nested Transactions

Notes

ADO supports nested transactions, but needs attention:

1. Nesting levels - ADO supports multi-level nested transactions
2. Commit order - Must commit in reverse order (inner first)
3. Rollback impact - Rollback affects all nesting levels

Nested Transaction Example

' Outer transaction
If db.TransBegin Then
    db.Sql("INSERT INTO users (name) VALUES ('User1')").Exec

    ' Inner transaction (nested)
    If db.TransBegin Then
        db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title1')").Exec

        ' Commit inner transaction
        If db.TransCommit Then
            Debug.Print "Inner transaction committed successfully"
        End If
    End If

    ' Commit outer transaction
    If db.TransCommit Then
        Debug.Print "Outer transaction committed successfully"
    End If
End If

---

Best Practices

1. Always Use Transactions for Multiple Related Operations

' Recommended: Use transactions to ensure consistency
db.TransBegin
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec
db.TransCommit

' Not recommended: Without transactions
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec
' If second operation fails, first operation already committed, data inconsistent

2. Check Return Value of Each Operation

' Recommended: Check each operation
db.TransBegin

If Not db.Sql("INSERT INTO users (name) VALUES ('John')").Exec Then
    db.TransRollback
    Exit Sub
End If

If Not db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec Then
    db.TransRollback
    Exit Sub
End If

db.TransCommit

3. Use Error Handling

' Recommended: Use error handling
On Error GoTo ErrHandler

db.TransBegin
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("INSERT INTO posts (user_id, title) VALUES (1, 'Title')").Exec
db.TransCommit

Exit Sub

ErrHandler:
    db.TransRollback
    Debug.Print "Error: " & Err.Description

4. Commit or Rollback Promptly

' Recommended: Commit or rollback immediately after operations
db.TransBegin
' ... execute operations ...
db.TransCommit  ' Or db.TransRollback

' Not recommended: Keep transaction open for long time
db.TransBegin
' ... execute operations ...
' Wait for user input (transaction remains open)
' ...
db.TransCommit

5. Use Transactions for Batch Operations

' Recommended: Use transactions for batch operations
db.TransBegin

Dim i As Long
For i = 1 To 1000
    If Not db.Sql("INSERT INTO users (name) VALUES ('User" & i & "')").Exec Then
        db.TransRollback
        Exit For
    End If
Next

If i > 1000 Then
    db.TransCommit
End If

---

Common Questions

Q1: Data not saved after transaction commit?

Cause: Database may not support transactions, or connection string configuration issue.

Solution:

• Check if database type supports transactions
• Verify connection string configuration

Q2: How to check transaction status?

' Note: Library internally uses IsTrans flag but does not expose it
' Can check by attempting to commit
If db.TransCommit Then
    Debug.Print "Transaction exists and committed successfully"
Else
    Debug.Print "No transaction or commit failed"
End If

Q3: Do transactions affect performance?

Answer: Yes, transactions lock resources and affect concurrent performance.

Recommendations:

• Minimize transaction duration
• Only include necessary operations
• Avoid long operations in transactions

---

Last Updated: 2026-01-21