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

← назад к разделу

Уровень изоляции пишут в коде одной строкой, а расплачиваются за него ошибками в проде. Механика, которая за ним стоит, — версии строк, снимки, номера транзакций — разобрана в соседней статье про ACID и изоляцию. Здесь прикладная сторона: какой уровень выбрать под задачу, как задать его в коде на четырёх языках, что делать с ошибкой 40001 и почему забытая открытая транзакция опаснее, чем кажется.

Что может пойти не так при параллельных транзакциях

Короткая памятка, чтобы дальше говорить об одном и том же. Механика каждой аномалии — в соседней статье, здесь только названия и суть.

• Грязное чтение — видны чужие незафиксированные данные. В PostgreSQL невозможно ни на одном уровне.
• Неповторяемое чтение — одна и та же строка, прочитанная дважды, даёт разные значения.
• Фантомное чтение — один и тот же запрос возвращает разное число строк.
• Write skew — каждая транзакция по отдельности правило соблюла, а вместе они его нарушили: пишут в разные строки, а проверяют общее условие. Разберём ниже на дежурных врачах.

А вот аномалия, которой нет в стандартной таблице и которая встречается чаще всех остальных вместе, — потерянное обновление. С неё и начнём, потому что лечится она не уровнем изоляции.

Потерянное обновление

Самая частая аномалия в прикладном коде — потерянное обновление (lost update): две транзакции читают одно значение, каждая считает новое в приложении и записывает результат; вторая запись затирает первую.

-- обе транзакции одновременно, READ COMMITTED
SELECT balance FROM account WHERE id = 1;      -- обе видят 100
UPDATE account SET balance = 70 WHERE id = 1;  -- обе пишут 100 - 30

Итог — 70 вместо 40, и база не сообщит об ошибке: с её точки зрения обе транзакции корректны. Ту же механику видно без базы: два потока читают одно значение и записывают посчитанное, а рядом тот же код под блокировкой строки.

import java.util.concurrent.CyclicBarrier;

public class LostUpdate {
    static int plain = 100;
    static int locked = 100;
    static final CyclicBarrier bothRead = new CyclicBarrier(2);
    static final Object row = new Object();

    public static void main(String[] args) throws InterruptedException {
        twice(() -> {
            int seen = plain;
            waitForBoth();
            plain = seen - 30;
        });
        twice(() -> {
            synchronized (row) {
                int seen = locked;
                locked = seen - 30;
            }
        });
        System.out.println("прочитали в приложении и записали: " + plain);
        System.out.println("прочитали с блокировкой строки:    " + locked);
    }

    static void twice(Runnable body) throws InterruptedException {
        Thread first = new Thread(body);
        Thread second = new Thread(body);
        first.start();
        second.start();
        first.join();
        second.join();
    }

    static void waitForBoth() {
        try {
            bothRead.await();
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }
}

package main

import (
	"fmt"
	"sync"
)

var (
	plain    = 100
	locked   = 100
	bothRead sync.WaitGroup
	row      sync.Mutex
)

func main() {
	bothRead.Add(2)
	twice(func() {
		seen := plain
		bothRead.Done()
		bothRead.Wait()
		plain = seen - 30
	})
	twice(func() {
		row.Lock()
		defer row.Unlock()
		seen := locked
		locked = seen - 30
	})
	fmt.Println("прочитали в приложении и записали:", plain)
	fmt.Println("прочитали с блокировкой строки:   ", locked)
}

func twice(body func()) {
	var done sync.WaitGroup
	for range 2 {
		done.Go(body)
	}
	done.Wait()
}

let plain = 100;
let locked = 100;
let busy = Promise.resolve();
const lock = (body) => (busy = busy.then(body));
const yieldToOther = () => new Promise((r) => setTimeout(r, 10));

async function twice(body) {
  await Promise.all([body(), body()]);
}

async function main() {
  await twice(async () => {
    const seen = plain;
    await yieldToOther();
    plain = seen - 30;
  });
  await twice(() => lock(async () => {
    const seen = locked;
    await yieldToOther();
    locked = seen - 30;
  }));
  console.log("прочитали в приложении и записали:", plain);
  console.log("прочитали с блокировкой строки:   ", locked);
}
main();

import threading

plain = 100
locked = 100
both_read = threading.Barrier(2)
row = threading.Lock()


def twice(body):
    threads = [threading.Thread(target=body) for _ in range(2)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()


def read_then_write():
    global plain
    seen = plain
    both_read.wait()
    plain = seen - 30


def under_lock():
    global locked
    with row:
        seen = locked
        locked = seen - 30


twice(read_then_write)
twice(under_lock)
print("прочитали в приложении и записали:", plain)
print("прочитали с блокировкой строки:   ", locked)

Первый прогон печатает 70: оба потока успели прочитать 100 до того, как кто-то записал. Второй печатает 40 — под synchronized второй поток ждёт первого и вычитает уже из 70, ровно как под SELECT … FOR UPDATE. Лечат потерянное обновление тремя способами.

Первый — считать в базе, а не в приложении: UPDATE account SET balance = balance - 30 WHERE id = 1 AND balance >= 30; второй UPDATE дождётся первого и увидит уже 70.

Второй — заблокировать строку заранее: SELECT … FOR UPDATE, и вторая транзакция подождёт.

Третий — поднять уровень до REPEATABLE READ: вторая транзакция получит ошибку 40001 и повторит работу с начала.

Три уровня в PostgreSQL

PostgreSQL поддерживает три реальных уровня. Формально в стандарте SQL есть ещё READ UNCOMMITTED, но в PostgreSQL он работает так же, как READ COMMITTED — грязное чтение просто не реализовано.

Важно: PostgreSQL реализует REPEATABLE READ через snapshot isolation, что строже стандарта SQL: фантомные чтения тоже исключены.

READ COMMITTED — уровень по умолчанию

Каждый SELECT в транзакции видит данные, закоммиченные на момент запуска этого конкретного запроса. Не на момент начала транзакции, а именно запроса.

-- TX1 начала транзакцию
BEGIN;
SELECT price FROM product WHERE id = 1;   -- 100

-- в это время TX2 изменила цену и закоммитила
-- UPDATE product SET price = 120 WHERE id = 1; COMMIT;

SELECT price FROM product WHERE id = 1;   -- 120! (non-repeatable read)
COMMIT;

Это звучит страшно, но в большинстве CRUD-операций строку читают один раз — проблемы не возникает.

Когда SELECT FOR UPDATE обязателен при RC: если логика «прочитал → проверил → записал» должна быть атомарной, FOR UPDATE блокирует строку до конца транзакции — это то же лекарство от потерянного обновления, что и выше.

REPEATABLE READ — снимок данных

При REPEATABLE READ PostgreSQL делает снимок (snapshot) данных на момент первого запроса в транзакции. Все последующие чтения в той же транзакции видят этот снимок — как будто данные «заморожены».

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM orders WHERE status = 'NEW';   -- 100

-- другая транзакция вставила 5 новых заказов и закоммитила

SELECT count(*) FROM orders WHERE status = 'NEW';   -- всё ещё 100
COMMIT;

Когда это нужно:

• длинный отчёт или пересборка данных по нескольким таблицам, где важна согласованность среза;
• pg_dump использует именно этот уровень.

Ошибка 40001 при REPEATABLE READ

Если TX1 читает строку, а TX2 успевает её изменить и закоммитить — и потом TX1 пытается изменить ту же строку, PostgreSQL не может «смешать» изменения. Он обрывает запрос TX1 ошибкой:

ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001

Сама база при этом ничего не откатывает — она только помечает транзакцию как испорченную. До ROLLBACK любой следующий запрос в ней ответит «current transaction is aborted, commands ignored until end of transaction block», а COMMIT сработает как откат. Закрыть транзакцию и начать её заново — работа приложения.

Без логики повтора (retry) REPEATABLE READ нельзя использовать в продакшене.

SERIALIZABLE — полная изоляция

SERIALIZABLE гарантирует, что результат параллельных транзакций будет таким же, как если бы они выполнялись строго по одной. PostgreSQL использует алгоритм SSI (Serializable Snapshot Isolation) — он отслеживает зависимости между транзакциями через предикатные локи.

Пример write skew

Инвариант: на смене всегда должен быть хотя бы один врач. На дежурстве двое.

-- TX1 (REPEATABLE READ): смотрит количество дежурных
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2
-- «ок, можно уйти, останется 1»
UPDATE doctors SET on_call = false WHERE id = 1;

-- TX2 (REPEATABLE READ) делает то же самое параллельно:
SELECT count(*) FROM doctors WHERE on_call = true;   -- тоже 2
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

-- TX1 коммитит — инвариант нарушен: 0 врачей на смене

REPEATABLE READ не помогает: каждая транзакция видела корректный снимок и писала в разные строки. Только SERIALIZABLE поймает такой конфликт — одна из транзакций получит 40001 и откатится.

Цена вопроса: предикатные локи создают нагрузку, процент откатов растёт под нагрузкой. На большинстве OLTP-приложений SERIALIZABLE избыточен. Зачастую дешевле оставить RC и заменить сложный инвариант на SELECT FOR UPDATE с явной проверкой в коде: например, завести строку смены со счётчиком дежурных и блокировать именно её — тогда обе транзакции выстроятся в очередь за одной строкой и посчитают правильно. А вот CHECK здесь не поможет: он проверяет одну строку и соседних не видит. Для правил «между строками» у PostgreSQL есть EXCLUDE-ограничения и триггеры.

Два условия, без которых SERIALIZABLE не даёт того, за что вы платите.

Первое: на этом уровне должны идти все конфликтующие транзакции. Уровень — свойство транзакции, а не таблицы, и одна параллельная транзакция на READ COMMITTED, пишущая в те же строки, возвращает write skew целиком. Поднимать уровень надо всем сценариям, которые трогают инвариант: сервису, отчёту, ночному заданию, ручному скрипту.

Второе: повтор обязателен, о нём ниже. Транзакция на SERIALIZABLE без обработки 40001 — это просто транзакция, которая иногда падает в лицо пользователю.

Отдельная форма для длинных отчётов — BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. Такая транзакция ничего не пишет, в начале ждёт заведомо безопасного снимка и дальше уже не откатывается никогда: повтор ей не нужен. Платой становится ожидание на старте — иногда секунды, пока не закончатся мешающие пишущие транзакции.

Как выбрать уровень

Практический алгоритм:

1. Простой CRUD или read-modify-write одной строки → READ COMMITTED + SELECT FOR UPDATE там, где нужна атомарность.
2. Длинный отчёт по нескольким таблицам с согласованным срезом → REPEATABLE READ.
3. Сложный инвариант на нескольких строках, нельзя выразить через FOR UPDATE → SERIALIZABLE + retry.
4. Финансовые операции → READ COMMITTED + SELECT FOR UPDATE с упорядоченными локами, когда строки известны заранее (счёт списания, счёт зачисления). Если же правило проверяется по выборке и опасна строка, которой ещё нет («не больше пяти активных заказов»), заблокировать нечего — тогда SERIALIZABLE.

При сомнениях — оставайтесь на READ COMMITTED. Поднимать уровень изоляции без понимания конкретной аномалии — лишняя нагрузка без гарантий безопасности.

Глубже: как задать уровень в кодерасширенное

Уровень изоляции чаще всего задают на отдельную транзакцию — так он стоит рядом с кодом, которому нужен, и не растекается на соседей. Но задать его можно и шире: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL … меняет умолчание для всей сессии, а параметр default_transaction_isolation — для всего сервера. READ COMMITTED — дефолт PostgreSQL, его явно не указывают.

@Transactional(isolation = Isolation.SERIALIZABLE)
public void releaseDoctorFromShift(long doctorId) {
    int onCallCount = doctorRepository.countByOnCallTrue();
    if (onCallCount <= 1) {
        throw new LastDoctorOnShiftException();
    }
    doctorRepository.setOnCallFalse(doctorId);
}

tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
if err != nil {
	return err
}
defer tx.Rollback(ctx)

var onCall int
if err := tx.QueryRow(ctx, "SELECT count(*) FROM doctors WHERE on_call").Scan(&onCall); err != nil {
	return err
}
if onCall <= 1 {
	return ErrLastDoctorOnShift
}
if _, err := tx.Exec(ctx, "UPDATE doctors SET on_call = false WHERE id = $1", doctorID); err != nil {
	return err
}
return tx.Commit(ctx)

const client = await pool.connect();
try {
  await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
  const { rows } = await client.query('SELECT count(*)::int AS on_call FROM doctors WHERE on_call');
  if (rows[0].on_call <= 1) throw new LastDoctorOnShiftError();
  await client.query('UPDATE doctors SET on_call = false WHERE id = $1', [doctorId]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

from psycopg import IsolationLevel

conn.isolation_level = IsolationLevel.SERIALIZABLE
with conn.transaction():
    on_call = conn.execute("SELECT count(*) FROM doctors WHERE on_call").fetchone()[0]
    if on_call <= 1:
        raise LastDoctorOnShiftError()
    conn.execute("UPDATE doctors SET on_call = false WHERE id = %s", (doctor_id,))

Go (pgx):

opts := pgx.TxOptions{IsoLevel: pgx.Serializable}
err := pgx.BeginTxFunc(ctx, pool, opts, func(tx pgx.Tx) error {
    // логика транзакции
    return nil
})

Node.js (pg):

await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
// запросы
await client.query('COMMIT');

Python (psycopg3):

async with pool.connection() as conn:
    await conn.set_isolation_level(psycopg.IsolationLevel.SERIALIZABLE)
    async with conn.transaction():
        # логика транзакции
        pass

И одна ошибка, которую делают, когда «нужно поднять уровень на один запрос»: внутри уже открытой транзакции уровень не меняется. SET TRANSACTION ISOLATION LEVEL работает только до первого запроса — после него PostgreSQL ответит SET TRANSACTION ISOLATION LEVEL must be called before any query. Из кода это выглядит так же: вложенный вызов, который просит SERIALIZABLE внутри уже открытой транзакции — аннотация @Transactional(isolation = …) на методе, вызванном из другой транзакции, или BeginTx с уровнем на соединении, где транзакция уже идёт, — молча не поднимет уровень: он присоединится к существующей и будет работать на её уровне. Нужен другой уровень — нужна другая транзакция: отдельная точка входа или явно новая транзакция вместо вложенной.

Глубже: retry на ошибку 40001расширенное

При REPEATABLE READ и SERIALIZABLE приложение должно повторять транзакцию при ошибке 40001.

@Retryable(
    retryFor = ConcurrencyFailureException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void doWork() { ... }

func withSerializableRetry(ctx context.Context, pool *pgxpool.Pool, attempts int, work func(pgx.Tx) error) error {
	delay := 50 * time.Millisecond
	for attempt := 1; ; attempt++ {
		err := pgx.BeginTxFunc(ctx, pool, pgx.TxOptions{IsoLevel: pgx.Serializable}, work)
		var pgErr *pgconn.PgError
		if err == nil || !errors.As(err, &pgErr) || pgErr.Code != "40001" || attempt == attempts {
			return err
		}
		time.Sleep(delay)
		delay *= 2
	}
}

async function withSerializableRetry(pool, work, attempts = 3) {
  let delay = 50;
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);
      await client.query('COMMIT');
      return result;
    } catch (e) {
      await client.query('ROLLBACK');
      if (e.code !== '40001' || attempt === attempts) throw e;
      await new Promise((r) => setTimeout(r, delay));
      delay *= 2;
    } finally {
      client.release();
    }
  }
}

import time
from psycopg import IsolationLevel
from psycopg.errors import SerializationFailure


def with_serializable_retry(conn, work, attempts=3):
    delay = 0.05
    conn.isolation_level = IsolationLevel.SERIALIZABLE
    for attempt in range(1, attempts + 1):
        try:
            with conn.transaction():
                return work(conn)
        except SerializationFailure:
            if attempt == attempts:
                raise
            time.sleep(delay)
            delay *= 2

Обычно хватает 3 попыток с нарастающей паузой. Сравнивают именно код ошибки — pgErr.Code в Go, err.code в Node, класс SerializationFailure в psycopg, — а не текст сообщения: он зависит от локали сервера.

Два условия, без которых повтор не работает. Повторять нужно всю транзакцию целиком, снаружи: после 40001 текущая уже помечена на откат, и новые запросы в ней падают. И ловить нужно только ошибку сериализации, а не любое исключение драйвера: сетевой обрыв или нарушение ограничения повторять бессмысленно. Узнают её по коду SQLSTATE 40001: в Go это *pgconn.PgError с полем Code, в Python — psycopg.errors.SerializationFailure, в Node — поле code у ошибки драйвера pg. Java-стек с транслятором ошибок отдаёт ConcurrencyFailureException — общего предка всех ошибок конкуренции; узкий CannotSerializeTransactionException объявлен устаревшим с версии 6.0.3, и ловить надо предка.

Три вещи, о которых забывают чаще всего.

Разброс паузы. Нарастающая пауза без случайной добавки выстраивает конкурентов в такт: они отступают на одинаковое время и сталкиваются снова. Поэтому к задержке добавляют разброс — в @Backoff это random = true, руками — умножение паузы на случайный множитель от 0,5 до 1,5.

Внешние эффекты. Повторять можно только то, что безопасно выполнить дважды. Если внутри транзакции ушло письмо, списались деньги во внешнем платёжном сервисе или отправилось сообщение в брокер, повтор отправит их второй раз: откат базы их не отменяет. Отсюда правило — внешние вызовы выносят за границу транзакции и делают идемпотентными; как именно — в статье про блокировки и идемпотентность.

Что делать, когда попытки кончились. Ответ зависит от того, кто ждёт. Пользовательский запрос честнее завершить ошибкой «попробуйте ещё раз» и не держать соединение дальше. Фоновому заданию правильнее вернуть задачу в очередь с большей паузой. Чего делать нельзя — глотать ошибку и продолжать так, будто работа выполнена: после 40001 не записано ничего, и это худший вариант молчаливой потери данных.

Глубже: тайм-аут на зависшие транзакциирасширенное

Открытая транзакция удерживает ресурсы и мешает уборке мусора, и тут важна связь с уровнем. На READ COMMITTED снимок живёт от запроса до запроса, поэтому висящая между запросами транзакция держит только блокировки. А на REPEATABLE READ и SERIALIZABLE снимок берётся один на всю транзакцию — значит, её горизонт видимости удерживает старые версии строк по всей базе до самого коммита, и VACUUM не вправе их вычистить. Час висящей транзакции на верхнем уровне — час накопления мусора у всех.

Открытая транзакция удерживает ресурсы и мешает работе autovacuum. По умолчанию idle_in_transaction_session_timeout = 0 — то есть ограничения нет вовсе, забытая транзакция может висеть сутками. Поэтому в продакшене его выставляют руками, в postgresql.conf:

idle_in_transaction_session_timeout = 30000   # значение в миллисекундах — 30 секунд

Тот же параметр можно поставить на одну сессию прямо из SQL, там единицу пишут явно:

SET idle_in_transaction_session_timeout = '30s';

Сервер обрывает сессию, чья открытая транзакция простаивает дольше 30 секунд: соединение закрывается, транзакция откатывается. Длинная незакрытая транзакция почти всегда означает ошибку в коде — лучше её прервать, чем ждать.

Коротко

• Рабочих уровней три: READ COMMITTED, REPEATABLE READ, SERIALIZABLE; READ UNCOMMITTED работает как первый. Грязное чтение MVCC исключает на всех.
• READ COMMITTED — дефолт и норма для OLTP: каждый запрос видит свежие закоммиченные данные, и для однократного чтения строки этого хватает.
• REPEATABLE READ фиксирует снимок на первом запросе транзакции, фантомы исключены. Нужен для согласованных отчётов и длинных операций.
• SERIALIZABLE — единственный уровень, защищающий от write skew. Нужен редко, стоит предикатных локов и откатов.
• Ошибка 40001 — штатная ситуация обоих верхних уровней: приложение обязано повторить транзакцию целиком, снаружи.
• Потерянное обновление лечится не уровнем, а расчётом в самом UPDATE или SELECT … FOR UPDATE; idle_in_transaction_session_timeout = 30s в продакшене обязателен.
• Уровень внутри открытой транзакции не меняется: вложенный вызов с другим уровнем (аннотацией или BeginTx на том же соединении) молча не сработает.
• Повтор делают с разбросом паузы и только для операций без внешних эффектов; исчерпали попытки — честная ошибка или возврат задачи в очередь, но не тихое продолжение.

Что почитать дальше

• Блокировки в PostgreSQL — SELECT FOR UPDATE и другие виды локов.
• Транзакции из кода — как уровень изоляции задаётся из приложения, на примере Java-стека.
• Connection pool — маршрутизация read-only транзакций на реплику.
• ACID и уровни изоляции — откуда берётся изоляция: MVCC, WAL и остальные буквы ACID.