Как выглядит транзакция записи в бд?

Как выглядит транзакция записи в БД

Транзакция — это последовательность SQL операций, которые либо выполняются полностью, либо откатываются полностью. Основана на принципе ACID.

Принцип ACID

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

• Транзакция либо полностью выполняется, либо полностью откатывается
• Нет состояния «частично выполнено»

C — Consistency (Согласованность)

• После транзакции БД находится в согласованном состоянии
• Ограничения и правила БД соблюдаются

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

• Одновременные транзакции не мешают друг другу
• Уровни изоляции: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

D — Durability (Долговечность)

• После коммита данные сохраняются, даже при сбое

Цикл жизни транзакции

import psycopg2

# 1. Подключение
conn = psycopg2.connect("dbname=mydb user=postgres")
cursor = conn.cursor()

# 2. НАЧАЛО ТРАНЗАКЦИИ (неявно начинается с первой команды)
cursor.execute("BEGIN")  # Явное начало

# 3. ОПЕРАЦИИ ЗАПИСИ
cursor.execute("""
    UPDATE accounts 
    SET balance = balance - 100 
    WHERE account_id = 1
""")
print(f"Обновлено строк: {cursor.rowcount}")  # 1

cursor.execute("""
    UPDATE accounts 
    SET balance = balance + 100 
    WHERE account_id = 2
""")
print(f"Обновлено строк: {cursor.rowcount}")  # 1

# 4. КОММИТ (фиксация изменений)
conn.commit()
print("Транзакция успешно выполнена")

# Или откат при ошибке
conn.close()

С обработкой ошибок

def transfer_money(from_id: int, to_id: int, amount: float) -> bool:
    """Переводит деньги между счётами с откатом при ошибке"""
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    try:
        # НАЧАЛО ТРАНЗАКЦИИ
        cursor.execute("BEGIN")
        
        # Проверяем баланс
        cursor.execute(
            "SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE",
            (from_id,)
        )
        balance = cursor.fetchone()[0]
        
        if balance < amount:
            raise ValueError("Недостаточно средств")
        
        # Снимаем со счёта 1
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
            (amount, from_id)
        )
        
        # Добавляем на счёт 2
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
            (amount, to_id)
        )
        
        # КОММИТ
        conn.commit()
        print(f"Транзакция успешна: {amount} переведено")
        return True
        
    except Exception as e:
        # ОТКАТ
        conn.rollback()
        print(f"Ошибка! Откат транзакции: {e}")
        return False
        
    finally:
        cursor.close()
        conn.close()

transfer_money(from_id=1, to_id=2, amount=100.0)

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

import psycopg2
from contextlib import contextmanager

@contextmanager
def transaction(conn):
    """Контекстный менеджер для транзакций"""
    try:
        yield conn.cursor()
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e

# Использование
conn = psycopg2.connect("dbname=mydb user=postgres")

try:
    with transaction(conn) as cursor:
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        # Автоматический коммит при выходе
except Exception as e:
    print(f"Транзакция отменена: {e}")
finally:
    conn.close()

SQLAlchemy (ORM)

from sqlalchemy import create_engine, Column, Integer, Float
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Account(Base):
    __tablename__ = 'accounts'
    account_id = Column(Integer, primary_key=True)
    balance = Column(Float)

engine = create_engine("postgresql://user:password@localhost/mydb")
SessionLocal = sessionmaker(bind=engine)

def transfer_sqlalchemy(from_id: int, to_id: int, amount: float) -> bool:
    session = SessionLocal()
    
    try:
        # Получаем счёта с блокировкой
        from_account = session.query(Account).filter(
            Account.account_id == from_id
        ).with_for_update().first()
        
        to_account = session.query(Account).filter(
            Account.account_id == to_id
        ).with_for_update().first()
        
        if not from_account or not to_account:
            raise ValueError("Счёт не найден")
        
        if from_account.balance < amount:
            raise ValueError("Недостаточно средств")
        
        # Изменяем балансы
        from_account.balance -= amount
        to_account.balance += amount
        
        # КОММИТ при выходе из контекста
        session.commit()
        print("Транзакция успешна")
        return True
        
    except Exception as e:
        session.rollback()
        print(f"Откат: {e}")
        return False
        
    finally:
        session.close()

transfer_sqlalchemy(from_id=1, to_id=2, amount=100.0)

Уровни изоляции транзакций

# 1. READ UNCOMMITTED (грязные чтения)
conn.set_isolation_level(0)  # Самый низкий уровень

# 2. READ COMMITTED (по умолчанию в PostgreSQL)
conn.set_isolation_level(1)

# 3. REPEATABLE READ
conn.set_isolation_level(2)

# 4. SERIALIZABLE (самый строгий)
conn.set_isolation_level(3)

Явно в SQL:

cursor.execute("""
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
""")

cursor.execute("BEGIN")
# транзакция с повышенной изоляцией
cursor.execute("SELECT * FROM accounts WHERE id = 1")
conn.commit()

Savepoints (контрольные точки)

def complex_transaction():
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    try:
        cursor.execute("BEGIN")
        
        # Первая часть операции
        cursor.execute("INSERT INTO logs VALUES (%s)", ("Operation 1",))
        
        # Сохранить контрольную точку
        cursor.execute("SAVEPOINT sp1")
        
        # Вторая часть
        cursor.execute("INSERT INTO data VALUES (%s)", ("Data",))
        
        # Откатить только до контрольной точки
        cursor.execute("ROLLBACK TO sp1")
        
        # Коммит только первой операции
        conn.commit()
        
    except Exception as e:
        conn.rollback()
        print(f"Ошибка: {e}")
    finally:
        cursor.close()
        conn.close()

complex_transaction()

Практический чеклист транзакций

✓ Используй ACID для критических операций ✓ Минимизируй время транзакции ✓ Избегай вложенных транзакций (используй SAVEPOINT) ✓ Блокируй ресурсы (FOR UPDATE) перед изменением ✓ Обрабатывай исключения с откатом ✓ Используй контекстные менеджеры для автоматического коммита/отката ✓ Монитори блокировки: SELECT * FROM pg_locks ✓ Для высоконагруженных систем рассмотри более низкий уровень изоляции