Зачем СУБД, если есть файлы: потерянная запись, обрезанный файл и четыре буквы ACID

Два скрипта дописывают заказы в один CSV, и из 400 строк в файле остаётся 198. Третий убит kill -9 посреди строки и оставляет на конце файла обрубок. Файл ничего не знает о других писателях и о том, что запись не дошла до конца. СУБД добавляет ровно эти знания: блокировки, журнал и проверки. Четыре буквы ACID — названия ответов на конкретные поломки, ниже они воспроизводятся.

DBA / 01koddo

Убитый процесс: файл остаётся обрубком, SQLite откатывает транзакцию

Что не ломается: дописывание в конец

Начнём с того, что ломаться должно, но не ломается. Два процесса по 20 000 раз дописывают строку в один файл, открытый режимом a:

mkdir -p /tmp/dba-database-vs-files && cd /tmp/dba-database-vs-files
cat > append.py <<'EOF'
import sys
name, n = sys.argv[1], int(sys.argv[2])
with open('orders.csv', 'a') as f:
    for i in range(n):
        f.write(f'{name},{i},100\n')
EOF
python3 append.py A 20000 & python3 append.py B 20000 & wait
wc -l orders.csv
cut -d, -f1 orders.csv | sort | uniq -c

40000 orders.csv
  20000 A
  20000 B

Все 40 000 строк на месте. Режим a открывает файл с флагом O_APPEND: ядро само ставит позицию записи в конец при каждом write, и две записи не затирают друг друга. Опыт повторён с теми же строками, только длиннее: по 5 КБ (4 000 строк, 0 битых) и по 2 МБ (100 строк, 0 битых). Перемешивания не было и там. Это результат одной файловой системы (tmpfs): на сетевом диске или другой файловой системе дописывание длинных строк может вести себя иначе, и в коде это не видно. Поломка, которая воспроизводится всегда, начинается там, где файл надо не дополнить, а изменить.

Опыт 1: два процесса меняют один CSV

Типичная работа с файлом вместо базы: прочитать всё, добавить строку, записать обратно. Идентификатор новой строки — число строк плюс один. Два процесса делают это по 200 раз:

cat > rmw_csv.py <<'EOF'
import os, sys
name = sys.argv[1]
for i in range(200):
    rows = open('orders.csv').read().splitlines() if os.path.exists('orders.csv') else []
    rows.append(f'{len(rows) + 1},{name},100')
    with open('orders.csv', 'w') as f:
        f.write('\n'.join(rows) + '\n')
EOF
rm -f orders.csv
python3 rmw_csv.py A & python3 rmw_csv.py B & wait
wc -l orders.csv
cut -d, -f2 orders.csv | sort | uniq -c

198 orders.csv
    145 A
     53 B

Ожидалось 400 строк, осталось 198: 202 заказа исчезли без единой ошибки. Режим w обнуляет файл при открытии, и процесс, который в этот момент читает, видит пустой или недописанный файл. Число каждый раз получается своё: ещё три прогона дали 162, 15 и 124 строки из 400.

Эту поломку лечит изоляция (I в ACID): пока один процесс читает и пишет, второй не видит и не портит промежуточное состояние.

Опыт 2: kill -9 посреди записи длинной строки

Теперь один писатель. Он записывает три нормальные строки и начинает четвёртую: поле note пишется 100 кусками по 100 КБ с flush после каждого. Родительская оболочка убивает его по PID, пока он в середине:

cat > crash_csv.py <<'EOF'
import time
with open('orders.csv', 'w') as f:
    for i in range(1, 4):
        f.write(f'{i},order-{i},100\n')
    f.flush()
    f.write('4,')
    for k in range(100):
        f.write(f'[{k:02d}]' * 25000)
        f.flush()
        time.sleep(0.02)
    f.write(',100\n')
EOF
python3 crash_csv.py & pid=$!; sleep 0.6; kill -9 $pid; wait $pid 2>/dev/null
wc -l orders.csv
stat -c '%s байт' orders.csv
tail -c 40 orders.csv; echo
awk -F, '{print NR": "NF" полей, "length($0)" байт"}' orders.csv

3 orders.csv
3000044 байт
[29][29][29][29][29][29][29][29][29][29]
1: 3 полей, 13 байт
2: 3 полей, 13 байт
3: 3 полей, 13 байт
4: 2 полей, 3000002 байт

Полный файл занял бы 10 000 046 байт, записано 3 000 044. wc -l считает только перевод строки и насчитал три строки, хотя «четвёртая» в файле есть: 3 МБ без конца и без третьего поля. Парсер на ней споткнётся или прочитает запись без суммы. Откатить её некому: файл не помнит, какое состояние было до начала записи.

Эту поломку лечит атомарность (A в ACID): запись либо видна целиком, либо не видна вообще.

Опыт 3: то же самое в SQLite

SQLite хранит базу в одном файле, как и CSV, но пишет иначе. Сначала два процесса из опыта 1 с той же логикой «число строк плюс один», только внутри транзакции с BEGIN IMMEDIATE:

cat > rmw_sqlite.py <<'EOF'
import sqlite3, sys
name = sys.argv[1]
db = sqlite3.connect('orders.db', timeout=30, isolation_level=None)
for i in range(200):
    db.execute('BEGIN IMMEDIATE')
    n = db.execute('SELECT count(*) FROM orders').fetchone()[0]
    db.execute('INSERT INTO orders VALUES (?, ?, 100)', (n + 1, name))
    db.execute('COMMIT')
EOF
python3 -c "
import sqlite3; db = sqlite3.connect('orders.db')
db.execute('CREATE TABLE orders(id INTEGER PRIMARY KEY, note TEXT, amount INT)'); db.commit()"
python3 rmw_sqlite.py A & python3 rmw_sqlite.py B & wait
python3 -c "
import sqlite3; db = sqlite3.connect('orders.db')
print(db.execute('SELECT count(*), count(DISTINCT id) FROM orders').fetchone())
print(db.execute('SELECT note, count(*) FROM orders GROUP BY note').fetchall())"

(400, 400)
[('A', 200), ('B', 200)]

Все 400 строк с разными id. BEGIN IMMEDIATE берёт блокировку записи в начале транзакции (в терминах SQLite — RESERVED), и второй процесс ждёт её до тридцати секунд, заданных в timeout. Прочитать число строк и вставить следующую можно, только когда никто другой не успеет вставить между этими двумя шагами.

Теперь убийство посреди транзакции. Таблица уже содержит три строки. Скрипт начинает транзакцию, вставляет строку 4 с полем note в 10 МБ (оно не помещается в кэш страниц SQLite, и часть страниц попадает в файл базы до COMMIT), печатает written и засыпает. Оболочка ждёт этого слова и убивает процесс по PID:

cat > crash_sqlite.py <<'EOF'
import sqlite3, sys, time
mode = sys.argv[1]
db = sqlite3.connect('orders.db', isolation_level=None)
if mode == 'init':
    db.execute('CREATE TABLE orders(id INTEGER PRIMARY KEY, note TEXT, amount INT)')
    db.executemany('INSERT INTO orders VALUES (?, ?, 100)', [(i, f'order-{i}') for i in (1, 2, 3)])
else:
    db.execute('BEGIN')
    db.execute('INSERT INTO orders VALUES (4, ?, 100)', ('x' * 10_000_000,))
    if mode == 'commit':
        db.execute('COMMIT')
    print('written', flush=True)
    time.sleep(60)
EOF
cat > show.py <<'EOF'
import sqlite3
db = sqlite3.connect('orders.db')
print(db.execute('SELECT count(*), max(id) FROM orders').fetchone(), db.execute('PRAGMA integrity_check').fetchone())
EOF
rm -f orders.db orders.db-journal out.txt
python3 crash_sqlite.py init; stat -c '%n %s' orders.db*
python3 crash_sqlite.py open > out.txt & pid=$!
until grep -q written out.txt; do sleep 0.1; done
stat -c '%n %s' orders.db*
kill -9 $pid; wait $pid 2>/dev/null
python3 show.py
stat -c '%n %s' orders.db*

orders.db 8192
orders.db 8245248
orders.db-journal 9736
(3, 3) ('ok',)
orders.db 8192

В момент убийства файл базы вырос с 8 192 до 8 245 248 байт, то есть данные строки 4 уже лежат в основном файле. Рядом лежит orders.db-journal на 9 736 байт: копии страниц, какими они были до транзакции, и исходный размер файла. Первое же открытие базы нашло журнал, вернуло старые страницы, обрезало файл до 8 192 байт и удалило журнал. Остались три строки и integrity_check вернул ok. Файл CSV в такой же ситуации остался с обрубком.

Это и есть атомарность. Строка 4 либо в базе целиком, либо ничего не изменилось.

А если убить процесс после COMMIT? Тот же скрипт в режиме commit:

rm -f orders.db orders.db-journal out.txt; python3 crash_sqlite.py init
python3 crash_sqlite.py commit > out.txt & pid=$!
until grep -q written out.txt; do sleep 0.1; done
kill -9 $pid; wait $pid 2>/dev/null
stat -c '%n %s' orders.db*
python3 show.py

orders.db 10014720
(4, 4) ('ok',)

Журнала нет, четыре строки целы. Но этот опыт не доказывает долговечность. kill -9 убивает процесс, а не машину: данные из страничного кэша ядра переживают процесс и без fsync. К тому же каталог на tmpfs, где fsync ничего не делает. Настоящая долговечность проверяется отключением питания, и здесь она не воспроизведена. Что гарантирует D, видно в источниках: документ SQLite «Atomic Commit In SQLite» разбирает порядок шагов, а в настройках по умолчанию PRAGMA journal_mode возвращает delete, а PRAGMA synchronous — 2 (FULL), то есть журнал и файл базы синхронизируются на диск. Поэтому долговечность ниже выводится из документа и кода, а не из прогона.

Откуда четыре буквы

Из трёх поломок получаются три буквы. Четвёртая отвечает на беду, которой в файле нечем помешать.

Атомарность отвечает на обрубок. Транзакция применяется целиком или не применяется. SQLite добивается этого порядком записи: сначала в журнал копируются старые страницы, журнал синхронизируется с диском, и только потом меняется файл базы. Если процесс умер раньше удаления журнала, при следующем открытии журнал проигрывается назад. Момент коммита, по документу, — удаление журнала (раздел 3.11).

Изоляция отвечает на потерянную запись. Параллельные транзакции не видят и не портят промежуточное состояние друг друга. В опыте 3 это блокировка записи на всю базу; PostgreSQL блокирует строки, а не базу целиком. Про уровни изоляции и их наблюдаемое поведение в PostgreSQL написано в статье про транзакции и уровни изоляции.

Долговечность отвечает на «COMMIT вернулся, а данных нет». После успешного COMMIT результат переживает аварию, включая отказ питания. Цена — ожидание fsync на каждом коммите. Это настройка: PRAGMA synchronous = OFF убирает ожидание и вместе с ним обещание.

Согласованность отвечает на бессмысленную строку. В файле заказ на несуществующего клиента или с суммой минус пять допишется без возражений. База проверяет ограничения, которые ты описал в схеме:

python3 - <<'EOF'
import sqlite3
db = sqlite3.connect(':memory:')
db.execute('PRAGMA foreign_keys = ON')
db.execute('CREATE TABLE customers(id INTEGER PRIMARY KEY)')
db.execute('CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id), amount INT CHECK (amount > 0))')
db.execute('INSERT INTO customers VALUES (1)')
for row in [(1, 1, 100), (2, 99, 100), (3, 1, -5)]:
    try:
        db.execute('INSERT INTO orders VALUES (?, ?, ?)', row); print(row, 'принята')
    except sqlite3.Error as e:
        print(row, type(e).__name__ + ':', e)
EOF

(1, 1, 100) принята
(2, 99, 100) IntegrityError: FOREIGN KEY constraint failed
(3, 1, -5) IntegrityError: CHECK constraint failed: amount > 0

Здесь согласованность — не свойство движка, а то, что ты описал в схеме, плюс гарантия, что транзакция не оставит базу между состояниями. В SQLite внешние ключи по умолчанию выключены: PRAGMA foreign_keys = ON нужно выполнять на каждом соединении (документ «Foreign Key Support»). Проверки есть, но включить их должен ты.

Что показал код SQLite

Горячий журнал — не метафора, а условие в коде. Версия 3.50.2, файл src/pager.c, функция hasHotJournal, комментарий к ней:

** the file-system for the given pager. A hot journal is one that
** needs to be played back. According to this function, a hot-journal
** file exists if the following criteria are met:
**
**   * The journal file exists in the file system, and
**   * No process holds a RESERVED or greater lock on the database file, and
**   * The database file itself is greater than 0 bytes in size, and
**   * The first byte of the journal file exists and is not 0x00.

Ссылка на строки 5089-5096 тега version-3.50.2. Журнал горячий, если файл есть, а блокировки записи на базе никто не держит: писатель ушёл, не завершив транзакцию. В опыте это ровно наш kill -9.

Что происходит дальше, видно в sqlite3PagerSharedLock, того же файла:

      /* Playback and delete the journal.  Drop the database write
      ** lock and reacquire the read lock. Purge the cache before
      ** playing back the hot-journal so that we don't end up with
      ** an inconsistent cache.  Sync the hot journal before playing
      ** it back since the process that crashed and left the hot journal
      ** probably did not sync it and we are required to always sync
      ** the journal before playing it back.
      */
      if( isOpen(pPager->jfd) ){
        assert( rc==SQLITE_OK );
        rc = pagerSyncHotJournal(pPager);
        if( rc==SQLITE_OK ){
          rc = pager_playback(pPager, !pPager->tempFile);
          pPager->eState = PAGER_OPEN;
        }

Ссылка на строки 5327-5340 тега version-3.50.2. Откат выполняет тот, кто первым открыл базу после аварии, и перед проигрыванием синхронизирует журнал: он не знает, успел ли это сделать умерший писатель. Поэтому в опыте 3 откат случился в show.py, а не в момент убийства.

Что из этого следует

Файл годится там, где писатель один, запись добавляет строки в конец и обрубок в конце допустим: логи, выгрузки. Он не годится, как только появляется «изменить строку» или второй писатель. Всё, что в опытах 1–3 делали блокировки, журнал и ограничения, придётся написать самому, и разработчик, написавший их заново, вернулся к СУБД с худшей реализацией.

Для DBA отсюда два вывода. Журнал и порядок записи — то место, где живёт сохранность данных; у PostgreSQL его роль играет WAL. И настройки вроде synchronous — это сделка скорости с обещанием D: менять их без понимания, кто за это заплатит при аварии, нельзя.

Чек-лист

1. Если несколько процессов меняют общий файл, найди в коде шаг «прочитать — изменить — записать» и спроси, что случится, если он выполнится дважды одновременно.
2. Убей процесс по PID посреди записи на тестовых данных и посмотри на хвост файла: tail -c и wc -l показывают обрубок.
3. В SQLite включай PRAGMA foreign_keys = ON на каждом соединении и описывай CHECK в схеме, а не в коде приложения.
4. Не принимай «данные пережили kill -9» за доказательство долговечности: настоящую проверку делает отключение питания и fsync.
5. Не отключай синхронизацию (synchronous = OFF или аналоги) ради скорости без письменного решения, кто принимает риск потери подтверждённых транзакций.

Один и тот же kill -9 оставил CSV с обрубком, а SQLite вернул базу в прежнее состояние. Разница не в формате файла, а в том, что СУБД пишет сначала журнал, а потом данные.