Prevencion y Resolucion de Deadlocks en Transacciones SQL

Los deadlocks ocurren cuando dos transacciones mantienen locks que la otra necesita, creando una espera circular. Esta recipe cubre detectar patrones de deadlock, aplicar ordenamiento consistente de locks, elegir niveles de aislamiento sabiamente e implementar logica de reintento client-side para resiliencia de base de datos en produccion.

Cuando Usar Esto

• Transacciones concurrentes fallan intermitentemente con errores de deadlock
• Operaciones batch y transacciones user-facing compiten por las mismas filas
• Se requiere row-level locking pero el rendimiento debe permanecer aceptable

Problema

Dos transferencias de fondos concurrentes entre cuentas A y B deadlock porque la Transaccion 1 lockea A y espera por B, mientras la Transaccion 2 lockea B y espera por A.

Solucion

1. Ordenamiento Consistente de Locks

// transactions/TransferService.ts
class TransferService {
  async transfer(fromId: string, toId: string, amount: number): Promise<void> {
    // Siempre lockea en orden consistente (ej. por ID de cuenta)
    const [first, second] = [fromId, toId].sort();

    await db.transaction(async (trx) => {
      const fromAccount = await trx('accounts')
        .where('id', first)
        .forUpdate()
        .first();

      const toAccount = await trx('accounts')
        .where('id', second)
        .forUpdate()
        .first();

      await trx('accounts')
        .where('id', fromId)
        .decrement('balance', amount);

      await trx('accounts')
        .where('id', toId)
        .increment('balance', amount);
    });
  }
}

2. Optimistic Locking (Sin Locks de Base de Datos)

// transactions/OptimisticUpdate.ts
class InventoryService {
  async updateStock(productId: string, delta: number): Promise<boolean> {
    const result = await db('inventory')
      .where('product_id', productId)
      .where('version', db('inventory')
        .select('version')
        .where('product_id', productId)
      )
      .update({
        quantity: db.raw('quantity + ?', [delta]),
        version: db.raw('version + 1'),
      });

    return result > 0;
  }
}

3. Seleccion de Nivel de Aislamiento

-- Read Committed: default, previene dirty reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read: previene non-repeatable reads (mayor contencion de locks)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable: aislamiento completo, mayor riesgo de deadlock
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4. Logica de Reintento Resiliente a Deadlocks

// transactions/RetryWithBackoff.ts
async function executeWithRetry<T>(
  operation: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (isDeadlockError(error) && attempt < maxRetries) {
        const delay = Math.pow(2, attempt) * 100 + Math.random() * 100;
        await sleep(delay);
        continue;
      }
      throw error;
    }
  }

  throw new Error('Max retries exceeded');
}

await executeWithRetry(() => transferService.transfer('A', 'B', 100));

5. Detectando Deadlocks en PostgreSQL

-- Ver locks actuales
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Log de detalles de deadlock
SHOW log_lock_waits;

Como Funciona

• Ordenamiento consistente previene esperas circulares adquiriendo locks siempre en la misma secuencia
• Optimistic locking usa versionado en lugar de locks de base de datos, reduciendo contencion
• Niveles de aislamiento intercambian consistencia contra concurrencia; niveles mas bajos tienen menos deadlocks
• Logica de reintento con exponential backoff maneja deadlocks transitorios que se resuelven rapidamente

Consideraciones de Produccion

• Manten transacciones cortas para minimizar duracion de locks
• Usa SELECT FOR UPDATE SKIP LOCKED para workloads tipo queue. Consulta Locks and Mutexes para coordinación.
• Monitorea pg_stat_database.deadlocks para trackear frecuencia de deadlocks

Errores Comunes

• Lockear filas en diferente orden en diferentes partes de la aplicacion
• Usar SELECT FOR UPDATE en filas innecesarias, incrementando scope del lock
• No reintentar despues de errores de deadlock, causando fallos user-facing

Tips de Rendimiento

1. Usa pgbench para reproducir deadlocks. Simula patrones de acceso concurrente:

pgbench -i -s 10 mydb
pgbench -c 20 -j 4 -T 60 -f deadlock_test.sql mydb

2. Monitorea el conteo de pg_locks. Un número alto de locks indica contención:

SELECT count(*) AS total_locks, count(*) FILTER (WHERE NOT granted) AS waiting_locks
FROM pg_locks;

3. Usa idle_in_transaction_session_timeout para prevenir transacciones atascadas. Las transacciones idle pero no commiteadas mantienen locks indefinidamente:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';

4. Batch FOR UPDATE con SKIP LOCKED para throughput de colas. Procesa múltiples jobs por transacción para reducir round trips:

BEGIN;
SELECT id FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 50;
UPDATE jobs SET status = 'processing' WHERE id IN (...);
COMMIT;

5. Usa lock_timeout combinado con reintento para degradación graceful. Establece un lock timeout corto y reintenta con backoff:

SET lock_timeout = '2s';
-- Si la adquisición del lock falla (55P03), reintenta con backoff