workspace-v1/orchestration/patrones/PATRON-TRANSACCIONES.md

PATRON DE TRANSACCIONES

Version: 1.0.0 Fecha: 2025-12-08 Prioridad: OBLIGATORIA - Seguir para integridad de datos Sistema: SIMCO + CAPVED

---

PROPOSITO

Definir patrones de manejo de transacciones de base de datos para garantizar integridad de datos, consistencia y correcta recuperacion de errores.

---

1. PRINCIPIOS ACID

╔══════════════════════════════════════════════════════════════════════╗
║  PROPIEDADES ACID                                                    ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                       ║
║  A - Atomicity (Atomicidad)                                          ║
║      Todo o nada. Si una parte falla, se revierte todo.              ║
║                                                                       ║
║  C - Consistency (Consistencia)                                      ║
║      La BD pasa de un estado valido a otro estado valido.            ║
║                                                                       ║
║  I - Isolation (Aislamiento)                                         ║
║      Transacciones concurrentes no interfieren entre si.             ║
║                                                                       ║
║  D - Durability (Durabilidad)                                        ║
║      Una vez confirmada, la transaccion persiste.                    ║
║                                                                       ║
╚══════════════════════════════════════════════════════════════════════╝

---

2. CUANDO USAR TRANSACCIONES

SI Usar Transaccion

✅ Multiples operaciones que deben ser atomicas
✅ Operaciones que afectan multiples tablas relacionadas
✅ Operaciones financieras o criticas
✅ Creacion de entidades con relaciones obligatorias
✅ Actualizaciones que requieren consistencia

NO Necesitas Transaccion

❌ Una sola query simple (SELECT, INSERT, UPDATE)
❌ Operaciones de solo lectura
❌ Operaciones independientes sin relacion

---

3. TYPEORM - METODOS DE TRANSACCION

3.1 QueryRunner (Recomendado para control total)

// src/modules/order/services/order.service.ts
import { Injectable } from '@nestjs/common';
import { DataSource, QueryRunner } from 'typeorm';

@Injectable()
export class OrderService {
  constructor(private readonly dataSource: DataSource) {}

  async createOrder(dto: CreateOrderDto): Promise<Order> {
    // Crear QueryRunner
    const queryRunner = this.dataSource.createQueryRunner();

    // Conectar y comenzar transaccion
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // 1. Crear la orden
      const order = queryRunner.manager.create(OrderEntity, {
        userId: dto.userId,
        status: OrderStatus.PENDING,
        total: 0,
      });
      await queryRunner.manager.save(order);

      // 2. Crear items y calcular total
      let total = 0;
      for (const item of dto.items) {
        // Verificar stock
        const product = await queryRunner.manager.findOne(ProductEntity, {
          where: { id: item.productId },
          lock: { mode: 'pessimistic_write' },  // Lock para evitar race condition
        });

        if (!product || product.stock < item.quantity) {
          throw new BadRequestException(
            `Stock insuficiente para producto ${item.productId}`,
          );
        }

        // Crear item
        const orderItem = queryRunner.manager.create(OrderItemEntity, {
          orderId: order.id,
          productId: item.productId,
          quantity: item.quantity,
          price: product.price,
        });
        await queryRunner.manager.save(orderItem);

        // Actualizar stock
        product.stock -= item.quantity;
        await queryRunner.manager.save(product);

        total += product.price * item.quantity;
      }

      // 3. Actualizar total de la orden
      order.total = total;
      await queryRunner.manager.save(order);

      // 4. Confirmar transaccion
      await queryRunner.commitTransaction();

      return order;

    } catch (error) {
      // Revertir en caso de error
      await queryRunner.rollbackTransaction();
      throw error;

    } finally {
      // Liberar QueryRunner
      await queryRunner.release();
    }
  }
}

3.2 DataSource.transaction() (Mas simple)

// Para casos mas simples
async createUserWithProfile(dto: CreateUserWithProfileDto): Promise<User> {
  return this.dataSource.transaction(async (manager) => {
    // Crear usuario
    const user = manager.create(UserEntity, {
      email: dto.email,
      password: await hashPassword(dto.password),
    });
    await manager.save(user);

    // Crear perfil
    const profile = manager.create(ProfileEntity, {
      userId: user.id,
      firstName: dto.firstName,
      lastName: dto.lastName,
    });
    await manager.save(profile);

    return user;
  });
}

3.3 @Transaction Decorator (NestJS)

// src/shared/decorators/transactional.decorator.ts
import { DataSource } from 'typeorm';

export function Transactional() {
  return function (
    target: any,
    propertyKey: string,
    descriptor: PropertyDescriptor,
  ) {
    const originalMethod = descriptor.value;

    descriptor.value = async function (...args: any[]) {
      const dataSource: DataSource = this.dataSource;

      return dataSource.transaction(async (manager) => {
        // Inyectar manager temporal
        const originalManager = this.manager;
        this.manager = manager;

        try {
          return await originalMethod.apply(this, args);
        } finally {
          this.manager = originalManager;
        }
      });
    };

    return descriptor;
  };
}

// Uso
@Injectable()
export class OrderService {
  constructor(
    private readonly dataSource: DataSource,
    @InjectRepository(OrderEntity)
    private readonly orderRepository: Repository<OrderEntity>,
  ) {}

  private manager: EntityManager;

  @Transactional()
  async createOrder(dto: CreateOrderDto): Promise<Order> {
    // this.manager es el manager transaccional
    const order = this.manager.create(OrderEntity, dto);
    return this.manager.save(order);
  }
}

---

4. NIVELES DE AISLAMIENTO

Niveles Disponibles

Nivel	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Posible	Posible	Posible
READ COMMITTED	No	Posible	Posible
REPEATABLE READ	No	No	Posible
SERIALIZABLE	No	No	No

Configurar Nivel de Aislamiento

// Por defecto PostgreSQL usa READ COMMITTED
// Para operaciones criticas, usar SERIALIZABLE

async processPayment(orderId: string): Promise<void> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();

  // Configurar nivel de aislamiento
  await queryRunner.startTransaction('SERIALIZABLE');

  try {
    // Operaciones criticas aqui
    await queryRunner.commitTransaction();
  } catch (error) {
    await queryRunner.rollbackTransaction();

    // SERIALIZABLE puede fallar por conflictos
    if (error.code === '40001') {  // Serialization failure
      // Reintentar la transaccion
      return this.processPayment(orderId);
    }
    throw error;
  } finally {
    await queryRunner.release();
  }
}

---

5. LOCKS (BLOQUEOS)

5.1 Pessimistic Locking

// Bloquear fila para escritura (otros esperan)
async updateStock(productId: string, quantity: number): Promise<void> {
  return this.dataSource.transaction(async (manager) => {
    // Obtener producto con lock exclusivo
    const product = await manager.findOne(ProductEntity, {
      where: { id: productId },
      lock: { mode: 'pessimistic_write' },
    });

    if (product.stock < quantity) {
      throw new BadRequestException('Stock insuficiente');
    }

    product.stock -= quantity;
    await manager.save(product);
  });
}

5.2 Optimistic Locking (Version)

// Entity con columna de version
@Entity()
export class ProductEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  stock: number;

  @VersionColumn()  // Auto-incrementa en cada update
  version: number;
}

// El update falla si version cambio (alguien mas modifico)
async updateStock(productId: string, quantity: number): Promise<void> {
  const product = await this.productRepository.findOne({
    where: { id: productId },
  });

  product.stock -= quantity;

  try {
    await this.productRepository.save(product);
  } catch (error) {
    if (error instanceof OptimisticLockVersionMismatchError) {
      // Reintentar o notificar conflicto
      throw new ConflictException('El producto fue modificado. Reintente.');
    }
    throw error;
  }
}

Cuando Usar Cada Tipo

Scenario	Lock Recomendado
Alta concurrencia, conflictos raros	Optimistic
Operaciones financieras criticas	Pessimistic
Updates frecuentes al mismo registro	Pessimistic
Lecturas frecuentes, updates raros	Optimistic

---

6. PATRONES COMUNES

6.1 Unit of Work Pattern

// src/shared/database/unit-of-work.ts
@Injectable()
export class UnitOfWork {
  private queryRunner: QueryRunner;

  constructor(private readonly dataSource: DataSource) {}

  async begin(): Promise<void> {
    this.queryRunner = this.dataSource.createQueryRunner();
    await this.queryRunner.connect();
    await this.queryRunner.startTransaction();
  }

  getRepository<T>(entity: EntityTarget<T>): Repository<T> {
    return this.queryRunner.manager.getRepository(entity);
  }

  async commit(): Promise<void> {
    await this.queryRunner.commitTransaction();
    await this.queryRunner.release();
  }

  async rollback(): Promise<void> {
    await this.queryRunner.rollbackTransaction();
    await this.queryRunner.release();
  }
}

// Uso
@Injectable()
export class OrderService {
  constructor(private readonly uow: UnitOfWork) {}

  async createOrder(dto: CreateOrderDto): Promise<Order> {
    await this.uow.begin();

    try {
      const orderRepo = this.uow.getRepository(OrderEntity);
      const productRepo = this.uow.getRepository(ProductEntity);

      // Operaciones...

      await this.uow.commit();
      return order;
    } catch (error) {
      await this.uow.rollback();
      throw error;
    }
  }
}

6.2 Saga Pattern (Para transacciones distribuidas)

// Cuando no puedes usar transaccion de BD (microservicios)
interface SagaStep {
  execute(): Promise<void>;
  compensate(): Promise<void>;  // Revertir si falla
}

class CreateOrderSaga {
  private executedSteps: SagaStep[] = [];

  async execute(steps: SagaStep[]): Promise<void> {
    try {
      for (const step of steps) {
        await step.execute();
        this.executedSteps.push(step);
      }
    } catch (error) {
      // Compensar en orden inverso
      for (const step of this.executedSteps.reverse()) {
        await step.compensate();
      }
      throw error;
    }
  }
}

// Uso
const saga = new CreateOrderSaga();
await saga.execute([
  {
    execute: () => this.orderService.create(orderDto),
    compensate: () => this.orderService.delete(orderId),
  },
  {
    execute: () => this.inventoryService.reserve(items),
    compensate: () => this.inventoryService.release(items),
  },
  {
    execute: () => this.paymentService.charge(payment),
    compensate: () => this.paymentService.refund(payment),
  },
]);

6.3 Retry con Backoff

// Para manejar deadlocks y conflictos
async withRetry<T>(
  operation: () => Promise<T>,
  maxRetries: number = 3,
  baseDelay: number = 100,
): Promise<T> {
  let lastError: Error;

  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      lastError = error;

      // Solo reintentar errores transitorios
      const isRetryable =
        error.code === '40001' ||  // Serialization failure
        error.code === '40P01' ||  // Deadlock
        error.code === '55P03';    // Lock not available

      if (!isRetryable || attempt === maxRetries) {
        throw error;
      }

      // Exponential backoff con jitter
      const delay = baseDelay * Math.pow(2, attempt - 1) * (0.5 + Math.random());
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }

  throw lastError;
}

// Uso
async createOrder(dto: CreateOrderDto): Promise<Order> {
  return this.withRetry(() => this.createOrderTransaction(dto));
}

---

7. ERRORES COMUNES Y SOLUCIONES

7.1 Deadlock

// ❌ PROBLEMA: Deadlock por orden inconsistente de locks
// Transaction 1: Lock A, then B
// Transaction 2: Lock B, then A

// ✅ SOLUCION: Siempre lockear en el mismo orden
async transferFunds(fromId: string, toId: string, amount: number): Promise<void> {
  // Ordenar IDs para lockear siempre en el mismo orden
  const [firstId, secondId] = [fromId, toId].sort();

  return this.dataSource.transaction(async (manager) => {
    const firstAccount = await manager.findOne(AccountEntity, {
      where: { id: firstId },
      lock: { mode: 'pessimistic_write' },
    });

    const secondAccount = await manager.findOne(AccountEntity, {
      where: { id: secondId },
      lock: { mode: 'pessimistic_write' },
    });

    // Ahora operar
    const from = firstId === fromId ? firstAccount : secondAccount;
    const to = firstId === toId ? firstAccount : secondAccount;

    from.balance -= amount;
    to.balance += amount;

    await manager.save([from, to]);
  });
}

7.2 Connection Leak

// ❌ PROBLEMA: QueryRunner no liberado
async badMethod(): Promise<void> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  const data = await queryRunner.manager.find(Entity);
  // Si hay error aqui, queryRunner nunca se libera!

  await queryRunner.commitTransaction();
  await queryRunner.release();
}

// ✅ SOLUCION: Siempre usar try/finally
async goodMethod(): Promise<void> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    const data = await queryRunner.manager.find(Entity);
    await queryRunner.commitTransaction();
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release();  // SIEMPRE se ejecuta
  }
}

7.3 Long-Running Transactions

// ❌ PROBLEMA: Transaccion muy larga
async processAllOrders(): Promise<void> {
  return this.dataSource.transaction(async (manager) => {
    const orders = await manager.find(OrderEntity);  // Puede ser miles
    for (const order of orders) {
      await this.processOrder(order);  // Minutos de bloqueo
    }
  });
}

// ✅ SOLUCION: Procesar en batches con transacciones cortas
async processAllOrders(): Promise<void> {
  const BATCH_SIZE = 100;
  let processed = 0;

  while (true) {
    const orders = await this.orderRepository.find({
      where: { status: OrderStatus.PENDING },
      take: BATCH_SIZE,
    });

    if (orders.length === 0) break;

    // Transaccion corta por batch
    await this.dataSource.transaction(async (manager) => {
      for (const order of orders) {
        await this.processOrderInTransaction(manager, order);
      }
    });

    processed += orders.length;
  }
}

---

8. TESTING DE TRANSACCIONES

// src/modules/order/order.service.spec.ts
describe('OrderService', () => {
  let service: OrderService;
  let dataSource: DataSource;

  beforeEach(async () => {
    const module = await Test.createTestingModule({
      imports: [TypeOrmModule.forRoot(testConfig)],
      providers: [OrderService],
    }).compile();

    service = module.get(OrderService);
    dataSource = module.get(DataSource);
  });

  afterEach(async () => {
    // Limpiar despues de cada test
    await dataSource.synchronize(true);
  });

  describe('createOrder', () => {
    it('should rollback if stock is insufficient', async () => {
      // Arrange
      const product = await productRepo.save({
        name: 'Test',
        stock: 5,
        price: 100,
      });

      // Act & Assert
      await expect(
        service.createOrder({
          items: [{ productId: product.id, quantity: 10 }],
        }),
      ).rejects.toThrow('Stock insuficiente');

      // Verificar que stock no cambio (rollback funciono)
      const updatedProduct = await productRepo.findOne({
        where: { id: product.id },
      });
      expect(updatedProduct.stock).toBe(5);
    });

    it('should commit successfully with valid data', async () => {
      // ...
    });
  });
});

---

9. CHECKLIST DE TRANSACCIONES

Antes de implementar:
[ ] ¿Necesito atomicidad? (multiples operaciones)
[ ] ¿Que nivel de aislamiento necesito?
[ ] ¿Necesito locks? ¿Pesimista u optimista?

Durante implementacion:
[ ] QueryRunner siempre liberado (finally)
[ ] Rollback en catch
[ ] Manejo de errores transitorios (retry)
[ ] Transacciones lo mas cortas posible
[ ] Orden consistente de locks (evitar deadlocks)

Testing:
[ ] Test de rollback en error
[ ] Test de commit exitoso
[ ] Test de concurrencia (si aplica)

---

Version: 1.0.0 | Sistema: SIMCO | Tipo: Patron de Codigo