0
# Transactions
1

2
Database transaction management with support for multiple isolation levels, savepoints, and automatic rollback on errors.
3

4
## Capabilities
5

6
### Creating Transactions
7

8
Methods for creating and managing database transactions.
9

10
```typescript { .api }
11
/**
12
 * Create managed transaction with automatic handling
13
 * @param options - Transaction options
14
 * @param autoCallback - Function to execute within transaction
15
 * @returns Promise resolving to callback result
16
 */
17
transaction<T>(options: TransactionOptions, autoCallback: (t: Transaction) => PromiseLike<T>): Promise<T>;
18

19
/**
20
 * Create unmanaged transaction
21
 * @param options - Transaction options
22
 * @returns Promise resolving to Transaction instance
23
 */
24
transaction(options?: TransactionOptions): Promise<Transaction>;
25

26
interface TransactionOptions {
27
  /** Transaction isolation level */
28
  isolationLevel?: Transaction.ISOLATION_LEVELS;
29
  /** Transaction type */
30
  type?: Transaction.TYPES;
31
  /** Deferrable constraints */
32
  deferrable?: Deferrable;
33
  /** Read-only transaction */
34
  readOnly?: boolean;
35
  /** Auto-commit mode */
36
  autocommit?: boolean;
37
  /** Query logging */
38
  logging?: boolean | ((sql: string, timing?: number) => void);
39
}
40
```
41

42
**Usage Examples:**
43

44
```typescript
45
// Managed transaction (recommended)
46
try {
47
  const result = await sequelize.transaction(async (t) => {
48
    const user = await User.create({
49
      firstName: 'John',
50
      lastName: 'Doe'
51
    }, { transaction: t });
52

53
    await Profile.create({
54
      userId: user.id,
55
      bio: 'Hello world'
56
    }, { transaction: t });
57

58
    return user;
59
  });
60
  // Transaction automatically committed
61
  console.log('User created:', result.id);
62
} catch (error) {
63
  // Transaction automatically rolled back
64
  console.error('Transaction failed:', error);
65
}
66

67
// Unmanaged transaction
68
const t = await sequelize.transaction();
69
try {
70
  const user = await User.create({
71
    firstName: 'Jane'
72
  }, { transaction: t });
73

74
  await Profile.create({
75
    userId: user.id,
76
    bio: 'Hello'
77
  }, { transaction: t });
78

79
  await t.commit();
80
} catch (error) {
81
  await t.rollback();
82
  throw error;
83
}
84
```
85

86
### Transaction Control
87

88
Methods for controlling transaction lifecycle.
89

90
```typescript { .api }
91
class Transaction {
92
  /**
93
   * Commit the transaction
94
   * @returns Promise that resolves when transaction is committed
95
   */
96
  commit(): Promise<void>;
97

98
  /**
99
   * Rollback the transaction
100
   * @returns Promise that resolves when transaction is rolled back
101
   */
102
  rollback(): Promise<void>;
103

104
  /**
105
   * Add callback to execute after successful commit
106
   * @param fn - Callback function
107
   */
108
  afterCommit(fn: (transaction: Transaction) => void): void;
109

110
  /**
111
   * Add callback to execute after rollback
112
   * @param fn - Callback function
113
   */
114
  afterRollback(fn: (transaction: Transaction) => void): void;
115
}
116
```
117

118
**Usage Examples:**
119

120
```typescript
121
const t = await sequelize.transaction();
122

123
// Add hooks
124
t.afterCommit((transaction) => {
125
  console.log('Transaction committed successfully');
126
  // Send notification, clear cache, etc.
127
});
128

129
t.afterRollback((transaction) => {
130
  console.log('Transaction was rolled back');
131
  // Log error, cleanup resources, etc.
132
});
133

134
try {
135
  // Perform operations
136
  await User.create({ name: 'John' }, { transaction: t });
137
  await t.commit();
138
} catch (error) {
139
  await t.rollback();
140
}
141
```
142

143
### Isolation Levels
144

145
Transaction isolation levels for controlling concurrent access.
146

147
```typescript { .api }
148
namespace Transaction {
149
  enum ISOLATION_LEVELS {
150
    READ_UNCOMMITTED = 'READ UNCOMMITTED',
151
    READ_COMMITTED = 'READ COMMITTED',
152
    REPEATABLE_READ = 'REPEATABLE READ',
153
    SERIALIZABLE = 'SERIALIZABLE'
154
  }
155
}
156
```
157

158
**Usage Examples:**
159

160
```typescript
161
// Different isolation levels
162
const t1 = await sequelize.transaction({
163
  isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED
164
});
165

166
const t2 = await sequelize.transaction({
167
  isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE
168
});
169

170
// Using with managed transactions
171
await sequelize.transaction({
172
  isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ
173
}, async (t) => {
174
  // Operations within transaction
175
  const user = await User.findByPk(1, { transaction: t });
176
  user.balance += 100;
177
  await user.save({ transaction: t });
178
});
179
```
180

181
### Transaction Types
182

183
Transaction types for different database behaviors.
184

185
```typescript { .api }
186
namespace Transaction {
187
  enum TYPES {
188
    DEFERRED = 'DEFERRED',
189
    IMMEDIATE = 'IMMEDIATE', 
190
    EXCLUSIVE = 'EXCLUSIVE'
191
  }
192
}
193
```
194

195
**Usage Example:**
196

197
```typescript
198
// SQLite transaction types
199
const t = await sequelize.transaction({
200
  type: Transaction.TYPES.IMMEDIATE
201
});
202
```
203

204
### Deferrable Constraints
205

206
Control when constraints are checked within transactions.
207

208
```typescript { .api }
209
interface Deferrable {
210
  INITIALLY_DEFERRED: symbol;
211
  INITIALLY_IMMEDIATE: symbol;
212
  NOT: symbol;
213
}
214

215
const Deferrable: Deferrable;
216
```
217

218
**Usage Example:**
219

220
```typescript
221
// PostgreSQL deferrable constraints
222
const t = await sequelize.transaction({
223
  deferrable: Deferrable.INITIALLY_DEFERRED
224
});
225

226
// Constraints checked at transaction end
227
await User.create({ /* data */ }, { transaction: t });
228
await Profile.create({ /* data */ }, { transaction: t });
229
await t.commit(); // Constraints validated here
230
```
231

232
### Savepoints
233

234
Create savepoints within transactions for partial rollbacks.
235

236
```typescript { .api }
237
class Transaction {
238
  /**
239
   * Create a savepoint
240
   * @param name - Savepoint name
241
   * @returns Promise resolving when savepoint is created
242
   */
243
  createSavepoint(name: string): Promise<void>;
244

245
  /**
246
   * Rollback to savepoint
247
   * @param name - Savepoint name
248
   * @returns Promise resolving when rolled back to savepoint
249
   */
250
  rollbackToSavepoint(name: string): Promise<void>;
251

252
  /**
253
   * Release savepoint
254
   * @param name - Savepoint name
255
   * @returns Promise resolving when savepoint is released
256
   */
257
  releaseSavepoint(name: string): Promise<void>;
258
}
259
```
260

261
**Usage Example:**
262

263
```typescript
264
await sequelize.transaction(async (t) => {
265
  // Create user
266
  const user = await User.create({ name: 'John' }, { transaction: t });
267

268
  // Create savepoint
269
  await t.createSavepoint('user_created');
270

271
  try {
272
    // Attempt risky operation
273
    await RiskyOperation.create({ userId: user.id }, { transaction: t });
274
  } catch (error) {
275
    // Rollback to savepoint, user creation preserved
276
    await t.rollbackToSavepoint('user_created');
277
    console.log('Risky operation failed, but user still created');
278
  }
279

280
  // Continue with transaction
281
  await Profile.create({ userId: user.id }, { transaction: t });
282
});
283
```
284

285
### Transaction with Queries
286

287
Using transactions with various query operations.
288

289
```typescript { .api }
290
// All query methods accept transaction option
291
interface QueryOptions {
292
  transaction?: Transaction;
293
}
294
```
295

296
**Usage Examples:**
297

298
```typescript
299
await sequelize.transaction(async (t) => {
300
  // Create operations
301
  const user = await User.create({
302
    name: 'John'
303
  }, { transaction: t });
304

305
  const posts = await Post.bulkCreate([
306
    { title: 'Post 1', userId: user.id },
307
    { title: 'Post 2', userId: user.id }
308
  ], { transaction: t });
309

310
  // Update operations
311
  await User.update({
312
    lastActive: new Date()
313
  }, {
314
    where: { id: user.id },
315
    transaction: t
316
  });
317

318
  // Find operations
319
  const updatedUser = await User.findByPk(user.id, {
320
    include: [Post],
321
    transaction: t
322
  });
323

324
  // Delete operations
325
  await Post.destroy({
326
    where: { userId: user.id, status: 'draft' },
327
    transaction: t
328
  });
329

330
  // Raw queries
331
  await sequelize.query(
332
    'UPDATE users SET updated_at = NOW() WHERE id = :userId',
333
    {
334
      replacements: { userId: user.id },
335
      transaction: t
336
    }
337
  );
338
});
339
```
340

341
### Concurrent Transaction Handling
342

343
Patterns for handling concurrent transactions and conflicts.
344

345
```typescript { .api }
346
// Optimistic locking with version field
347
class User extends Model {}
348
User.init({
349
  name: DataTypes.STRING,
350
  version: {
351
    type: DataTypes.INTEGER,
352
    defaultValue: 0
353
  }
354
});
355

356
// Handle optimistic lock errors
357
try {
358
  await sequelize.transaction(async (t) => {
359
    const user = await User.findByPk(1, { transaction: t });
360
    user.name = 'Updated Name';
361
    user.version += 1;
362
    
363
    await user.save({
364
      transaction: t,
365
      where: { version: user.previous('version') } // Optimistic lock
366
    });
367
  });
368
} catch (error) {
369
  if (error instanceof OptimisticLockError) {
370
    console.log('Record was modified by another transaction');
371
    // Retry logic here
372
  }
373
}
374
```
375

376
### Transaction Best Practices
377

378
Recommended patterns for transaction usage.
379

380
```typescript { .api }
381
// Connection pooling considerations
382
const sequelize = new Sequelize(database, username, password, {
383
  pool: {
384
    max: 5,
385
    min: 0,
386
    acquire: 30000,
387
    idle: 10000
388
  }
389
});
390

391
// Batch operations in transactions
392
async function createUsersInBatch(userData: any[]) {
393
  return await sequelize.transaction(async (t) => {
394
    const users = [];
395
    
396
    // Process in chunks to avoid long-running transactions
397
    for (let i = 0; i < userData.length; i += 100) {
398
      const chunk = userData.slice(i, i + 100);
399
      const chunkUsers = await User.bulkCreate(chunk, { transaction: t });
400
      users.push(...chunkUsers);
401
    }
402
    
403
    return users;
404
  });
405
}
406

407
// Error handling with specific rollback logic
408
async function transferFunds(fromUserId: number, toUserId: number, amount: number) {
409
  return await sequelize.transaction(async (t) => {
410
    const fromUser = await User.findByPk(fromUserId, {
411
      transaction: t,
412
      lock: Transaction.LOCK.UPDATE // Row-level locking
413
    });
414
    
415
    const toUser = await User.findByPk(toUserId, {
416
      transaction: t,
417
      lock: Transaction.LOCK.UPDATE
418
    });
419
    
420
    if (fromUser.balance < amount) {
421
      throw new Error('Insufficient funds');
422
    }
423
    
424
    fromUser.balance -= amount;
425
    toUser.balance += amount;
426
    
427
    await fromUser.save({ transaction: t });
428
    await toUser.save({ transaction: t });
429
    
430
    // Log transaction
431
    await TransactionLog.create({
432
      fromUserId,
433
      toUserId,
434
      amount,
435
      timestamp: new Date()
436
    }, { transaction: t });
437
    
438
    return { fromUser, toUser };
439
  });
440
}
441
```
442

443
### Row-Level Locking
444

445
Transaction locking mechanisms for concurrent access control.
446

447
```typescript { .api }
448
namespace Transaction {
449
  enum LOCK {
450
    UPDATE = 'UPDATE',
451
    SHARE = 'SHARE',
452
    KEY_SHARE = 'KEY SHARE',
453
    NO_KEY_UPDATE = 'NO KEY UPDATE'
454
  }
455
}
456

457
interface FindOptions {
458
  /** Row-level lock type */
459
  lock?: Transaction.LOCK | boolean;
460
  /** Skip locked rows */
461
  skipLocked?: boolean;
462
}
463
```
464

465
**Usage Examples:**
466

467
```typescript
468
// Row-level locking for updates
469
await sequelize.transaction(async (t) => {
470
  // Lock user row for update
471
  const user = await User.findByPk(1, {
472
    lock: Transaction.LOCK.UPDATE,
473
    transaction: t
474
  });
475
  
476
  // Modify user (other transactions will wait)
477
  user.balance += 100;
478
  await user.save({ transaction: t });
479
});
480

481
// Shared lock for reading
482
const users = await User.findAll({
483
  where: { isActive: true },
484
  lock: Transaction.LOCK.SHARE,
485
  transaction: t
486
});
487

488
// Skip locked rows (PostgreSQL)
489
const availableUsers = await User.findAll({
490
  lock: Transaction.LOCK.UPDATE,
491
  skipLocked: true,
492
  transaction: t
493
});
494
```
495

496
### Nested Transactions with Savepoints
497

498
Advanced savepoint management for complex transaction scenarios.
499

500
```typescript { .api }
501
class Transaction {
502
  /**
503
   * Create a savepoint within the transaction
504
   * @param name - Savepoint name
505
   * @returns Promise resolving when savepoint is created
506
   */
507
  createSavepoint(name: string): Promise<void>;
508

509
  /**
510
   * Rollback to a specific savepoint
511
   * @param name - Savepoint name
512
   * @returns Promise resolving when rolled back to savepoint
513
   */
514
  rollbackToSavepoint(name: string): Promise<void>;
515

516
  /**
517
   * Release a savepoint (remove it)
518
   * @param name - Savepoint name
519
   * @returns Promise resolving when savepoint is released
520
   */
521
  releaseSavepoint(name: string): Promise<void>;
522
}
523
```
524

525
**Usage Examples:**
526

527
```typescript
528
// Complex transaction with multiple savepoints
529
await sequelize.transaction(async (t) => {
530
  // Initial operations
531
  const user = await User.create({ name: 'John' }, { transaction: t });
532
  
533
  // First savepoint
534
  await t.createSavepoint('after_user_creation');
535
  
536
  try {
537
    // Risky operation 1
538
    await RiskyService.operation1(user.id, { transaction: t });
539
    
540
    // Second savepoint
541
    await t.createSavepoint('after_operation1');
542
    
543
    try {
544
      // Risky operation 2
545
      await RiskyService.operation2(user.id, { transaction: t });
546
      
547
      // Success - release savepoints
548
      await t.releaseSavepoint('after_operation1');
549
      await t.releaseSavepoint('after_user_creation');
550
      
551
    } catch (error) {
552
      // Rollback operation 2, keep operation 1
553
      console.log('Operation 2 failed, rolling back to after operation 1');
554
      await t.rollbackToSavepoint('after_operation1');
555
    }
556
    
557
  } catch (error) {
558
    // Rollback everything except user creation
559
    console.log('Operation 1 failed, rolling back to after user creation');
560
    await t.rollbackToSavepoint('after_user_creation');
561
  }
562
  
563
  // Continue with other operations...
564
  await Profile.create({ userId: user.id }, { transaction: t });
565
});
566
```
567

568
### Transaction Callback Hooks
569

570
Advanced transaction lifecycle hooks.
571

572
```typescript { .api }
573
class Transaction {
574
  /**
575
   * Add callback to execute after successful commit
576
   * @param fn - Callback function
577
   */
578
  afterCommit(fn: (transaction: Transaction) => void | Promise<void>): void;
579

580
  /**
581
   * Add callback to execute after rollback
582
   * @param fn - Callback function
583
   */
584
  afterRollback(fn: (transaction: Transaction) => void | Promise<void>): void;
585
}
586
```
587

588
**Usage Examples:**
589

590
```typescript
591
// Transaction with lifecycle callbacks
592
const t = await sequelize.transaction();
593

594
// Setup callbacks
595
t.afterCommit(async (transaction) => {
596
  // Clear cache after successful commit
597
  await cache.clear(['users', 'posts']);
598
  
599
  // Send notifications
600
  await notificationService.sendUpdates();
601
  
602
  // Log successful transaction
603
  console.log('Transaction committed successfully');
604
});
605

606
t.afterRollback(async (transaction) => {
607
  // Log rollback
608
  console.log('Transaction was rolled back');
609
  
610
  // Send error notifications
611
  await notificationService.sendErrorAlert();
612
  
613
  // Cleanup any side effects
614
  await cleanupService.rollbackSideEffects();
615
});
616

617
try {
618
  // Perform operations
619
  const user = await User.create({ name: 'John' }, { transaction: t });
620
  const profile = await Profile.create({ userId: user.id }, { transaction: t });
621
  
622
  await t.commit(); // afterCommit callbacks will run
623
} catch (error) {
624
  await t.rollback(); // afterRollback callbacks will run
625
}
626
```
627

628
### Transaction Context Management
629

630
Advanced patterns for transaction context management.
631

632
```typescript { .api }
633
// Transaction context utilities
634
interface TransactionContext {
635
  /** Current transaction */
636
  transaction?: Transaction;
637
  /** User performing the operation */
638
  userId?: number;
639
  /** Request context */
640
  requestId?: string;
641
  /** Additional metadata */
642
  metadata?: any;
643
}
644

645
// Utility functions for transaction management
646
class TransactionManager {
647
  static async withTransaction<T>(
648
    operation: (context: TransactionContext) => Promise<T>,
649
    options?: TransactionOptions & { userId?: number; requestId?: string }
650
  ): Promise<T> {
651
    return await sequelize.transaction(async (t) => {
652
      const context: TransactionContext = {
653
        transaction: t,
654
        userId: options?.userId,
655
        requestId: options?.requestId || generateRequestId(),
656
        metadata: {}
657
      };
658
      
659
      return await operation(context);
660
    });
661
  }
662
  
663
  static async withSavepoint<T>(
664
    transaction: Transaction,
665
    savepointName: string,
666
    operation: () => Promise<T>
667
  ): Promise<T> {
668
    await transaction.createSavepoint(savepointName);
669
    
670
    try {
671
      const result = await operation();
672
      await transaction.releaseSavepoint(savepointName);
673
      return result;
674
    } catch (error) {
675
      await transaction.rollbackToSavepoint(savepointName);
676
      throw error;
677
    }
678
  }
679
}
680
```
681

682
**Usage Examples:**
683

684
```typescript
685
// Use transaction manager for complex operations
686
const result = await TransactionManager.withTransaction(async (context) => {
687
  const { transaction, userId, requestId } = context;
688
  
689
  // Create user
690
  const user = await User.create({
691
    name: 'John',
692
    createdBy: userId
693
  }, { transaction });
694
  
695
  // Use savepoint for risky operation
696
  await TransactionManager.withSavepoint(transaction, 'before_profile', async () => {
697
    const profile = await Profile.create({
698
      userId: user.id,
699
      bio: 'User bio'
700
    }, { transaction });
701
    
702
    // This might fail, but user creation will be preserved
703
    await ExternalService.createProfile(profile.id);
704
  });
705
  
706
  // Log the operation
707
  await AuditLog.create({
708
    action: 'user_creation',
709
    userId: user.id,
710
    performedBy: userId,
711
    requestId,
712
    timestamp: new Date()
713
  }, { transaction });
714
  
715
  return user;
716
}, { 
717
  userId: 123, 
718
  requestId: 'req-456',
719
  isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED 
720
});
721
```
722

723
### Transaction Retry Pattern
724

725
Automatic retry logic for transaction conflicts.
726

727
```typescript { .api }
728
// Transaction retry utility
729
class TransactionRetry {
730
  static async withRetry<T>(
731
    operation: () => Promise<T>,
732
    options: {
733
      maxRetries?: number;
734
      baseDelay?: number;
735
      maxDelay?: number;
736
      retryOnError?: (error: Error) => boolean;
737
    } = {}
738
  ): Promise<T> {
739
    const {
740
      maxRetries = 3,
741
      baseDelay = 100,
742
      maxDelay = 5000,
743
      retryOnError = (error) => 
744
        error.name === 'SequelizeOptimisticLockError' ||
745
        error.name === 'SequelizeTimeoutError' ||
746
        error.message.includes('deadlock')
747
    } = options;
748

749
    let attempt = 1;
750
    let delay = baseDelay;
751

752
    while (attempt <= maxRetries) {
753
      try {
754
        return await operation();
755
      } catch (error) {
756
        if (attempt === maxRetries || !retryOnError(error)) {
757
          throw error;
758
        }
759

760
        console.log(`Transaction failed on attempt ${attempt}, retrying in ${delay}ms...`);
761
        await new Promise(resolve => setTimeout(resolve, delay));
762
        
763
        attempt++;
764
        delay = Math.min(delay * 2, maxDelay); // Exponential backoff
765
      }
766
    }
767

768
    throw new Error('Max retries exceeded');
769
  }
770
}
771
```
772

773
**Usage Examples:**
774

775
```typescript
776
// Retry transaction on conflicts
777
const user = await TransactionRetry.withRetry(async () => {
778
  return await sequelize.transaction(async (t) => {
779
    const user = await User.findByPk(1, {
780
      lock: Transaction.LOCK.UPDATE,
781
      transaction: t
782
    });
783
    
784
    if (!user) {
785
      throw new Error('User not found');
786
    }
787
    
788
    // This might cause optimistic lock errors in high concurrency
789
    user.balance += 100;
790
    user.version += 1; // Manual version increment
791
    
792
    await user.save({
793
      transaction: t,
794
      where: { version: user.previous('version') } // Optimistic locking
795
    });
796
    
797
    return user;
798
  });
799
}, {
800
  maxRetries: 5,
801
  baseDelay: 50,
802
  retryOnError: (error) => 
803
    error.name === 'SequelizeOptimisticLockError' ||
804
    error.message.includes('version')
805
});
806
```