How does TypeORM handle transactions? Detailed explanation of transaction isolation levels, locking mechanisms, and distributed transactions

Transactions are a core concept in database operations, ensuring that a set of database operations either all succeed or all fail. TypeORM provides multiple transaction handling methods, allowing developers to easily manage database transactions.

Transaction Basic Concepts

What is a Transaction

A transaction is a logical unit of database operations with ACID properties:

• Atomicity: Operations in a transaction either all execute or none execute
• Consistency: Database remains in a consistent state before and after transaction execution
• Isolation: Concurrent transactions are isolated from each other
• Durability: Changes are permanently saved after transaction commit

Transaction Use Cases

• Bank transfers (debit from one account, credit to another)
• Order processing (create order, reduce inventory, update user balance)
• Multi-table related operations
• Complex business logic requiring data consistency

TypeORM Transaction Handling Methods

1. Using DataSource.transaction()

This is the most commonly used transaction handling method, automatically managing transaction commit and rollback.

typescript
import { DataSource } from 'typeorm';

async function transferFunds(
  dataSource: DataSource,
  fromUserId: number,
  toUserId: number,
  amount: number
) {
  await dataSource.transaction(async transactionalEntityManager => {
    // Query sender account
    const fromUser = await transactionalEntityManager.findOne(User, {
      where: { id: fromUserId },
      lock: { mode: 'pessimistic_write' }
    });

    if (!fromUser || fromUser.balance < amount) {
      throw new Error('Insufficient balance');
    }

    // Query recipient account
    const toUser = await transactionalEntityManager.findOne(User, {
      where: { id: toUserId },
      lock: { mode: 'pessimistic_write' }
    });

    if (!toUser) {
      throw new Error('Recipient not found');
    }

    // Deduct from sender
    fromUser.balance -= amount;
    await transactionalEntityManager.save(fromUser);

    // Add to recipient
    toUser.balance += amount;
    await transactionalEntityManager.save(toUser);

    // Record transaction log
    const transaction = transactionalEntityManager.create(Transaction, {
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    });
    await transactionalEntityManager.save(transaction);
  });
}

2. Using QueryRunner

QueryRunner provides more fine-grained transaction control.

typescript
import { DataSource } from 'typeorm';

async function transferWithQueryRunner(
  dataSource: DataSource,
  fromUserId: number,
  toUserId: number,
  amount: number
) {
  const queryRunner = dataSource.createQueryRunner();
  
  try {
    // Start transaction
    await queryRunner.connect();
    await queryRunner.startTransaction();

    // Query sender account
    const fromUser = await queryRunner.manager.findOne(User, {
      where: { id: fromUserId },
      lock: { mode: 'pessimistic_write' }
    });

    if (!fromUser || fromUser.balance < amount) {
      throw new Error('Insufficient balance');
    }

    // Query recipient account
    const toUser = await queryRunner.manager.findOne(User, {
      where: { id: toUserId },
      lock: { mode: 'pessimistic_write' }
    });

    if (!toUser) {
      throw new Error('Recipient not found');
    }

    // Execute transfer
    await queryRunner.manager.update(User, fromUserId, {
      balance: fromUser.balance - amount
    });

    await queryRunner.manager.update(User, toUserId, {
      balance: toUser.balance + amount
    });

    // Record transaction log
    await queryRunner.manager.insert(Transaction, {
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    });

    // Commit transaction
    await queryRunner.commitTransaction();
  } catch (error) {
    // Rollback transaction
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    // Release QueryRunner
    await queryRunner.release();
  }
}

3. Using Decorator Transaction

Use decorators to declare transactions on class methods.

typescript
import { Transaction, TransactionManager, EntityManager } from 'typeorm';

class PaymentService {
  @Transaction()
  async processPayment(
    userId: number,
    amount: number,
    @TransactionManager() manager?: EntityManager
  ) {
    const user = await manager.findOne(User, {
      where: { id: userId },
      lock: { mode: 'pessimistic_write' }
    });

    if (!user || user.balance < amount) {
      throw new Error('Insufficient balance');
    }

    user.balance -= amount;
    await manager.save(user);

    const payment = manager.create(Payment, {
      userId,
      amount,
      status: 'completed'
    });
    await manager.save(payment);
  }
}

Transaction Isolation Levels

Setting Isolation Level

typescript
import { DataSource } from 'typeorm';

const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Transaction],
  extra: {
    connectionLimit: 10,
  },
});

// Set isolation level in transaction
await dataSource.transaction(async transactionalEntityManager => {
  const queryRunner = transactionalEntityManager.queryRunner;
  
  // Set isolation level
  await queryRunner.query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
  
  // Execute business logic
  // ...
});

Isolation Level Explanation

1. READ UNCOMMITTED

   • Can read uncommitted data
   • May cause dirty reads, non-repeatable reads, phantom reads
   • Best performance but worst data consistency

2. READ COMMITTED

   • Can only read committed data
   • Avoids dirty reads but may cause non-repeatable reads, phantom reads
   • Default isolation level for most databases

3. REPEATABLE READ

   • Multiple reads of same data in same transaction return consistent results
   • Avoids dirty reads, non-repeatable reads but may cause phantom reads
   • Default isolation level for MySQL

4. SERIALIZABLE

   • Highest isolation level, completely avoids concurrency issues
   • Worst performance but best data consistency

typescript
// Examples of setting different isolation levels
await dataSource.transaction(async transactionalEntityManager => {
  const queryRunner = transactionalEntityManager.queryRunner;
  
  // READ COMMITTED
  await queryRunner.query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
  
  // REPEATABLE READ
  await queryRunner.query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
  
  // SERIALIZABLE
  await queryRunner.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
});

Locking Mechanisms

Pessimistic Locking

Pessimistic locking assumes conflicts will occur and locks data when reading.

typescript
// SELECT ... FOR UPDATE
const user = await dataSource.transaction(async transactionalEntityManager => {
  const user = await transactionalEntityManager.findOne(User, {
    where: { id: userId },
    lock: { mode: 'pessimistic_write' }
  });
  
  // Row is locked, other transactions cannot modify
  user.balance -= amount;
  await transactionalEntityManager.save(user);
  
  return user;
});

// SELECT ... FOR SHARE
const user = await dataSource.transaction(async transactionalEntityManager => {
  const user = await transactionalEntityManager.findOne(User, {
    where: { id: userId },
    lock: { mode: 'pessimistic_read' }
  });
  
  // Other transactions can read but cannot modify
  return user;
});

Optimistic Locking

Optimistic locking assumes no conflicts will occur and detects conflicts through version numbers or timestamps.

typescript
@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  stock: number;

  @VersionColumn()
  version: number;
}

// Using optimistic locking
async function updateProductStock(productId: number, quantity: number) {
  const product = await dataSource.manager.findOne(Product, {
    where: { id: productId }
  });

  if (!product || product.stock < quantity) {
    throw new Error('Insufficient stock');
  }

  product.stock -= quantity;
  
  try {
    await dataSource.manager.save(product);
  } catch (error) {
    // Version conflict, another transaction modified the data
    throw new Error('Concurrent modification detected');
  }
}

Nested Transactions

Using Savepoints

typescript
async function complexOperation(dataSource: DataSource) {
  await dataSource.transaction(async transactionalEntityManager => {
    const queryRunner = transactionalEntityManager.queryRunner;
    
    try {
      // Main transaction operations
      await transactionalEntityManager.save(/* ... */);
      
      // Create savepoint
      await queryRunner.query('SAVEPOINT savepoint1');
      
      try {
        // Sub-transaction operations
        await transactionalEntityManager.save(/* ... */);
        
        // If successful, release savepoint
        await queryRunner.query('RELEASE SAVEPOINT savepoint1');
      } catch (error) {
        // If failed, rollback to savepoint
        await queryRunner.query('ROLLBACK TO SAVEPOINT savepoint1');
        // Continue with other operations
      }
      
      // Continue main transaction operations
      await transactionalEntityManager.save(/* ... */);
      
    } catch (error) {
      throw error; // Entire transaction rolls back
    }
  });
}

Transaction Timeout and Retry

Setting Transaction Timeout

typescript
async function transactionWithTimeout(
  dataSource: DataSource,
  timeout: number = 30000
) {
  const queryRunner = dataSource.createQueryRunner();
  
  try {
    await queryRunner.connect();
    await queryRunner.startTransaction();
    
    // Set transaction timeout (MySQL)
    await queryRunner.query(`SET max_execution_time = ${timeout}`);
    
    // Execute business logic
    // ...
    
    await queryRunner.commitTransaction();
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release();
  }
}

Transaction Retry Mechanism

typescript
async function transactionWithRetry(
  dataSource: DataSource,
  maxRetries: number = 3,
  operation: (manager: EntityManager) => Promise<void>
) {
  let lastError: Error;
  
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      await dataSource.transaction(async transactionalEntityManager => {
        await operation(transactionalEntityManager);
      });
      return; // Success, exit retry loop
    } catch (error) {
      lastError = error;
      
      // If deadlock error, can retry
      if (error.code === 'ER_LOCK_DEADLOCK' && attempt < maxRetries) {
        await new Promise(resolve => setTimeout(resolve, 100 * attempt));
        continue;
      }
      
      throw error; // Other errors thrown directly
    }
  }
  
  throw lastError;
}

// Usage example
await transactionWithRetry(dataSource, 3, async manager => {
  const user = await manager.findOne(User, { where: { id: 1 } });
  user.balance += 100;
  await manager.save(user);
});

Distributed Transactions

Two-Phase Commit (2PC)

TypeORM doesn't support distributed transactions natively, but can be implemented as follows:

typescript
async function distributedTransaction(
  dataSource1: DataSource,
  dataSource2: DataSource
) {
  const queryRunner1 = dataSource1.createQueryRunner();
  const queryRunner2 = dataSource2.createQueryRunner();
  
  try {
    await queryRunner1.connect();
    await queryRunner2.connect();
    
    await queryRunner1.startTransaction();
    await queryRunner2.startTransaction();
    
    // Phase 1: Prepare
    await queryRunner1.manager.save(/* ... */);
    await queryRunner2.manager.save(/* ... */);
    
    // Phase 2: Commit
    await queryRunner1.commitTransaction();
    await queryRunner2.commitTransaction();
  } catch (error) {
    // Rollback all transactions
    await queryRunner1.rollbackTransaction();
    await queryRunner2.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner1.release();
    await queryRunner2.release();
  }
}

Best Practices

1. Keep Transactions Short

typescript
// ❌ Bad: Transaction too long
await dataSource.transaction(async manager => {
  const user = await manager.findOne(User, { where: { id: 1 } });
  
  // Time-consuming operations (should not be in transaction)
  await sendEmail(user.email);
  await processPayment(user);
  
  user.balance += 100;
  await manager.save(user);
});

// ✅ Good: Only database operations in transaction
const user = await dataSource.manager.findOne(User, { where: { id: 1 } });

// Execute time-consuming operations outside transaction
await sendEmail(user.email);
await processPayment(user);

// Only database operations in transaction
await dataSource.transaction(async manager => {
  user.balance += 100;
  await manager.save(user);
});

2. Handle Exceptions Properly

typescript
await dataSource.transaction(async manager => {
  try {
    const user = await manager.findOne(User, { where: { id: 1 } });
    
    if (!user) {
      throw new Error('User not found');
    }
    
    user.balance += 100;
    await manager.save(user);
  } catch (error) {
    // Log error
    console.error('Transaction failed:', error);
    // Throw error to trigger rollback
    throw error;
  }
});

3. Avoid Nested Transactions

typescript
// ❌ Bad: Nested transactions
await dataSource.transaction(async manager1 => {
  await dataSource.transaction(async manager2 => {
    // Nested transactions may cause problems
  });
});

// ✅ Good: Single transaction
await dataSource.transaction(async manager => {
  // All operations in one transaction
});

4. Use Appropriate Isolation Levels

typescript
// Choose appropriate isolation level based on business requirements
await dataSource.transaction(async manager => {
  const queryRunner = manager.queryRunner;
  
  // READ COMMITTED for read-heavy, write-light scenarios
  await queryRunner.query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
  
  // SERIALIZABLE for scenarios requiring strong consistency
  // await queryRunner.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  
  // Execute business logic
});

TypeORM's transaction handling is powerful and flexible. Mastering transaction usage is crucial for building reliable data-driven applications. Proper use of transactions ensures data consistency and integrity, avoiding concurrency issues.