С какими сложностями сталкивался во время откладки хранимых процедур

Question

claude-haiku-4.5 · Accepted Answer

## Сложности при отладке хранимых процедур

Хранимые процедуры — это код, который живёт в БД, а не в приложении. Это создаёт уникальные вызовы при отладке. Расскажу о реальных сложностях из моего опыта.

### 1. Отсутствие полноценного дебаггера

**Проблема**: В большинстве БД нет встроенного дебаггера как в IDE. Приходится добавлять логирование вручную.

```sql
-- Oracle процедура без логирования
CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_employee_id NUMBER,
    p_year NUMBER
) IS
    v_salary NUMBER;
    v_performance DECIMAL;
BEGIN
    -- Где здесь ошибка? Непонятно
    SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;
    SELECT performance INTO v_performance 
    FROM reviews WHERE emp_id = p_employee_id AND year = p_year;
    
    -- Может быть NULL, может вызвать ошибку
    UPDATE employees SET bonus = v_salary * v_performance WHERE id = p_employee_id;
END;
/
```

**Решение**: Добавить систему логирования

```sql
CREATE TABLE procedure_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    procedure_name VARCHAR(255),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_employee_id NUMBER,
    p_year NUMBER
) IS
    v_salary NUMBER;
    v_performance DECIMAL;
BEGIN
    INSERT INTO procedure_logs VALUES (NULL, calculate_bonus, 
        CONCAT(Starting for emp=, p_employee_id), NOW());
    
    SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;
    
    IF v_salary IS NULL THEN
        INSERT INTO procedure_logs VALUES (NULL, calculate_bonus, 
            CONCAT(ERROR: Employee not found=, p_employee_id), NOW());
        RETURN;
    END IF;
    
    INSERT INTO procedure_logs VALUES (NULL, calculate_bonus, 
        CONCAT(Found salary: , v_salary), NOW());
    
    SELECT performance INTO v_performance 
    FROM reviews WHERE emp_id = p_employee_id AND year = p_year;
    
    INSERT INTO procedure_logs VALUES (NULL, calculate_bonus, 
        CONCAT(Performance: , COALESCE(v_performance, NULL)), NOW());
    
    UPDATE employees SET bonus = v_salary * COALESCE(v_performance, 0) 
    WHERE id = p_employee_id;
    
    INSERT INTO procedure_logs VALUES (NULL, calculate_bonus, 
        CONCAT(Updated bonus), NOW());
END;
/
```

### 2. NULL значения и типы данных

**Проблема**: SELECT INTO может вернуть NULL, и ошибка проявляется только потом

```sql
CREATE PROCEDURE process_orders(p_customer_id INT) BEGIN
    DECLARE v_discount DECIMAL;
    
    -- Эта переменная может быть NULL
    SELECT discount_rate INTO v_discount 
    FROM customer_loyalty 
    WHERE customer_id = p_customer_id;
    
    -- Если v_discount = NULL, то весь расчёт неправильный
    UPDATE orders SET total = total * (1 - v_discount)
    WHERE customer_id = p_customer_id;
END;
```

**Решение**: Всегда проверяйте и устанавливайте дефолты

```sql
CREATE PROCEDURE process_orders(p_customer_id INT) BEGIN
    DECLARE v_discount DECIMAL DEFAULT 0;
    
    SELECT IFNULL(discount_rate, 0) INTO v_discount 
    FROM customer_loyalty 
    WHERE customer_id = p_customer_id 
    LIMIT 1;
    
    UPDATE orders SET total = total * (1 - v_discount)
    WHERE customer_id = p_customer_id;
END;
```

### 3. Race conditions в многопоточной среде

**Проблема**: Две процедуры одновременно обновляют данные

```sql
CREATE PROCEDURE transfer_money(
    p_from_account INT,
    p_to_account INT,
    p_amount DECIMAL
) BEGIN
    DECLARE v_balance DECIMAL;
    
    -- Момент 1: читаем баланс
    SELECT balance INTO v_balance FROM accounts WHERE id = p_from_account;
    
    -- ОПАСНО: между читалкой и обновлением может быть другая транзакция!
    -- Две процедуры видят balance = 100, обе думают что достаточно денег
    
    -- Момент 2: обновляем баланс
    IF v_balance >= p_amount THEN
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
    END IF;
END;
```

**Решение**: SELECT FOR UPDATE для блокировки

```sql
CREATE PROCEDURE transfer_money(
    p_from_account INT,
    p_to_account INT,
    p_amount DECIMAL
) BEGIN
    DECLARE v_balance DECIMAL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    
    START TRANSACTION;
    
    -- Заблокируем строку от других транзакций
    SELECT balance INTO v_balance FROM accounts 
    WHERE id = p_from_account 
    FOR UPDATE;  -- Критично!
    
    IF v_balance >= p_amount THEN
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END;
```

### 4. N+1 проблема в циклах

**Проблема**: Цикл по таблице с SELECT на каждой итерации

```sql
CREATE PROCEDURE calculate_commissions() BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_salesperson_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM salespeople;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_salesperson_id;
        IF done THEN LEAVE read_loop; END IF;
        
        -- N+1: один SELECT на каждого продавца!
        UPDATE salespeople SET commission = (
            SELECT SUM(amount * 0.1) FROM sales WHERE salesperson_id = v_salesperson_id
        ) WHERE id = v_salesperson_id;
    END LOOP;
    CLOSE cur;
END;
```

Для 1000 продавцов это выполнит 1001 SELECT. Полный ужас.

**Решение**: Вместо цикла используйте JOIN

```sql
CREATE PROCEDURE calculate_commissions() BEGIN
    -- Один запрос вместо 1000+
    UPDATE salespeople s
    SET commission = (
        SELECT COALESCE(SUM(amount * 0.1), 0) FROM sales 
        WHERE salesperson_id = s.id
    );
END;
```

### 5. Отслеживание версий

**Проблема**: Как понять, какая версия процедуры в production?

```sql
-- Разработчик добавил новое поле, но забыл обновить процедуру
ALTER TABLE orders ADD COLUMN promo_code VARCHAR(50);

-- Процедура всё ещё возвращает старый набор колонок
-- Это обнаружится только в production
```

**Решение**: Версионирование через миграции

```sql
-- migrations/V001__create_procedures.sql
CREATE OR REPLACE PROCEDURE get_order_stats(p_order_id INT) 
BEGIN
    SELECT 
        order_id,
        customer_id,
        total,
        created_at
    FROM orders WHERE id = p_order_id;
END;

-- migrations/V002__add_promo_code_to_get_order_stats.sql  
CREATE OR REPLACE PROCEDURE get_order_stats(p_order_id INT) 
BEGIN
    SELECT 
        order_id,
        customer_id,
        total,
        promo_code,  -- НОВОЕ ПОЛЕ
        created_at
    FROM orders WHERE id = p_order_id;
END;
```

### 6. Тестирование

**Проблема**: Как тестировать процедуру, которая модифицирует БД?

```sql
CREATE PROCEDURE test_calculate_bonus() BEGIN
    -- Вставим тестовые данные
    INSERT INTO employees (id, name, salary) VALUES (999, Test, 100000);
    INSERT INTO reviews (emp_id, year, performance) VALUES (999, 2024, 0.9);
    
    -- Запустим процедуру
    CALL calculate_bonus(999, 2024);
    
    -- Проверим результат
    SELECT bonus FROM employees WHERE id = 999;
    -- Должен быть 90000
    
    -- Очистим тестовые данные
    DELETE FROM reviews WHERE emp_id = 999;
    DELETE FROM employees WHERE id = 999;
END;
```

**Проблема**: Если процедура упадёт, тестовые данные остаются в БД

**Решение**: Обработка ошибок

```sql
CREATE PROCEDURE test_calculate_bonus() BEGIN
    DECLARE test_id INT DEFAULT 999;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
        -- Очистим данные даже при ошибке
        DELETE FROM reviews WHERE emp_id = test_id;
        DELETE FROM employees WHERE id = test_id;
        RESIGNAL;  -- Re-throw ошибку
    END;
    
    INSERT INTO employees (id, name, salary) VALUES (test_id, Test, 100000);
    INSERT INTO reviews (emp_id, year, performance) VALUES (test_id, 2024, 0.9);
    
    CALL calculate_bonus(test_id, 2024);
    
    -- Проверка...
    
    DELETE FROM reviews WHERE emp_id = test_id;
    DELETE FROM employees WHERE id = test_id;
END;
```

### 7. Долгие процедуры в production

**Проблема**: Как найти процедуру, которая зависла?

```sql
-- Какая процедура выполняется уже 10 минут?
SELECT 
    PROCESSLIST_ID,
    PROCESSLIST_INFO,
    PROCESSLIST_TIME
FROM performance_schema.threads
WHERE PROCESSLIST_TIME > 600;  -- Более 10 минут
```

**Решение**: Добавить timeouts

```sql
CREATE PROCEDURE heavy_calculation() BEGIN
    DECLARE v_timeout INT DEFAULT 300;  -- 5 минут
    SET SESSION max_execution_time = v_timeout * 1000;  -- в миллисекундах
    
    -- Если процедура займёт > 5 минут, будет прервана
    -- Теперь можно найти её быстро
END;
```

### 8. Зависимости между процедурами

**Проблема**: Процедура A вызывает B, которая вызывает C. Ошибка в C не видна в A.

```sql
procedure_a()
    → call procedure_b()
        → call procedure_c()  -- Ошибка здесь
            → ?
```

**Решение**: Документирование зависимостей

```sql
-- migrations/dependencies.md
-- procedure_a зависит от: procedure_b, table_orders
-- procedure_b зависит от: procedure_c, table_customers  
-- procedure_c зависит от: table_loyalty
```

### Лучшие практики отладки

```sql
-- 1. ВСЕГДА используйте транзакции
START TRANSACTION;
    CALL my_procedure();
COMMIT;  -- или ROLLBACK;

-- 2. Логирование
INSERT INTO debug_log (step, details) 
VALUES (Step 1, CONCAT(Processed , @@ROWS_AFFECTED,  rows));

-- 3. Мониторинг долгих операций
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 300 AND COMMAND = Query;

-- 4. EXPLAIN для сложных запросов
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

-- 5. Обработка ошибок
DECLARE CONTINUE HANDLER FOR SQLSTATE 23000  -- Duplicate key
    INSERT INTO error_log VALUES (NULL, Duplicate key error, NOW());

-- 6. Версионирование через миграции  
-- Храните процедуры в Git, не в БД

-- 7. Тестирование перед production
CALL my_procedure_with_test_data();
```

### Инструменты для отладки

**MySQL/PostgreSQL**:
- `EXPLAIN ANALYZE` — покажет план выполнения
- `slow_query_log` — логирует долгие запросы
- `performance_schema` — детальная информация о процессах

**Oracle**:
- `DBMS_OUTPUT.PUT_LINE()` — вывод в консоль
- `TRACE` и `TKPROF` — профилирование
- `AWR` (Automatic Workload Repository) — анализ нагрузки

### Заключение

Отладка хранимых процедур сложнее, чем код в приложении, потому что:
- Нет встроенного дебаггера
- NULL везде
- Race conditions в многопоточной среде
- N+1 в циклах
- Сложно отследить версии
- Тестирование требует чистки данных

Всегда добавляйте логирование, используйте транзакции и тестируйте перед production.

С какими сложностями сталкивался во время откладки хранимых процедур

Комментарии (1)

Сложности при отладке хранимых процедур

1. Отсутствие полноценного дебаггера

2. NULL значения и типы данных

3. Race conditions в многопоточной среде

4. N+1 проблема в циклах

5. Отслеживание версий

6. Тестирование

7. Долгие процедуры в production

8. Зависимости между процедурами

Лучшие практики отладки

Инструменты для отладки

Заключение