Transactions

Transactions ensure that a group of database operations either all succeed together or all fail together, maintaining database consistency and integrity.

Transaction Basics

GRDB automatically wraps write calls in transactions:

try dbQueue.write { db in
    // Implicit transaction
    try Player(name: "Arthur", score: 100).insert(db)
    try Player(name: "Barbara", score: 1000).insert(db)
    // Transaction automatically commits if no error is thrown
}

Explicit Transactions

Use inTransaction for explicit control:

try dbQueue.inTransaction { db in
    try Player(name: "Arthur", score: 100).insert(db)
    try Player(name: "Barbara", score: 1000).insert(db)
    return .commit  // or .rollback
}

Transaction Completion

The inTransaction closure returns a TransactionCompletion:

enum TransactionCompletion {
    case commit
    case rollback
}

Commit
Rollback
Error Rollback

try dbQueue.inTransaction { db in
    try Player(name: "Arthur", score: 100).insert(db)
    // All changes are saved
    return .commit
}

try dbQueue.inTransaction { db in
    try Player(name: "Arthur", score: 100).insert(db)
    // Changes are discarded
    return .rollback
}

do {
    try dbQueue.inTransaction { db in
        try Player(name: "Arthur", score: 100).insert(db)
        throw CustomError()
        // Transaction automatically rolls back
    }
} catch {
    print("Transaction rolled back due to error")
}

Transaction Kinds

SQLite supports three transaction kinds:

enum TransactionKind {
    case deferred      // Default
    case immediate
    case exclusive
}

// Locks acquired on first read/write
try dbQueue.inTransaction(.deferred) { db in
    try Player.fetchAll(db)
    return .commit
}

When to Use Each Kind

Kind	Locking	Best For
Deferred	Lazy	Read-heavy transactions
Immediate	On begin	Mixed read/write transactions
Exclusive	Full lock	Write-heavy transactions, avoiding deadlocks

Savepoints

Savepoints create nested transactions:

try dbQueue.write { db in
    try Player(name: "Arthur", score: 100).insert(db)
    
    try db.inSavepoint {
        try Player(name: "Barbara", score: 1000).insert(db)
        try Player(name: "Charlie", score: 500).insert(db)
        return .commit
    }
    
    // Arthur is inserted, Barbara and Charlie are inserted
}

Rolling Back Savepoints

try dbQueue.write { db in
    try Player(name: "Arthur", score: 100).insert(db)
    
    try db.inSavepoint {
        try Player(name: "Barbara", score: 1000).insert(db)
        try Player(name: "Charlie", score: 500).insert(db)
        return .rollback  // Rollback this savepoint only
    }
    
    // Arthur is inserted, Barbara and Charlie are NOT inserted
}

Nested Savepoints

try dbQueue.write { db in
    try Player(name: "Arthur", score: 100).insert(db)
    
    try db.inSavepoint {
        try Player(name: "Barbara", score: 1000).insert(db)
        
        try db.inSavepoint {
            try Player(name: "Charlie", score: 500).insert(db)
            return .rollback  // Rollback only Charlie
        }
        
        return .commit  // Commit Barbara
    }
    
    // Arthur and Barbara are inserted, Charlie is NOT inserted
}

Write Without Transaction

Some operations need to execute without a transaction:

try dbQueue.writeWithoutTransaction { db in
    // No transaction - each statement auto-commits
    try Player(name: "Arthur", score: 100).insert(db)
    try Player(name: "Barbara", score: 1000).insert(db)
}

Without a transaction, each statement auto-commits. If an error occurs, previous statements are not rolled back.

When to Use

Schema Changes
Manual Transactions
Multiple Transactions

try dbQueue.writeWithoutTransaction { db in
    // Some SQLite statements can't run inside transactions
    try db.execute(sql: "VACUUM")
}

try dbQueue.writeWithoutTransaction { db in
    try db.execute(sql: "BEGIN IMMEDIATE")
    try Player(name: "Arthur", score: 100).insert(db)
    try db.execute(sql: "COMMIT")
}

try dbQueue.writeWithoutTransaction { db in
    try db.inTransaction {
        try Player(name: "Arthur", score: 100).insert(db)
        return .commit
    }
    
    try db.inTransaction {
        try Player(name: "Barbara", score: 1000).insert(db)
        return .commit
    }
}

DatabasePool Specifics

DatabasePool has additional write methods:

writeInTransaction

try dbPool.writeInTransaction { db in
    try Player(name: "Arthur", score: 100).insert(db)
    return .commit
}

barrierWriteWithoutTransaction

Barrier writes wait for all reads to complete:

try dbPool.barrierWriteWithoutTransaction { db in
    // Waits for all concurrent reads to finish
    try db.execute(sql: "DROP TABLE temp_table")
}

Barrier writes are useful when you need to ensure no concurrent reads are happening, such as when dropping tables or performing schema changes.

Transaction Hooks

Observe transaction lifecycle events:

try dbQueue.write { db in
    db.afterNextTransaction(onCommit: {
        print("Transaction committed!")
    }, onRollback: { _ in
        print("Transaction rolled back!")
    })
    
    try Player(name: "Arthur", score: 100).insert(db)
}

Multiple Hooks

try dbQueue.write { db in
    // First hook
    db.afterNextTransaction(onCommit: {
        print("Saving to UserDefaults")
        UserDefaults.standard.set(true, forKey: "dataChanged")
    }, onRollback: { _ in
        print("No changes to save")
    })
    
    // Second hook
    db.afterNextTransaction(onCommit: {
        print("Posting notification")
        NotificationCenter.default.post(name: .dataChanged, object: nil)
    })
    
    try Player(name: "Arthur", score: 100).insert(db)
}

Error Handling

do {
    try dbQueue.inTransaction { db in
        try Player(name: "Arthur", score: 100).insert(db)
        try Player(name: "Barbara", score: 1000).insert(db)
        return .commit
    }
} catch {
    print("Transaction failed: \(error)")
    // All changes automatically rolled back
}

Common Patterns

Atomic Batch Operations

func insertPlayers(_ players: [Player]) throws {
    try dbQueue.write { db in
        for player in players {
            try player.insert(db)
        }
        // All or nothing - if any insert fails, all are rolled back
    }
}

Conditional Commit

try dbQueue.inTransaction { db in
    try Player(name: "Arthur", score: 100).insert(db)
    
    // Validate state
    let totalPlayers = try Player.fetchCount(db)
    guard totalPlayers <= 1000 else {
        return .rollback  // Too many players
    }
    
    return .commit
}

Progressive Updates with Savepoints

try dbQueue.write { db in
    var successfulUpdates = 0
    
    for player in players {
        try db.inSavepoint {
            do {
                try player.insert(db)
                successfulUpdates += 1
                return .commit
            } catch {
                print("Failed to insert \(player.name)")
                return .rollback  // Skip this player, continue with others
            }
        }
    }
    
    print("Inserted \(successfulUpdates) players")
}

Transaction Retry Logic

func retryTransaction<T>(
    maxAttempts: Int = 3,
    operation: (Database) throws -> T
) throws -> T {
    var lastError: Error?
    
    for attempt in 1...maxAttempts {
        do {
            return try dbQueue.inTransaction { db in
                let result = try operation(db)
                return .commit
            }
        } catch let error as DatabaseError where error.resultCode == .SQLITE_BUSY {
            lastError = error
            print("Attempt \(attempt) failed, retrying...")
            Thread.sleep(forTimeInterval: 0.1)
            continue
        } catch {
            throw error
        }
    }
    
    throw lastError ?? DatabaseError(message: "Transaction failed")
}

Transaction Observation

Implement TransactionObserver for advanced observation:

class ChangeObserver: TransactionObserver {
    func observes(eventsOfKind eventKind: DatabaseEventKind) -> Bool {
        return true  // Observe all events
    }
    
    func databaseDidChange(with event: DatabaseEvent) {
        print("Changed: \(event.tableName)")
    }
    
    func databaseWillCommit() throws {
        print("About to commit")
    }
    
    func databaseDidCommit(_ db: Database) {
        print("Committed!")
    }
    
    func databaseDidRollback(_ db: Database) {
        print("Rolled back!")
    }
}

// Register observer
try dbQueue.write { db in
    db.add(transactionObserver: ChangeObserver())
}

Best Practices

Keep Transactions Short
Use Appropriate Transaction Kind
Avoid Nested Transactions

// ✅ GOOD: Short transaction
try dbQueue.write { db in
    try player.insert(db)
}

// ❌ BAD: Long transaction
try dbQueue.write { db in
    let data = downloadLargeFile()  // Don't do slow operations in transactions
    try processData(data, db: db)
}

// ✅ GOOD: Deferred for read-heavy
try dbQueue.inTransaction(.deferred) { db in
    let players = try Player.fetchAll(db)
    try updateScore(for: players.first!, db: db)
    return .commit
}

// ✅ GOOD: Immediate for write-heavy
try dbQueue.inTransaction(.immediate) { db in
    try insertManyPlayers(db: db)
    return .commit
}

// ✅ GOOD: Use savepoints for nesting
try dbQueue.write { db in
    try player1.insert(db)
    try db.inSavepoint {
        try player2.insert(db)
        return .commit
    }
}

// ❌ BAD: Don't nest transactions
try dbQueue.inTransaction { db in
    try dbQueue.inTransaction { db in  // ERROR!
        return .commit
    }
    return .commit
}

Performance Considerations

Batch Operations

Transactions significantly improve bulk insert performance:

// Slow: Individual transactions (implicit)
for player in players {
    try dbQueue.write { db in
        try player.insert(db)
    }
}

// Fast: Single transaction
try dbQueue.write { db in
    for player in players {
        try player.insert(db)
    }
}

Transaction Size

Balance transaction size with performance:

// Process in batches
let batchSize = 1000
for batch in players.chunked(into: batchSize) {
    try dbQueue.write { db in
        for player in batch {
            try player.insert(db)
        }
    }
}

Next Steps

Database Connections - Learn about DatabaseQueue and DatabasePool
Records - Learn about persistence methods
Concurrency - Learn about thread safety and async operations

Getting Started

Core Concepts

Records

Query Interface

Advanced Features

Guides

Transaction Basics

Explicit Transactions

Transaction Completion

Transaction Kinds

When to Use Each Kind

Savepoints

Rolling Back Savepoints

Nested Savepoints

Write Without Transaction

When to Use

DatabasePool Specifics

writeInTransaction

barrierWriteWithoutTransaction

Transaction Hooks

Multiple Hooks

Error Handling

Common Patterns

Atomic Batch Operations

Conditional Commit

Progressive Updates with Savepoints

Transaction Retry Logic

Transaction Observation

Best Practices

Performance Considerations

Batch Operations

Transaction Size

Next Steps

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Records

Query Interface

Advanced Features

Guides

​Transaction Basics

​Explicit Transactions

​Transaction Completion

​Transaction Kinds

​When to Use Each Kind

​Savepoints

​Rolling Back Savepoints

​Nested Savepoints

​Write Without Transaction

​When to Use

​DatabasePool Specifics

​writeInTransaction

​barrierWriteWithoutTransaction

​Transaction Hooks

​Multiple Hooks

​Error Handling

​Common Patterns

​Atomic Batch Operations

​Conditional Commit

​Progressive Updates with Savepoints

​Transaction Retry Logic

​Transaction Observation

​Best Practices

​Performance Considerations

​Batch Operations

​Transaction Size

​Next Steps

Build docs developers (and LLMs) love

Transaction Basics

Explicit Transactions

Transaction Completion

Transaction Kinds

When to Use Each Kind

Savepoints

Rolling Back Savepoints

Nested Savepoints

Write Without Transaction

When to Use

DatabasePool Specifics

writeInTransaction

barrierWriteWithoutTransaction

Transaction Hooks

Multiple Hooks

Error Handling

Common Patterns

Atomic Batch Operations

Conditional Commit

Progressive Updates with Savepoints

Transaction Retry Logic

Transaction Observation

Best Practices

Performance Considerations

Batch Operations

Transaction Size

Next Steps