Nexus - AI Task Orchestrator

17-database-lock-analysis
Procedimiento: An├ílisis de Bloqueos y Deadlocks

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
15113 caracteres
# Procedimiento: An├ílisis de Bloqueos y Deadlocks

## Metadata
- **ID**: PROC-17
- **Frecuencia**: Bajo demanda (ante problemas de rendimiento) o Diario
- **Duraci├│n estimada**: 15-30 min
- **Requiere**: Acceso SQL Server (VIEW SERVER STATE) o PostgreSQL (pg_stat_activity)
- **Dependencias**: Ninguna
- **Bloquea**: Ninguno (solo lectura y diagn├│stico)
- **Agentes**: database-optimizer

---

## Objetivo

Analizar y diagnosticar problemas de bloqueos y deadlocks:
1. Identificar sesiones bloqueadas actualmente
2. Encontrar la sesi├│n ra├¡z que causa el bloqueo
3. Analizar historial de deadlocks
4. Proponer soluciones (├¡ndices, refactoring de queries, etc.)

---

## Umbrales de Alerta

| M├®trica | WARNING | CRITICAL |
|---------|---------|----------|
| Sesiones bloqueadas | >5 | >20 |
| Tiempo de bloqueo | >30 seg | >5 min |
| Deadlocks/hora | >1 | >5 |
| Cadena de bloqueo | >3 niveles | >5 niveles |

---

## Checklist Ejecutable

### 1. Pre-verificaciones

- [ ] Conectar a la base de datos
- [ ] Verificar permisos de lectura en DMV/pg_stat

---

## Para SQL Server

### 2. Bloqueos Activos Ahora

```sql
-- Vista r├ípida de bloqueos actuales
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocking_session,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.wait_resource,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    t.text AS blocked_query
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;
```

- [ ] Identificar sesiones bloqueadas
- [ ] Documentar tiempo de espera

### 3. Cadena Completa de Bloqueos

```sql
-- ├ürbol de bloqueos (qui├®n bloquea a qui├®n)
WITH BlockingTree AS (
    -- Sesiones que bloquean a otros
    SELECT
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        0 AS level,
        CAST(session_id AS VARCHAR(MAX)) AS blocking_chain
    FROM sys.dm_exec_requests
    WHERE blocking_session_id = 0
        AND session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0)

    UNION ALL

    -- Sesiones bloqueadas
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        bt.level + 1,
        bt.blocking_chain + ' -> ' + CAST(r.session_id AS VARCHAR(10))
    FROM sys.dm_exec_requests r
    JOIN BlockingTree bt ON r.blocking_session_id = bt.session_id
    WHERE r.blocking_session_id > 0
)
SELECT
    bt.session_id,
    bt.blocking_session_id,
    bt.level,
    bt.blocking_chain,
    bt.wait_type,
    bt.wait_time / 1000 AS wait_seconds,
    s.login_name,
    s.host_name,
    s.program_name,
    (SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS query
FROM BlockingTree bt
JOIN sys.dm_exec_sessions s ON bt.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON bt.session_id = r.session_id
ORDER BY bt.blocking_chain;
```

- [ ] Mapear ├írbol de bloqueos
- [ ] Identificar sesi├│n ra├¡z (level = 0)

### 4. Sesi├│n Ra├¡z del Bloqueo (Detalle)

```sql
-- Detalle de la sesi├│n que est├í causando el bloqueo
DECLARE @blocking_session_id INT = (
    SELECT TOP 1 blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
);

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    s.last_request_end_time,
    c.client_net_address,
    r.command,
    r.wait_type,
    r.wait_resource,
    t.text AS current_query,
    p.query_plan
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE s.session_id = @blocking_session_id;
```

- [ ] Analizar query de la sesi├│n bloqueadora
- [ ] Revisar si est├í ejecutando algo o est├í idle

### 5. Historial de Deadlocks (Extended Events)

```sql
-- Deadlocks recientes del System Health session
SELECT
    XEvent.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph,
    XEvent.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
        AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
ORDER BY deadlock_time DESC;
```

- [ ] Revisar deadlocks ├║ltimas 24h
- [ ] Analizar patrones (mismas tablas, mismas queries)

### 6. Objetos M├ís Bloqueados

```sql
-- Recursos que m├ís bloqueos causan
SELECT
    resource_type,
    resource_description,
    resource_database_id,
    DB_NAME(resource_database_id) AS database_name,
    resource_associated_entity_id,
    OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS object_name,
    request_mode,
    COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE resource_type != 'DATABASE'
GROUP BY resource_type, resource_description, resource_database_id,
         resource_associated_entity_id, request_mode
ORDER BY COUNT(*) DESC;
```

- [ ] Identificar tablas/├¡ndices m├ís bloqueados

### 7. Acciones para Resolver

```sql
-- Opci├│n 1: Terminar sesi├│n bloqueadora (PRECAUCI├ôN)
-- KILL [session_id];

-- Opci├│n 2: Verificar si necesita ├¡ndice
-- Ejecutar PROC-13 para la tabla afectada

-- Opci├│n 3: Sugerir NOLOCK si aplica (solo lecturas no cr├¡ticas)
-- SELECT ... FROM table WITH (NOLOCK)

-- Opci├│n 4: Reducir tiempo de transacci├│n
-- Revisar c├│digo de la aplicaci├│n
```

---

## Para PostgreSQL

### 2. Bloqueos Activos Ahora

```sql
-- Vista r├ípida de bloqueos actuales
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.application_name AS blocked_app,
    blocked.client_addr AS blocked_addr,
    blocked.wait_event_type,
    blocked.wait_event,
    NOW() - blocked.query_start AS wait_duration,
    blocked.state AS blocked_state,
    LEFT(blocked.query, 200) AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.state AS blocking_state,
    LEFT(blocking.query, 200) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted
ORDER BY blocked.query_start;
```

- [ ] Identificar sesiones bloqueadas
- [ ] Documentar duraci├│n del bloqueo

### 3. ├ürbol de Bloqueos

```sql
-- Cadena de bloqueos recursiva
WITH RECURSIVE lock_tree AS (
    -- Sesiones que bloquean pero no son bloqueadas
    SELECT
        a.pid,
        a.usename,
        a.application_name,
        a.query,
        a.state,
        0 AS level,
        ARRAY[a.pid] AS path
    FROM pg_stat_activity a
    WHERE a.pid IN (
        SELECT DISTINCT blocking_locks.pid
        FROM pg_locks blocked_locks
        JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
            AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
            AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
            AND blocked_locks.pid != blocking_locks.pid
        WHERE NOT blocked_locks.granted AND blocking_locks.granted
    )
    AND a.pid NOT IN (
        SELECT blocked_locks.pid
        FROM pg_locks blocked_locks
        WHERE NOT blocked_locks.granted
    )

    UNION ALL

    -- Sesiones bloqueadas
    SELECT
        blocked.pid,
        blocked.usename,
        blocked.application_name,
        blocked.query,
        blocked.state,
        lt.level + 1,
        lt.path || blocked.pid
    FROM pg_stat_activity blocked
    JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
    JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
        AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
        AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
        AND blocked_locks.pid != blocking_locks.pid
    JOIN lock_tree lt ON blocking_locks.pid = lt.pid
    WHERE NOT blocked_locks.granted
        AND blocked.pid != ALL(lt.path)
)
SELECT
    pid,
    usename,
    application_name,
    level,
    array_to_string(path, ' -> ') AS blocking_chain,
    state,
    LEFT(query, 100) AS query
FROM lock_tree
ORDER BY path;
```

- [ ] Mapear ├írbol de bloqueos
- [ ] Identificar PID ra├¡z

### 4. Tipos de Locks Actuales

```sql
-- Detalle de locks por tabla
SELECT
    l.locktype,
    d.datname AS database,
    COALESCE(c.relname, l.relation::text) AS relation,
    l.page,
    l.tuple,
    l.virtualxid,
    l.transactionid,
    l.mode,
    l.granted,
    a.pid,
    a.usename,
    a.application_name,
    a.state,
    NOW() - a.query_start AS duration
FROM pg_locks l
LEFT JOIN pg_class c ON l.relation = c.oid
LEFT JOIN pg_database d ON l.database = d.oid
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.pid != pg_backend_pid()
ORDER BY l.relation, l.granted DESC;
```

- [ ] Revisar tipos de lock (AccessShare, RowExclusive, etc.)

### 5. Deadlocks (Log de PostgreSQL)

```sql
-- PostgreSQL registra deadlocks en el log del servidor
-- Buscar en: /var/log/postgresql/postgresql-*.log
-- Patr├│n: "deadlock detected"

-- Tambi├®n se puede habilitar logging:
SHOW log_lock_waits;  -- Debe ser 'on' para loguear esperas
SHOW deadlock_timeout;  -- Por defecto 1s

-- Ver configuraci├│n actual
SELECT name, setting
FROM pg_settings
WHERE name IN ('log_lock_waits', 'deadlock_timeout', 'lock_timeout');
```

- [ ] Verificar configuraci├│n de logging
- [ ] Revisar logs del servidor si hay deadlocks

### 6. Objetos M├ís Bloqueados

```sql
-- Tablas con m├ís locks
SELECT
    c.relname AS table_name,
    l.mode,
    COUNT(*) AS lock_count,
    COUNT(*) FILTER (WHERE NOT l.granted) AS waiting_count
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
WHERE c.relkind = 'r'
GROUP BY c.relname, l.mode
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;
```

- [ ] Identificar tablas con m├ís contenci├│n

### 7. Acciones para Resolver

```sql
-- Opci├│n 1: Cancelar query (graceful)
-- SELECT pg_cancel_backend(pid);

-- Opci├│n 2: Terminar conexi├│n (force)
-- SELECT pg_terminate_backend(pid);

-- Opci├│n 3: Configurar timeouts
-- SET lock_timeout = '10s';
-- SET statement_timeout = '5min';

-- Opci├│n 4: Revisar nivel de aislamiento
SHOW transaction_isolation;
```

---

## Output Esperado

```
====== PROC-17 COMPLETADO [TIMESTAMP] ======
Motor: SQL Server / PostgreSQL
Base de datos: [nombre]

BLOQUEOS ACTUALES: X sesiones bloqueadas

CADENA DE BLOQUEO:
PID [ra├¡z] -> PID [nivel1] -> PID [nivel2]
  Ra├¡z: [query del bloqueador]
  Estado: [running/idle]
  Duraci├│n: X segundos

SESI├ôN RA├ìZ (BLOQUEADORA):
- PID: [id]
- Usuario: [login]
- Aplicaci├│n: [programa]
- Query: [query truncada]
- Estado: [running/sleeping/idle]
- Recomendaci├│n: [acci├│n]

HISTORIAL DEADLOCKS (24h): X ocurrencias
- Tablas afectadas: [lista]
- Patr├│n: [descripci├│n si hay]

OBJETOS M├üS BLOQUEADOS:
1. [tabla]: X locks
2. [tabla]: Y locks

RECOMENDACIONES:
1. [acci├│n prioritaria]
2. [acci├│n secundaria]
```

---

## Criterios de ├ëxito

- [ ] No hay bloqueos > 30 segundos
- [ ] No hay deadlocks en ├║ltimas 24h
- [ ] Cadena de bloqueo < 3 niveles
- [ ] Identificada causa ra├¡z si hay bloqueos

---

## Alertas y Escalaci├│n

| Severidad | Condici├│n | Acci├│n |
|-----------|-----------|--------|
| CRITICAL | Bloqueo > 5 min | WhatsApp inmediato + intervenci├│n |
| CRITICAL | >20 sesiones bloqueadas | Escalar a DBA |
| WARNING | Bloqueo > 30 seg | Investigar causa |
| WARNING | >1 deadlock/hora | Revisar dise├▒o de transacciones |
| INFO | Sin bloqueos | Documentar estado saludable |

---

## Automatizaci├│n

En ejecuci├│n no-interactiva:
1. Detectar bloqueos actuales
2. Mapear cadena de bloqueos
3. Generar scripts de resoluci├│n (no ejecutar)
4. Alertar seg├║n umbrales
5. Nunca ejecutar KILL/terminate autom├íticamente

---


---

## Output Estructurado (Nexus)

Al finalizar, el agente DEBE generar un bloque JSON con el siguiente formato para que Nexus pueda procesarlo automaticamente:

```json
{
  "result": "success",
  "summary": "Ejecucion de PROC-17 completada. [Descripcion breve de resultados]",
  "metrics": {
    "issues_found": 0,
    "issues_resolved": 0,
    "queries_analyzed": 50, "slow_queries": 3,
    "custom": {
      "procedure_specific_metric": "value"
    }
  },
  "backlog_items": [
    {
      "title": "Titulo del item de seguimiento",
      "description": "Descripcion detallada si se requiere accion futura",
      "priority": "medium",
      "type": "improvement",
      "tags": ["proc-17"]
    }
  ],
  "next_steps": [
    "Accion recomendada 1",
    "Accion recomendada 2"
  ],
  "warnings": [
    "Advertencias encontradas durante la ejecucion"
  ]
}
```

**Campos requeridos:**
- `result`: `"success"` | `"partial"` | `"failed"`
- `summary`: Resumen ejecutivo en 1-3 lineas

**Metricas especificas de este procedure:**
- locks_detected, deadlocks, blocking_queries

**Criterios de resultado:**
- `success`: Procedimiento completado sin errores criticos
- `partial`: Completado con algunos problemas menores o items pendientes
- `failed`: Error critico o no se pudo completar

## Historial de Ejecuciones

| Fecha | Proyecto | Bloqueados | Max Tiempo | Deadlocks | Causa Ra├¡z | Acci├│n |
|-------|----------|------------|------------|-----------|------------|--------|
| | | | | | | |