0
# Transaction Management
1

2
Comprehensive transaction control including commit, rollback, and distributed transaction support.
3

4
## Capabilities
5

6
### Basic Transaction Control
7

8
Standard transaction operations for database consistency.
9

10
```javascript { .api }
11
/**
12
 * Commits the current transaction
13
 * @returns Promise that resolves when transaction is committed
14
 */
15
commit(): Promise<void>;
16

17
/**
18
 * Rolls back the current transaction
19
 * @returns Promise that resolves when transaction is rolled back
20
 */
21
rollback(): Promise<void>;
22
```
23

24
**Usage Examples:**
25

26
```javascript
27
const oracledb = require('oracledb');
28

29
// Manual transaction control
30
oracledb.autoCommit = false; // Disable auto-commit
31

32
const connection = await oracledb.getConnection(config);
33

34
try {
35
  // Execute multiple related operations
36
  await connection.execute(
37
    'INSERT INTO orders (order_id, customer_id, total) VALUES (:1, :2, :3)',
38
    [1001, 'CUST001', 199.99]
39
  );
40
  
41
  await connection.execute(
42
    'INSERT INTO order_items (order_id, product_id, quantity) VALUES (:1, :2, :3)',
43
    [1001, 'PROD001', 2]
44
  );
45
  
46
  await connection.execute(
47
    'UPDATE inventory SET quantity = quantity - :1 WHERE product_id = :2',
48
    [2, 'PROD001']
49
  );
50
  
51
  // All operations successful - commit
52
  await connection.commit();
53
  console.log('Transaction committed successfully');
54
  
55
} catch (error) {
56
  // Error occurred - rollback
57
  await connection.rollback();
58
  console.error('Transaction rolled back:', error.message);
59
  throw error;
60
  
61
} finally {
62
  await connection.close();
63
}
64
```
65

66
### Distributed Transactions (TPC/XA)
67

68
Two-Phase Commit protocol support for distributed transactions across multiple databases.
69

70
```javascript { .api }
71
/**
72
 * Begins a distributed transaction
73
 * @param xid - Transaction identifier
74
 * @param flags - Optional transaction flags
75
 * @param timeout - Optional timeout in seconds
76
 * @returns Promise that resolves when transaction begins
77
 */
78
tpcBegin(xid: string, flags?: number, timeout?: number): Promise<void>;
79

80
/**
81
 * Ends a distributed transaction branch
82
 * @param xid - Transaction identifier
83
 * @param flags - Optional end flags
84
 * @returns Promise that resolves when transaction branch ends
85
 */
86
tpcEnd(xid: string, flags?: number): Promise<void>;
87

88
/**
89
 * Prepares a distributed transaction for commit
90
 * @param xid - Transaction identifier
91
 * @returns Promise resolving to boolean indicating if transaction is read-only
92
 */
93
tpcPrepare(xid: string): Promise<boolean>;
94

95
/**
96
 * Commits a distributed transaction
97
 * @param xid - Transaction identifier
98
 * @param onePhase - Whether to use one-phase commit
99
 * @returns Promise resolving to boolean indicating success
100
 */
101
tpcCommit(xid: string, onePhase?: boolean): Promise<boolean>;
102

103
/**
104
 * Rolls back a distributed transaction
105
 * @param xid - Transaction identifier
106
 * @returns Promise that resolves when transaction is rolled back
107
 */
108
tpcRollback(xid: string): Promise<void>;
109

110
/**
111
 * Forgets a distributed transaction
112
 * @param xid - Transaction identifier
113
 * @returns Promise that resolves when transaction is forgotten
114
 */
115
tpcForget(xid: string): Promise<void>;
116

117
/**
118
 * Recovers distributed transactions
119
 * @param flags - Recovery flags
120
 * @returns Promise resolving to array of transaction identifiers
121
 */
122
tpcRecover(flags?: number): Promise<string[]>;
123
```
124

125
**Usage Examples:**
126

127
```javascript
128
// Distributed transaction example with two connections
129
const connection1 = await oracledb.getConnection(config1);
130
const connection2 = await oracledb.getConnection(config2);
131

132
const xid = 'distributed-tx-' + Date.now();
133

134
try {
135
  // Begin distributed transaction on both connections
136
  await connection1.tpcBegin(xid, oracledb.TPC_BEGIN_NEW);
137
  await connection2.tpcBegin(xid, oracledb.TPC_BEGIN_NEW);
138
  
139
  // Perform operations on both databases
140
  await connection1.execute(
141
    'UPDATE accounts SET balance = balance - :amount WHERE account_id = :id',
142
    { amount: 1000, id: 'ACC001' }
143
  );
144
  
145
  await connection2.execute(
146
    'UPDATE accounts SET balance = balance + :amount WHERE account_id = :id',
147
    { amount: 1000, id: 'ACC002' }
148
  );
149
  
150
  // End transaction branches
151
  await connection1.tpcEnd(xid);
152
  await connection2.tpcEnd(xid);
153
  
154
  // Prepare both branches
155
  const readOnly1 = await connection1.tpcPrepare(xid);
156
  const readOnly2 = await connection2.tpcPrepare(xid);
157
  
158
  // Commit both branches
159
  await connection1.tpcCommit(xid);
160
  await connection2.tpcCommit(xid);
161
  
162
  console.log('Distributed transaction committed successfully');
163
  
164
} catch (error) {
165
  // Rollback both branches on error
166
  try {
167
    await connection1.tpcRollback(xid);
168
    await connection2.tpcRollback(xid);
169
  } catch (rollbackError) {
170
    console.error('Rollback error:', rollbackError);
171
  }
172
  
173
  console.error('Distributed transaction failed:', error.message);
174
  throw error;
175
  
176
} finally {
177
  await connection1.close();
178
  await connection2.close();
179
}
180
```
181

182
### Sessionless Transactions
183

184
Transactions that can span multiple connection sessions.
185

186
```javascript { .api }
187
/**
188
 * Begins a sessionless transaction
189
 * @param options - Transaction options
190
 * @returns Promise resolving to transaction ID
191
 */
192
beginSessionlessTransaction(options?: SessionlessTransactionOptions): Promise<Buffer>;
193

194
/**
195
 * Resumes a sessionless transaction
196
 * @param transactionId - Transaction ID from beginSessionlessTransaction
197
 * @param options - Resume options
198
 * @returns Promise that resolves when transaction is resumed
199
 */
200
resumeSessionlessTransaction(transactionId: Buffer, options?: SessionlessTransactionOptions): Promise<void>;
201

202
interface SessionlessTransactionOptions {
203
  timeout?: number;
204
}
205
```
206

207
**Usage Examples:**
208

209
```javascript
210
// Begin sessionless transaction
211
const connection1 = await oracledb.getConnection(config);
212

213
const transactionId = await connection1.beginSessionlessTransaction({
214
  timeout: 300 // 5 minutes
215
});
216

217
// Perform some operations
218
await connection1.execute(
219
  'INSERT INTO temp_data (id, data) VALUES (:1, :2)',
220
  [1, 'Transaction data']
221
);
222

223
// Close first connection
224
await connection1.close();
225

226
// Later, resume transaction with different connection
227
const connection2 = await oracledb.getConnection(config);
228

229
await connection2.resumeSessionlessTransaction(transactionId);
230

231
// Continue transaction
232
await connection2.execute(
233
  'UPDATE temp_data SET data = :1 WHERE id = :2',
234
  ['Updated data', 1]
235
);
236

237
// Commit the sessionless transaction
238
await connection2.commit();
239
await connection2.close();
240

241
console.log('Sessionless transaction completed');
242
```
243

244
### Transaction Properties
245

246
Monitor transaction state and properties.
247

248
```javascript { .api }
249
interface Connection {
250
  // Transaction state (read-only)
251
  transactionInProgress: boolean;
252
}
253
```
254

255
**Usage Examples:**
256

257
```javascript
258
const connection = await oracledb.getConnection(config);
259

260
console.log('Transaction in progress:', connection.transactionInProgress); // false
261

262
// Start a transaction
263
await connection.execute('INSERT INTO test (id) VALUES (1)');
264

265
console.log('Transaction in progress:', connection.transactionInProgress); // true
266

267
// Commit transaction
268
await connection.commit();
269

270
console.log('Transaction in progress:', connection.transactionInProgress); // false
271
```
272

273
### Auto-Commit Configuration
274

275
Configure automatic transaction commitment behavior.
276

277
```javascript { .api }
278
// Global auto-commit setting
279
oracledb.autoCommit: boolean;
280

281
// Per-execute auto-commit
282
interface ExecuteOptions {
283
  autoCommit?: boolean;
284
}
285
```
286

287
**Usage Examples:**
288

289
```javascript
290
// Global configuration
291
oracledb.autoCommit = true; // All operations auto-commit
292

293
// Per-operation override
294
await connection.execute(
295
  'INSERT INTO logs (message) VALUES (:1)',
296
  ['Debug message'],
297
  { autoCommit: false } // Don't auto-commit this operation
298
);
299

300
// Batch operations with manual commit
301
oracledb.autoCommit = false;
302

303
await connection.execute('INSERT INTO batch (id) VALUES (1)');
304
await connection.execute('INSERT INTO batch (id) VALUES (2)');
305
await connection.execute('INSERT INTO batch (id) VALUES (3)');
306

307
// Commit all at once
308
await connection.commit();
309

310
// Or use auto-commit for the entire batch
311
await connection.executeMany(
312
  'INSERT INTO batch (id, data) VALUES (:1, :2)',
313
  [[1, 'data1'], [2, 'data2'], [3, 'data3']],
314
  { autoCommit: true }
315
);
316
```
317

318
### Transaction Constants
319

320
```javascript { .api }
321
// TPC/XA begin flags
322
const TPC_BEGIN_JOIN = 0x00000002;
323
const TPC_BEGIN_NEW = 0x00000001;
324
const TPC_BEGIN_PROMOTE = 0x00000008;
325
const TPC_BEGIN_RESUME = 0x00000004;
326

327
// TPC/XA end flags
328
const TPC_END_NORMAL = 0;
329
const TPC_END_SUSPEND = 0x00100000;
330
```
331

332
**Usage Examples:**
333

334
```javascript
335
// Join existing distributed transaction
336
await connection.tpcBegin(xid, oracledb.TPC_BEGIN_JOIN);
337

338
// Suspend transaction branch
339
await connection.tpcEnd(xid, oracledb.TPC_END_SUSPEND);
340

341
// Resume suspended transaction branch
342
await connection.tpcBegin(xid, oracledb.TPC_BEGIN_RESUME);
343

344
// Normal transaction end
345
await connection.tpcEnd(xid, oracledb.TPC_END_NORMAL);
346
```
347

348
### Error Handling and Recovery
349

350
Common patterns for transaction error handling and recovery.
351

352
**Usage Examples:**
353

354
```javascript
355
// Robust transaction with retry logic
356
async function executeWithRetry(connection, operations, maxRetries = 3) {
357
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
358
    try {
359
      // Execute all operations
360
      for (const operation of operations) {
361
        await connection.execute(operation.sql, operation.binds);
362
      }
363
      
364
      // Commit if all successful
365
      await connection.commit();
366
      console.log('Transaction completed successfully');
367
      return;
368
      
369
    } catch (error) {
370
      await connection.rollback();
371
      
372
      if (attempt < maxRetries && isRetryableError(error)) {
373
        console.log(`Transaction attempt ${attempt} failed, retrying...`);
374
        await new Promise(resolve => setTimeout(resolve, 1000 * attempt));
375
        continue;
376
      }
377
      
378
      console.error(`Transaction failed after ${attempt} attempts`);
379
      throw error;
380
    }
381
  }
382
}
383

384
function isRetryableError(error) {
385
  // Check for specific retryable Oracle errors
386
  return error.message.includes('ORA-00060') || // Deadlock
387
         error.message.includes('ORA-08177'); // Serialization failure
388
}
389

390
// Deadlock handling
391
try {
392
  await connection.execute('UPDATE table1 SET col1 = :1 WHERE id = :2', [val1, id1]);
393
  await connection.execute('UPDATE table2 SET col2 = :1 WHERE id = :2', [val2, id2]);
394
  await connection.commit();
395
} catch (error) {
396
  await connection.rollback();
397
  
398
  if (error.message.includes('ORA-00060')) { // Deadlock detected
399
    console.log('Deadlock detected, retrying transaction...');
400
    // Implement retry logic with exponential backoff
401
    await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
402
    // Retry transaction
403
  } else {
404
    throw error;
405
  }
406
}
407
```