0
# Transaction Management
1

2
Interactive transaction support with commit/rollback control for complex multi-statement operations requiring atomicity and consistency.
3

4
## Capabilities
5

6
### Transaction Creation
7

8
Start interactive transactions with different isolation modes for fine-grained control over database operations.
9

10
```typescript { .api }
11
/**
12
 * Start an interactive transaction
13
 * @param mode - Transaction mode (default: "write") 
14
 * @returns Promise resolving to Transaction instance
15
 */
16
transaction(mode?: TransactionMode): Promise<Transaction>;
17
```
18

19
**Usage Examples:**
20

21
```typescript
22
import { createClient } from "@libsql/client";
23

24
const client = createClient({ url: "file:database.db" });
25

26
// Write transaction (default)
27
const writeTxn = await client.transaction("write");
28

29
// Read-only transaction for consistent snapshots
30
const readTxn = await client.transaction("read");
31

32
// Deferred transaction (starts as read, upgrades to write as needed)
33
const deferredTxn = await client.transaction("deferred");
34
```
35

36
### Transaction Execution
37

38
Execute SQL statements within the transaction context with full atomicity guarantees.
39

40
```typescript { .api }
41
/**
42
 * Execute an SQL statement within the transaction
43
 * @param stmt - Statement with SQL and optional arguments
44
 * @returns Promise resolving to query results
45
 */
46
execute(stmt: InStatement): Promise<ResultSet>;
47
```
48

49
**Usage Examples:**
50

51
```typescript
52
const transaction = await client.transaction("write");
53

54
try {
55
  // Execute statements within transaction
56
  const user = await transaction.execute({
57
    sql: "INSERT INTO users (name, email) VALUES (?, ?)",
58
    args: ["Alice", "alice@example.com"]
59
  });
60
  
61
  const userId = user.lastInsertRowid;
62
  
63
  await transaction.execute({
64
    sql: "INSERT INTO user_preferences (user_id, theme) VALUES (?, ?)",
65
    args: [userId, "dark"]
66
  });
67
  
68
  // Commit if all operations succeed
69
  await transaction.commit();
70
} catch (error) {
71
  // Rollback on any error
72
  await transaction.rollback();
73
} finally {
74
  // Always close transaction to free resources
75
  transaction.close();
76
}
77
```
78

79
### Transaction Batch Operations
80

81
Execute multiple statements atomically within a transaction for better performance.
82

83
```typescript { .api }
84
/**
85
 * Execute a batch of statements within the transaction
86
 * @param stmts - Array of statements to execute
87
 * @returns Promise resolving to array of results
88
 */
89
batch(stmts: Array<InStatement>): Promise<Array<ResultSet>>;
90
```
91

92
**Usage Examples:**
93

94
```typescript
95
const transaction = await client.transaction("write");
96

97
try {
98
  // Batch multiple related operations
99
  const results = await transaction.batch([
100
    {
101
      sql: "UPDATE accounts SET balance = balance - ? WHERE id = ?",
102
      args: [100, 1] // Debit account 1
103
    },
104
    {
105
      sql: "UPDATE accounts SET balance = balance + ? WHERE id = ?", 
106
      args: [100, 2] // Credit account 2
107
    },
108
    {
109
      sql: "INSERT INTO transactions (from_account, to_account, amount) VALUES (?, ?, ?)",
110
      args: [1, 2, 100] // Record transaction
111
    }
112
  ]);
113
  
114
  await transaction.commit();
115
  console.log("Transfer completed successfully");
116
} finally {
117
  transaction.close();
118
}
119
```
120

121
### Multiple Statement Execution
122

123
Execute multiple semicolon-separated statements within the transaction.
124

125
```typescript { .api }
126
/**
127
 * Execute multiple semicolon-separated SQL statements within transaction
128
 * @param sql - SQL script with multiple statements
129
 * @returns Promise resolving when all statements complete
130
 */
131
executeMultiple(sql: string): Promise<void>;
132
```
133

134
**Usage Examples:**
135

136
```typescript
137
const transaction = await client.transaction("write");
138

139
try {
140
  await transaction.executeMultiple(`
141
    CREATE TEMPORARY TABLE temp_data (id INTEGER, value TEXT);
142
    INSERT INTO temp_data VALUES (1, 'test'), (2, 'data');
143
    INSERT INTO main_table SELECT * FROM temp_data;
144
    DROP TABLE temp_data;
145
  `);
146
  
147
  await transaction.commit();
148
} finally {
149
  transaction.close();
150
}
151
```
152

153
### Transaction Control
154

155
Commit or rollback transaction changes with explicit control over when changes are applied.
156

157
```typescript { .api }
158
/**
159
 * Commit all changes made in this transaction
160
 * Makes all changes permanent and closes the transaction
161
 */
162
commit(): Promise<void>;
163

164
/**
165
 * Roll back all changes made in this transaction
166
 * Discards all changes and closes the transaction  
167
 */
168
rollback(): Promise<void>;
169

170
/**
171
 * Close the transaction
172
 * Rolls back if not already committed
173
 */
174
close(): void;
175

176
/** Whether the transaction is closed */
177
readonly closed: boolean;
178
```
179

180
**Usage Examples:**
181

182
```typescript
183
const transaction = await client.transaction("write");
184

185
try {
186
  await transaction.execute({
187
    sql: "INSERT INTO users (name) VALUES (?)",
188
    args: ["Test User"]
189
  });
190
  
191
  // Explicitly commit changes
192
  await transaction.commit();
193
  console.log("Transaction committed successfully");
194
  
195
} catch (error) {
196
  // Explicitly rollback on error
197
  await transaction.rollback();
198
  console.log("Transaction rolled back due to error:", error);
199
  
200
} finally {
201
  // Close always safe to call, even after commit/rollback
202
  transaction.close();
203
}
204

205
// Check transaction state
206
console.log(transaction.closed); // true after commit/rollback/close
207
```
208

209
### Transaction Patterns
210

211
Common patterns for safe transaction handling:
212

213
**Basic Transaction Pattern:**
214

215
```typescript
216
const transaction = await client.transaction("write");
217
try {
218
  // Perform operations
219
  await transaction.execute("INSERT INTO ...", args);
220
  await transaction.commit();
221
} finally {
222
  transaction.close(); // Always close to free resources
223
}
224
```
225

226
**Conditional Commit Pattern:**
227

228
```typescript
229
const transaction = await client.transaction("write");
230
let shouldCommit = false;
231

232
try {
233
  const result = await transaction.execute("SELECT ...", args);
234
  
235
  if (result.rows.length > 0) {
236
    await transaction.execute("UPDATE ...", updateArgs);
237
    shouldCommit = true;
238
  }
239
  
240
  if (shouldCommit) {
241
    await transaction.commit();
242
  } else {
243
    await transaction.rollback();
244
  }
245
} finally {
246
  transaction.close();
247
}
248
```
249

250
**Nested Operation Pattern:**
251

252
```typescript
253
async function transferMoney(fromId: number, toId: number, amount: number) {
254
  const transaction = await client.transaction("write");
255
  
256
  try {
257
    // Check sufficient balance
258
    const balance = await transaction.execute({
259
      sql: "SELECT balance FROM accounts WHERE id = ?",
260
      args: [fromId]
261
    });
262
    
263
    if (balance.rows[0].balance < amount) {
264
      throw new Error("Insufficient funds");
265
    }
266
    
267
    // Perform transfer
268
    await transaction.batch([
269
      {
270
        sql: "UPDATE accounts SET balance = balance - ? WHERE id = ?",
271
        args: [amount, fromId]
272
      },
273
      {
274
        sql: "UPDATE accounts SET balance = balance + ? WHERE id = ?",
275
        args: [amount, toId]
276
      }
277
    ]);
278
    
279
    await transaction.commit();
280
    return true;
281
    
282
  } catch (error) {
283
    await transaction.rollback();
284
    throw error;
285
  } finally {
286
    transaction.close();
287
  }
288
}
289
```
290

291
## Transaction Modes
292

293
```typescript { .api }
294
/**
295
 * Transaction isolation and locking behavior
296
 * - "write": READ-WRITE transaction with immediate lock, blocks other writes
297
 * - "read": READ-ONLY transaction, allows concurrent reads, lower latency
298
 * - "deferred": Starts as read-only, upgrades to write on first write operation
299
 */
300
type TransactionMode = "write" | "read" | "deferred";
301
```
302

303
**Mode Selection Guidelines:**
304

305
- **"read"**: Use for read-only operations requiring consistent snapshots
306
- **"write"**: Use when you know you'll be making changes
307
- **"deferred"**: Use when you might or might not make changes based on read results
308

309
## Error Handling
310

311
Transactions can fail at various points. Common transaction-related error codes include `TRANSACTION_CLOSED` and `CLIENT_CLOSED`. For a complete list of error codes and handling examples, see [Client Configuration - Error Handling](./client-configuration.md#error-handling).
312

313
```typescript
314
import { LibsqlError } from "@libsql/client";
315

316
try {
317
  const transaction = await client.transaction("write");
318
  await transaction.execute("INVALID SQL");
319
} catch (error) {
320
  if (error instanceof LibsqlError) {
321
    console.log(`Transaction error [${error.code}]: ${error.message}`);
322
  }
323
}
324
```
325

326
## Performance Considerations
327

328
- **Batch Operations**: Use `batch()` instead of multiple `execute()` calls for better performance
329
- **Transaction Duration**: Keep transactions short to avoid blocking other operations
330
- **Read Transactions**: Use read-only transactions when possible for better concurrency
331
- **Connection Reuse**: Transactions use dedicated connections; close promptly to free resources