0
# Transaction Management
1

2
SQLDelight's transaction management system provides ACID guarantees, nested transaction support, and automatic resource cleanup. It offers both synchronous and coroutine-based asynchronous transaction APIs with comprehensive error handling and lifecycle callbacks.
3

4
## Capabilities
5

6
### Synchronous Transaction Management
7

8
Execute operations within ACID transactions with automatic rollback on exceptions.
9

10
```kotlin { .api }
11
/**
12
 * A transaction-aware SqlDriver wrapper which can begin a Transaction on the current connection
13
 */
14
interface Transacter : TransacterBase {
15
    /**
16
     * Starts a Transaction and runs bodyWithReturn in that transaction
17
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
18
     * @param bodyWithReturn Lambda that executes within the transaction and returns a value
19
     * @returns The value returned by bodyWithReturn
20
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
21
     */
22
    fun <R> transactionWithResult(
23
        noEnclosing: Boolean = false,
24
        bodyWithReturn: TransactionWithReturn<R>.() -> R
25
    ): R
26
    
27
    /**
28
     * Starts a Transaction and runs body in that transaction
29
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
30
     * @param body Lambda that executes within the transaction
31
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
32
     */
33
    fun transaction(
34
        noEnclosing: Boolean = false,
35
        body: TransactionWithoutReturn.() -> Unit
36
    )
37
}
38
```
39

40
**Usage Examples:**
41

42
```kotlin
43
import app.cash.sqldelight.Transacter
44

45
// Simple transaction without return value
46
database.transaction {
47
    userQueries.insertUser("Alice", "alice@example.com")
48
    userQueries.updateLastLogin("Alice", System.currentTimeMillis())
49
    // Both operations succeed or both are rolled back
50
}
51

52
// Transaction with return value
53
val newUserId: Long = database.transactionWithResult {
54
    userQueries.insertUser("Bob", "bob@example.com")
55
    val userId = userQueries.lastInsertRowId().executeAsOne()
56
    profileQueries.createProfile(userId, "Bob's Profile")
57
    userId // Return the new user ID
58
}
59

60
// Explicit rollback with return value
61
val result: String = database.transactionWithResult {
62
    userQueries.insertUser("Charlie", "charlie@example.com")
63
    val user = userQueries.selectByEmail("charlie@example.com").executeAsOneOrNull()
64
    if (user == null) {
65
        rollback("User creation failed")
66
    }
67
    "User created successfully"
68
}
69

70
// Manual rollback without return value
71
database.transaction {
72
    val userCount = userQueries.countUsers().executeAsOne()
73
    if (userCount > 1000) {
74
        rollback() // Stop transaction without creating more users
75
    }
76
    userQueries.insertUser("David", "david@example.com")
77
}
78
```
79

80
### Asynchronous Transaction Management
81

82
Execute operations within transactions using Kotlin coroutines for non-blocking database access.
83

84
```kotlin { .api }
85
/**
86
 * A transaction-aware SqlDriver wrapper which can begin a Transaction on the current connection
87
 */
88
interface SuspendingTransacter : TransacterBase {
89
    /**
90
     * Starts a Transaction and runs bodyWithReturn in that transaction
91
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
92
     * @param bodyWithReturn Suspending lambda that executes within the transaction and returns a value
93
     * @returns The value returned by bodyWithReturn
94
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
95
     */
96
    suspend fun <R> transactionWithResult(
97
        noEnclosing: Boolean = false,
98
        bodyWithReturn: suspend SuspendingTransactionWithReturn<R>.() -> R
99
    ): R
100
    
101
    /**
102
     * Starts a Transaction and runs body in that transaction
103
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
104
     * @param body Suspending lambda that executes within the transaction
105
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
106
     */
107
    suspend fun transaction(
108
        noEnclosing: Boolean = false,
109
        body: suspend SuspendingTransactionWithoutReturn.() -> Unit
110
    )
111
}
112
```
113

114
**Usage Examples:**
115

116
```kotlin
117
import app.cash.sqldelight.SuspendingTransacter
118
import kotlinx.coroutines.delay
119

120
// Async transaction without return value
121
suspendingDatabase.transaction {
122
    userQueries.insertUser("Alice", "alice@example.com")
123
    delay(100) // Simulate async operation
124
    userQueries.updateLastLogin("Alice", System.currentTimeMillis())
125
}
126

127
// Async transaction with return value
128
val newUserId: Long = suspendingDatabase.transactionWithResult {
129
    userQueries.insertUser("Bob", "bob@example.com")
130
    delay(50) // Simulate async validation
131
    val userId = userQueries.lastInsertRowId().executeAsOne()
132
    profileQueries.createProfile(userId, "Bob's Profile")
133
    userId
134
}
135

136
// Conditional rollback in async context
137
val result: String = suspendingDatabase.transactionWithResult {
138
    userQueries.insertUser("Charlie", "charlie@example.com")
139
    val validationResult = validateUserAsync("charlie@example.com")
140
    if (!validationResult.isValid) {
141
        rollback("Validation failed: ${validationResult.error}")
142
    }
143
    "User created successfully"
144
}
145
```
146

147
### Transaction Context Interfaces
148

149
Interfaces that define the transaction execution context and available operations.
150

151
```kotlin { .api }
152
/**
153
 * Transaction context that supports returning values and nested transactions
154
 */
155
interface TransactionWithReturn<R> : TransactionCallbacks {
156
    /**
157
     * Rolls back this transaction with a return value
158
     */
159
    fun rollback(returnValue: R): Nothing
160
    
161
    /**
162
     * Begin an inner transaction that returns a value
163
     */
164
    fun <R> transaction(body: TransactionWithReturn<R>.() -> R): R
165
}
166

167
/**
168
 * Transaction context that doesn't return values but supports nested transactions
169
 */
170
interface TransactionWithoutReturn : TransactionCallbacks {
171
    /**
172
     * Rolls back this transaction
173
     */
174
    fun rollback(): Nothing
175
    
176
    /**
177
     * Begin an inner transaction without return value
178
     */
179
    fun transaction(body: TransactionWithoutReturn.() -> Unit)
180
}
181

182
/**
183
 * Suspending transaction context that supports returning values and nested transactions
184
 */
185
interface SuspendingTransactionWithReturn<R> : TransactionCallbacks {
186
    /**
187
     * Rolls back this transaction with a return value
188
     */
189
    fun rollback(returnValue: R): Nothing
190
    
191
    /**
192
     * Begin an inner suspending transaction that returns a value
193
     */
194
    suspend fun <R> transaction(body: suspend SuspendingTransactionWithReturn<R>.() -> R): R
195
}
196

197
/**
198
 * Suspending transaction context that doesn't return values but supports nested transactions
199
 */
200
interface SuspendingTransactionWithoutReturn : TransactionCallbacks {
201
    /**
202
     * Rolls back this transaction
203
     */
204
    fun rollback(): Nothing
205
    
206
    /**
207
     * Begin an inner suspending transaction without return value
208
     */
209
    suspend fun transaction(body: suspend SuspendingTransactionWithoutReturn.() -> Unit)
210
}
211
```
212

213
### Transaction Implementation Classes
214

215
Base implementation classes for creating custom database classes with transaction support.
216

217
```kotlin { .api }
218
/**
219
 * Base implementation for synchronous transaction management
220
 */
221
abstract class TransacterImpl(driver: SqlDriver) : BaseTransacterImpl(driver), Transacter
222

223
/**
224
 * Base implementation for asynchronous transaction management  
225
 */
226
abstract class SuspendingTransacterImpl(driver: SqlDriver) : BaseTransacterImpl(driver), SuspendingTransacter
227

228
/**
229
 * Common base class for both synchronous and asynchronous transacter implementations
230
 */
231
abstract class BaseTransacterImpl(protected val driver: SqlDriver) {
232
    /**
233
     * Notify query listeners that results have changed for the given identifier and tables
234
     */
235
    protected fun notifyQueries(identifier: Int, tableProvider: ((String) -> Unit) -> Unit)
236
    
237
    /**
238
     * Create SQL parameter placeholders for the given count
239
     */
240
    protected fun createArguments(count: Int): String
241
}
242
```
243

244
**Usage Examples:**
245

246
```kotlin
247
import app.cash.sqldelight.TransacterImpl
248

249
// Custom database implementation
250
abstract class MyDatabase : TransacterImpl(driver) {
251
    // Generated query interfaces would be properties here
252
    abstract val userQueries: UserQueries
253
    abstract val profileQueries: ProfileQueries
254
    
255
    companion object {
256
        fun create(driver: SqlDriver): MyDatabase {
257
            return MyDatabaseImpl(driver)
258
        }
259
    }
260
}
261

262
// For async databases  
263
abstract class MyAsyncDatabase : SuspendingTransacterImpl(driver) {
264
    abstract val userQueries: UserQueries
265
    abstract val profileQueries: ProfileQueries
266
    
267
    companion object {
268
        fun create(driver: SqlDriver): MyAsyncDatabase {
269
            return MyAsyncDatabaseImpl(driver) 
270
        }
271
    }
272
}
273
```
274

275
### Transaction Lifecycle and Callbacks
276

277
Manage transaction lifecycle with commit and rollback callbacks.
278

279
```kotlin { .api }
280
/**
281
 * Base interface for transaction callback management
282
 */
283
interface TransactionCallbacks {
284
    /**
285
     * Queues function to be run after this transaction successfully commits
286
     */
287
    fun afterCommit(function: () -> Unit)
288
    
289
    /**
290
     * Queues function to be run after this transaction rolls back
291
     */
292
    fun afterRollback(function: () -> Unit)
293
}
294

295
/**
296
 * A SQL transaction with lifecycle management and thread confinement
297
 */
298
abstract class Transacter.Transaction : TransactionCallbacks {
299
    /**
300
     * The parent transaction, if there is any
301
     */
302
    protected abstract val enclosingTransaction: Transaction?
303
    
304
    /**
305
     * Signal to the underlying SQL driver that this transaction should be finished
306
     * @param successful Whether the transaction completed successfully or not
307
     */
308
    protected abstract fun endTransaction(successful: Boolean): QueryResult<Unit>
309
    
310
    /**
311
     * Queues function to be run after this transaction successfully commits
312
     */
313
    override fun afterCommit(function: () -> Unit)
314
    
315
    /**
316
     * Queues function to be run after this transaction rolls back
317
     */
318
    override fun afterRollback(function: () -> Unit)
319
}
320
```
321

322
**Usage Examples:**
323

324
```kotlin
325
import app.cash.sqldelight.TransactionCallbacks
326

327
// Using commit and rollback callbacks
328
database.transaction {
329
    afterCommit {
330
        println("Transaction committed successfully")
331
        notifyUI("Data saved")
332
        clearCache()
333
    }
334
    
335
    afterRollback {
336
        println("Transaction was rolled back")
337
        notifyUI("Save failed")
338
        resetFormData()
339
    }
340
    
341
    userQueries.insertUser("Alice", "alice@example.com")
342
    profileQueries.createProfile(userId = 1, name = "Alice Profile")
343
}
344

345
// Multiple callbacks
346
database.transaction {
347
    afterCommit {
348
        updateSearchIndex()
349
    }
350
    
351
    afterCommit {
352
        sendNotificationEmail()
353
    }
354
    
355
    afterRollback {
356
        logError("User creation failed")
357
    }
358
    
359
    userQueries.insertUser("Bob", "bob@example.com")
360
}
361
```
362

363
### Nested Transactions
364

365
Execute nested transactions with proper isolation and error handling.
366

367
**Usage Examples:**
368

369
```kotlin
370
// Nested transactions
371
database.transaction {
372
    userQueries.insertUser("Parent", "parent@example.com")
373
    
374
    // Inner transaction - will be committed with outer transaction
375
    transaction {
376
        profileQueries.createProfile(1, "Parent Profile")
377
        settingsQueries.createUserSettings(1, theme = "dark")
378
    }
379
    
380
    // Another inner transaction
381
    val configId: Long = transactionWithResult {
382
        configQueries.insertConfig("user_1_config")
383
        configQueries.lastInsertRowId().executeAsOne()
384
    }
385
    
386
    userQueries.updateConfigId(1, configId)
387
}
388

389
// Independent nested transaction
390
database.transactionWithResult {
391
    val userId = userQueries.insertUser("Independent", "independent@example.com")
392
    
393
    // This inner transaction can be rolled back independently
394
    try {
395
        transaction(noEnclosing = true) {
396
            riskyQueries.performRiskyOperation(userId)
397
        }
398
    } catch (e: IllegalStateException) {
399
        // Inner transaction conflicts are handled separately
400
        println("Inner transaction failed: ${e.message}")
401
    }
402
    
403
    userId
404
}
405
```
406

407
### Transaction Error Handling
408

409
Handle transaction failures and implement proper error recovery strategies.
410

411
**Usage Examples:**
412

413
```kotlin
414
// Basic error handling
415
try {
416
    database.transaction {
417
        userQueries.insertUser("Test", "test@example.com")
418
        // This will cause a constraint violation if email already exists
419
        userQueries.insertUser("Test2", "test@example.com") 
420
    }
421
} catch (e: SQLException) {
422
    println("Transaction failed: ${e.message}")
423
    // Transaction was automatically rolled back
424
}
425

426
// Error handling with rollback callbacks
427
database.transaction {
428
    afterRollback {
429
        println("Cleaning up after transaction failure")
430
        cleanupTempFiles()
431
        resetState()
432
    }
433
    
434
    try {
435
        userQueries.insertUser("Risky", "risky@example.com")
436
        performRiskyOperation()
437
    } catch (e: RiskyOperationException) {
438
        // Explicit rollback
439
        rollback()
440
    }
441
}
442

443
// Conditional operations with manual rollback
444
val result = database.transactionWithResult<String> {
445
    val user = userQueries.insertUser("Conditional", "conditional@example.com")
446
    val validation = validateUser(user)
447
    
448
    if (!validation.isValid) {
449
        rollback("Validation failed: ${validation.errors.joinToString()}")
450
    }
451
    
452
    "User created successfully"
453
}
454
```
455

456
### Thread Safety and Confinement
457

458
Transaction objects are confined to the thread they were created on and cannot be accessed from other threads. SQLDelight uses internal thread identification mechanisms to enforce this constraint.
459

460
```kotlin { .api }
461
/**
462
 * Internal function for thread confinement checking
463
 * Used by transaction implementations to ensure thread safety
464
 * Note: This is an internal API and should not be used directly
465
 */
466
internal expect fun currentThreadId(): Long
467
```
468

469
**Usage Examples:**
470

471
```kotlin
472
// Correct usage - all operations on same thread
473
database.transaction {
474
    userQueries.insertUser("ThreadSafe", "safe@example.com")
475
    val userId = userQueries.lastInsertRowId().executeAsOne()
476
    profileQueries.createProfile(userId, "Profile")
477
}
478

479
// Incorrect usage - would throw IllegalStateException
480
database.transaction {
481
    val transaction = this
482
    
483
    // This would fail - transaction used from different thread
484
    Thread {
485
        try {
486
            transaction.afterCommit { 
487
                println("This will throw an exception")
488
            }
489
        } catch (e: IllegalStateException) {
490
            println("Cannot access transaction from different thread: ${e.message}")
491
        }
492
    }.start()
493
}
494

495
// Async operations should use SuspendingTransacter instead
496
suspendingDatabase.transaction {
497
    userQueries.insertUser("Async", "async@example.com")
498
    // This is safe because the coroutine context maintains thread affinity
499
    withContext(Dispatchers.IO) {
500
        // Async operations within the same coroutine scope
501
    }
502
}
503
```