Что такое ROLLBACK в SQL?

Что такое ROLLBACK в SQL

ROLLBACK — это SQL команда, которая отменяет все изменения, сделанные в текущей транзакции, и возвращает базу данных в состояние, в котором она была до начала транзакции. Это один из ключевых элементов ACID принципов, обеспечивающих целостность данных.

Основной синтаксис

import sqlite3

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

# Создаём таблицу
cursor.execute('CREATE TABLE accounts (id INTEGER, balance INTEGER)')
cursor.execute('INSERT INTO accounts VALUES (1, 1000)')
conn.commit()  # Сохраняем

# Начинаем транзакцию
cursor.execute('BEGIN')
cursor.execute('UPDATE accounts SET balance = 500 WHERE id = 1')
print('После UPDATE, до ROLLBACK: balance = 500')

# Откатываем все изменения
conn.rollback()

# Проверяем: баланс вернулся к 1000
cursor.execute('SELECT balance FROM accounts WHERE id = 1')
print(f'После ROLLBACK: balance = {cursor.fetchone()[0]}')  # 1000

Когда ROLLBACK нужен

1. При ошибке в транзакции:

try:
    cursor.execute('BEGIN')
    cursor.execute('INSERT INTO users (name) VALUES ("Alice")')
    cursor.execute('INSERT INTO invalid_table VALUES (1)')  # Ошибка!
    conn.commit()
except Exception as e:
    conn.rollback()  # Отменяем всё
    print(f'Ошибка: {e}, все изменения отменены')

2. При нарушении бизнес-логики:

# Переводим деньги между счётами
cursor.execute('BEGIN')

try:
    # Снимаем со счёта 1
    cursor.execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
    
    # Добавляем на счёт 2
    cursor.execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2')
    
    # Проверяем данные
    cursor.execute('SELECT SUM(balance) FROM accounts')
    total = cursor.fetchone()[0]
    
    if total != expected_total:  # Проверка целостности
        raise Exception('Total amount mismatch')
    
    conn.commit()  # Успех
except:
    conn.rollback()  # Откатываем обе операции
    raise

3. При deadlock:

# PostgreSQL автоматически откатывает при deadlock
try:
    cursor.execute('SELECT * FROM accounts FOR UPDATE')
    cursor.execute('UPDATE accounts SET balance = 0')  # Deadlock!
except psycopg2.extensions.TransactionRollbackError:
    # Транзакция автоматически откачена
    print('Deadlock detected, transaction rolled back')
    conn.rollback()

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

READ COMMITTED (по умолчанию):

cursor.execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED')
cursor.execute('BEGIN')
# При ROLLBACK откатываются только локальные изменения
cursor.execute('UPDATE accounts SET balance = 0')
conn.rollback()

REPEATABLE READ:

cursor.execute('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ')
cursor.execute('BEGIN')
# При ROLLBACK откатываются строки, заблокированные в этой транзакции
cursor.execute('SELECT * FROM accounts FOR UPDATE')
conn.rollback()

SERIALIZABLE:

cursor.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
cursor.execute('BEGIN')
# Защита от phantom reads и serialization conflicts
conn.rollback()

SAVEPOINT — частичный откат

import psycopg2

conn = psycopg2.connect('dbname=test')
cursor = conn.cursor()

cursor.execute('BEGIN')

# Операция 1
cursor.execute('INSERT INTO users VALUES (1, "Alice")')

# Создаём точку сохранения
cursor.execute('SAVEPOINT sp1')

# Операция 2
cursor.execute('INSERT INTO users VALUES (2, "Bob")')

# Откатываем только до SAVEPOINT
cursor.execute('ROLLBACK TO SAVEPOINT sp1')

# Теперь в БД только Alice, Bob откачен
conn.commit()

# Проверяем
cursor.execute('SELECT COUNT(*) FROM users')
print(cursor.fetchone()[0])  # 1 (только Alice)

Вложенные SAVEPOINT'ы

import psycopg2

conn = psycopg2.connect('dbname=test')
cursor = conn.cursor()

try:
    cursor.execute('BEGIN')
    
    # Уровень 1
    cursor.execute('INSERT INTO users VALUES (1, "Alice")')
    cursor.execute('SAVEPOINT level1')
    
    try:
        # Уровень 2
        cursor.execute('INSERT INTO users VALUES (2, "Bob")')
        cursor.execute('SAVEPOINT level2')
        
        # Ошибка на уровне 2
        cursor.execute('INSERT INTO users VALUES (2, "Bob")')  # Дублирование
    except:
        # Откатываем только уровень 2
        cursor.execute('ROLLBACK TO SAVEPOINT level2')
    
    # Уровень 1 всё ещё актуален, Alice в БД
    conn.commit()
except:
    conn.rollback()

ROLLBACK vs COMMIT

Таблица сравнения:

# COMMIT — сохраняет всё
cursor.execute('BEGIN')
cursor.execute('UPDATE accounts SET balance = 100')
conn.commit()  # ✅ Изменения сохранены навсегда

# ROLLBACK — отменяет всё
cursor.execute('BEGIN')
cursor.execute('UPDATE accounts SET balance = 100')
conn.rollback()  # ❌ Изменения отменены, баланс не изменился

Использование с context manager

import psycopg2
from contextlib import contextmanager

@contextmanager
def transaction(conn):
    try:
        yield conn
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(f'Transaction rolled back due to: {e}')
        raise

# Использование
with transaction(conn) as conn:
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET active = true')
    # Если будет ошибка, ROLLBACK автоматически
    # Если успех, COMMIT автоматически

SQLAlchemy автоматический откат

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@localhost/db')

with Session(engine) as session:
    try:
        user = User(name='Alice')
        session.add(user)
        session.flush()  # Отправляет в БД, но не коммитит
        
        # Если ошибка здесь
        raise ValueError('Invalid user')
        
        session.commit()
    except:
        session.rollback()  # Автоматический откат
        raise

Django ORM откат

from django.db import transaction

try:
    with transaction.atomic():
        user = User.objects.create(name='Alice')
        # Если ошибка здесь
        raise ValueError('Error')
        # ROLLBACK автоматически
except ValueError:
    print('User not created, transaction rolled back')

ROLLBACK и лог-файлы

При ROLLBACK транзакция:

1. Читает лог (undo log) изменений
2. Отменяет операции в обратном порядке
3. Освобождает блокировки на строках
4. Обновляет версию транзакции

Важные правила

✅ ПРАВИЛЬНО:

try:
    with transaction.atomic():
        operation1()
        operation2()
        operation3()
except:
    # ROLLBACK автоматический
    pass

❌ НЕПРАВИЛЬНО:

# Забыл откатить при ошибке
try:
    cursor.execute('BEGIN')
    operation1()
    operation2()  # Ошибка
    conn.commit()  # Это не выполнится
except:
    pass  # ROLLBACK забыли!

Мониторинг ROLLBACK'ов

from prometheus_client import Counter

rollback_counter = Counter(
    'database_rollbacks_total',
    'Total number of rollbacks',
    ['reason']
)

try:
    with transaction.atomic():
        risky_operation()
except Exception as e:
    rollback_counter.labels(reason=type(e).__name__).inc()
    raise

ROLLBACK — это гарантия целостности данных. Правильное использование транзакций с ROLLBACK при ошибках — основа надёжного приложения.