Зачем нужен TCL в БД?

TCL в базах данных

TCL (Transaction Control Language) — это подмножество SQL команд для управления транзакциями в базах данных. Транзакции критичны для обеспечения консистентности данных и целостности БД, особенно в production системах.

Что такое транзакция

Транзакция — это логическая единица работы, состоящая из нескольких операций (INSERT, UPDATE, DELETE, SELECT), которые либо все выполняются успешно, либо все откатываются назад.

Примеры TCL команд:
- BEGIN / START TRANSACTION — начать транзакцию
- COMMIT — подтвердить (сохранить) все изменения
- ROLLBACK — отменить все изменения
- SAVEPOINT — промежуточная точка в транзакции

Классический пример: Перевод денег между счетами

Без транзакции (грозит потерей денег!)

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

# Создаем счета
c.execute('CREATE TABLE accounts (id INT, balance DECIMAL)')
c.execute('INSERT INTO accounts VALUES (1, 1000)')  # Alice: 1000
c.execute('INSERT INTO accounts VALUES (2, 500)')   # Bob: 500

# ❌ Без транзакции — опасно!
def transfer_money_bad(from_id, to_id, amount):
    # Шаг 1: Снимаем со счета отправителя
    c.execute(f'UPDATE accounts SET balance = balance - ? WHERE id = ?',
              (amount, from_id))
    
    # ОПАСНОСТЬ: Если здесь произойдет ошибка (отключится электричество,
    # упадет приложение и т.д.), то деньги снимут, но не добавят!
    
    # Шаг 2: Добавляем на счет получателя
    c.execute(f'UPDATE accounts SET balance = balance + ? WHERE id = ?',
              (amount, to_id))
    
    conn.commit()

# Если происходит ошибка между шагами:
# Alice: 1000 → 900 (снимают)
# [ОШИБКА! Приложение упало]
# Bob: 500 → 500 (не добавляют)
# ПОТЕРЯ 100 руб!

С транзакцией (правильно)

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

# ✅ С транзакцией
def transfer_money_good(from_id, to_id, amount):
    try:
        c.execute('BEGIN')  # Начинаем транзакцию
        
        # Шаг 1: Снимаем
        c.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?',
                  (amount, from_id))
        
        # Проверяем, достаточно ли денег
        balance = c.execute('SELECT balance FROM accounts WHERE id = ?',
                           (from_id,)).fetchone()[0]
        if balance < 0:
            raise ValueError('Insufficient funds')
        
        # Шаг 2: Добавляем
        c.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?',
                  (amount, to_id))
        
        c.execute('COMMIT')  # Подтверждаем ВСЕ изменения
        print('Transfer successful')
        
    except Exception as e:
        c.execute('ROLLBACK')  # Откатываем ВСЕ изменения
        print(f'Transfer failed: {e}')

transfer_money_good(1, 2, 100)
# Результат: ЛИБО обе операции выполнены, ЛИБО ни одна

Основные свойства транзакций: ACID

A — Atomicity (Атомарность)

Транзакция либо выполняется полностью, либо не выполняется совсем.
Нет "полуготовых" состояний.

Примеры:
✓ Transfer 100 AND add 100 both succeed
✗ Transfer 100 but fail to add 100 — ROLLBACK both

C — Consistency (Консистентность)

БД переходит из одного корректного состояния в другое.
Все правила и ограничения соблюдаются.

Примеры:
✓ account.balance >= 0  (никогда не отрицательное)
✓ total(all_accounts) не меняется при передаче денег

I — Isolation (Изоляция)

Параллельные транзакции не мешают друг другу.

Проблема без изоляции:

Транзакция 1: SELECT balance FROM account WHERE id=1  // 1000
Транзакция 2: UPDATE account SET balance=500 WHERE id=1  // Меняет
Транзакция 2: COMMIT
Транзакция 1: Видит 500 вместо 1000 (dirty read!)

D — Durability (Надежность)

Одна коммитленные данные сохраняются навсегда.
Даже если БД упадет, данные не потеряются.

Примеры:
Коммитили транзакцию → данные на диске
Упала БД → восстанавливает из диска

Уровни изоляции в PostgreSQL

1. Read Uncommitted (самый небезопасный)

# Видим "грязные" данные (не коммиченные еще)

Транзакция 1:
  BEGIN;
  UPDATE account SET balance = 100 WHERE id = 1;
  -- Еще не COMMIT

Транзакция 2:
  BEGIN;
  SELECT balance FROM account WHERE id = 1;  -- Видит 100 (dirty!)
  COMMIT;

Транзакция 1:
  ROLLBACK;  -- Откатываем! Трансакция 2 работала с фантомом

2. Read Committed (по умолчанию в большинстве БД)

# Видим только коммиченные данные

Транзакция 1:
  BEGIN;
  UPDATE account SET balance = 100 WHERE id = 1;
  -- Еще не COMMIT

Транзакция 2:
  BEGIN;
  SELECT balance FROM account WHERE id = 1;  -- Видит старое значение
  COMMIT;

Транзакция 1:
  COMMIT;  # Теперь видно новое значение

3. Repeatable Read

Внутри транзакции всегда видим одну версию данных.
Даже если другая транзакция их изменила и коммитила.

4. Serializable (самый безопасный, медленный)

Транзакции выполняются так, как будто идут одна за другой.
Без параллельных конфликтов.

Примеры TCL в Python

С использованием контекстного менеджера

import psycopg2

conn = psycopg2.connect('dbname=mydb user=postgres')

try:
    with conn:
        cur = conn.cursor()
        # Транзакция автоматически начита
        cur.execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
        cur.execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2')
        # Если здесь ошибка — автоматический ROLLBACK
        # Если успех — автоматический COMMIT
except Exception as e:
    print(f'Error: {e}')
finally:
    conn.close()

С явными COMMIT/ROLLBACK

import psycopg2

conn = psycopg2.connect('dbname=mydb user=postgres')
conn.autocommit = False  # Явное управление транзакциями
cur = conn.cursor()

try:
    cur.execute('BEGIN')
    
    # Операция 1
    cur.execute('UPDATE users SET status = ? WHERE id = ?',
               ('active', 1))
    
    # Проверка
    cur.execute('SELECT * FROM users WHERE id = 1')
    result = cur.fetchone()
    if not result:
        raise ValueError('User not found!')
    
    # Операция 2
    cur.execute('INSERT INTO audit_log VALUES (?, ?)',
               (1, 'status_changed'))
    
    conn.commit()  # Все успешно
    print('Success')
    
except Exception as e:
    conn.rollback()  # Откатываем все
    print(f'Failed: {e}')
finally:
    cur.close()
    conn.close()

Savepoints для частичных откатов

import psycopg2

conn = psycopg2.connect('dbname=mydb')
cur = conn.cursor()

try:
    cur.execute('BEGIN')
    
    # Операция 1
    cur.execute('INSERT INTO users VALUES (1, "Alice")')
    
    # Сохраняем точку
    cur.execute('SAVEPOINT sp1')
    
    # Операция 2 (может пойти не по плану)
    try:
        cur.execute('INSERT INTO users VALUES (1, "Bob")')  # Дублicate!
    except:
        # Откатываем только до SAVEPOINT, не всю транзакцию
        cur.execute('ROLLBACK TO SAVEPOINT sp1')
    
    # Операция 3
    cur.execute('INSERT INTO users VALUES (2, "Charlie")')
    
    conn.commit()
    # Результат: Alice и Charlie сохранены, Bob откачен
    
except Exception as e:
    conn.rollback()
    print(f'Error: {e}')
finally:
    cur.close()
    conn.close()

Когда особенно важны транзакции

1. Финансовые системы

# Переводы денег ДОЛЖНЫ быть в транзакции
# Иначе деньги могут потеряться или продублироваться

2. Заказы в e-commerce

# Все операции должны выполниться вместе:
# 1. Снять со счета
# 2. Зарезервировать товар
# 3. Создать заказ
# 4. Отправить уведомление (если нужно)

3. Синхронизация связанных таблиц

# Если обновляем users и заодно обновляем users_metadata,
# нужно, чтобы оба обновления либо прошли, либо откатились

Общее правило

Любая логически связанная группа операций должна быть в одной транзакции.

Если одна операция важна для другой, они должны успешны вместе или откатиться вместе. Иначе БД окажется в несогласованном состоянии.