Изоляция в SQLite

Свойство «изоляции» базы данных определяет, когда изменения, внесенные в базу данных одной операцией, становятся видимыми для других одновременных операций.

Изоляция между подключениями к базе данных

Если одна и та же база данных читается и записывается с помощью двух разных подключений к базе данных (двух разных объектов sqlite3, возвращаемых отдельными вызовами sqlite3_open()) и у двух подключений к базе данных нет совместного кэша, то читатель может увидеть только полностью подтверждённые транзакции от писателя. Частичные изменения писателя, которые не были подтверждены, невидимы для читателя. Это верно независимо от того, находятся ли два подключения к базе данных в одном потоке, в разных потоках одного процесса или в разных процессах. Это обычное и ожидаемое поведение для систем баз данных SQL.

Предыдущий абзац также верен (разные подключения к базе данных изолированы друг от друга) в режиме совместного кэша, если pragma read_uncommitted остаётся выключенным. pragma read_uncommitted по умолчанию выключен, поэтому, если приложение ничего не делает для его включения, он остаётся выключенным. Следовательно, если не используется pragma read_uncommitted для изменения поведения по умолчанию, изменения, внесённые одним подключением к базе данных, невидимы для читателей с другим подключением, использующим тот же кэш, пока писатель не подтвердит свою транзакцию.

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

За исключением случаев совместного кэша подключений к базе данных с включенным PRAGMA read_uncommitted, все транзакции в SQLite показывают изоляцию «serializable». SQLite реализует транзакции serializable, фактически сериализуя записи. В базе данных SQLite может быть только один писатель за раз. Можно открыть несколько подключений к базе данных одновременно, и все эти подключения могут записывать в файл базы данных, но они должны делать это по очереди. SQLite использует блокировки для автоматической сериализации записей; приложения, использующие SQLite, не должны об этом беспокоиться.

Изоляция и конкурентность

SQLite реализует изоляцию и управление конкурентностью (и атомарность) с помощью временных файлов журнала, которые появляются в той же директории, что и файл базы данных. Существует два основных «режима журнала». Более старый «режим отката» соответствует использованию опций «DELETE», «PERSIST» или «TRUNCATE» для pragma journal_mode. В режиме отката изменения записываются непосредственно в файл базы данных, а одновременно создаётся отдельный файл журнала отката, который способен восстановить базу данных в исходное состояние, если транзакция отменяется. Режим отката (в частности, режим DELETE, означающий, что журнал отката удаляется с диска по завершении каждой транзакции) — текущее поведение по умолчанию.

С версии 3.7.0 (2010-07-21) SQLite также поддерживает «режим WAL». В режиме WAL изменения не записываются в исходный файл базы данных. Вместо этого изменения попадают в отдельный файл «write-ahead log» или «WAL». Позже, после подтверждения транзакции, эти изменения будут перенесены из файла WAL обратно в исходную базу данных в операции, называемой «чекпоинт». Режим WAL включается с помощью выполнения "PRAGMA journal_mode=WAL".

В режиме отката SQLite реализует изоляцию, блокируя файл базы данных и предотвращая любые чтения другими подключениями к базе данных во время каждой транзакции записи. Читатели могут быть активными в начале записи, до того, как любое содержимое будет выгружено на диск и пока все изменения всё ещё хранятся в личном пространстве памяти писателя. Но перед внесением каких-либо изменений в файл базы данных на диске все читатели должны быть (временно) исключены, чтобы предоставить писателю эксклюзивный доступ к файлу базы данных. Поэтому читатели не могут увидеть неполные транзакции в силу того, что они заблокированы из базы данных во время записи транзакции на диск. Только после того, как транзакция полностью записана и синхронизирована с диском и подтверждена, читатели допускаются обратно в базу данных. Следовательно, читатели никогда не получают возможности увидеть частично записанные изменения.

Режим WAL позволяет одновременным читателям и писателям. Он может это делать, потому что изменения не перезаписывают исходный файл базы данных, а записываются в отдельный файл журнала предзаписи. Это означает, что читатели могут продолжать читать старое, исходное, неизменённое содержимое из исходного файла базы данных одновременно с тем, как писатель добавляет в журнал предзаписи. В режиме WAL SQLite демонстрирует «изоляцию снимка». Когда начинается транзакция чтения, этот читатель продолжает видеть неизменный «снимок» файла базы данных, как он существовал в момент начала транзакции чтения. Любые транзакции записи, которые подтверждаются, пока активна транзакция чтения, всё ещё невидимы для транзакции чтения, потому что читатель видит снимок файла базы данных из предыдущего момента времени.

Пример: предположим, есть два подключения к базе данных X и Y. X начинает транзакцию чтения с помощью BEGIN, за которым следуют один или несколько операторов SELECT. Затем Y приходит и выполняет оператор UPDATE для изменения базы данных. X может впоследствии выполнить SELECT для записей, изменённых Y, но X увидит более старые неизменённые записи, потому что изменения Y невидимы для X, пока X держит транзакцию чтения. Если X хочет увидеть изменения, сделанные Y, то X должен завершить свою транзакцию чтения и начать новую (выполнив COMMIT, за которым следует ещё один BEGIN.)

Ещё один пример: X начинает транзакцию чтения с помощью BEGIN и SELECT, затем Y вносит изменения в базу данных с помощью UPDATE. Затем X пытается внести изменения в базу данных с помощью UPDATE. Попытка X повысить свою транзакцию с транзакции чтения до транзакции записи терпит неудачу с ошибкой SQLITE_BUSY_SNAPSHOT, потому что снимок базы данных, просматриваемый X, больше не является последней версией базы данных. Если X разрешено записывать, он разделит историю файла базы данных, чего SQLite не поддерживает. Для записи в базу данных X должен сначала освободить свой снимок (например, с помощью ROLLBACK), а затем начать новую транзакцию с последующим BEGIN.

Если X начинает транзакцию, которая изначально будет только читать, но X знает, что в конечном итоге захочет записывать и не хочет беспокоиться о возможных ошибках SQLITE_BUSY_SNAPSHOT, возникающих потому, что другое соединение опередило его в очереди, то X может выполнить BEGIN IMMEDIATE, чтобы начать свою транзакцию вместо обычного BEGIN. Команда BEGIN IMMEDIATE сразу же запускает транзакцию записи и, таким образом, блокирует всех других писателей. Если операция BEGIN IMMEDIATE выполняется успешно, то никакие последующие операции в этой транзакции никогда не завершатся с ошибкой SQLITE_BUSY.

Отсутствие изоляции между операциями в одном подключении к базе данных

SQLite обеспечивает изоляцию между операциями в разных подключениях к базе данных. Однако нет изоляции между операциями, которые происходят в одном подключении к базе данных.

Другими словами, если X начинает транзакцию записи с помощью BEGIN IMMEDIATE, затем выполняет один или несколько операторов UPDATE, DELETE и/или INSERT, то эти изменения видны последующим операторам SELECT, которые оцениваются в подключении к базе данных X. Операторы SELECT в другом подключении к базе данных Y не покажут изменений, пока транзакция X не подтвердится. Но операторы SELECT в X покажут изменения до подтверждения.

В рамках одного подключения к базе данных X оператор SELECT всегда видит все изменения в базе данных, завершённые до начала оператора SELECT, вне зависимости от того, были ли они подтверждены или нет. И оператор SELECT, очевидно, не видит никаких изменений, которые происходят после завершения оператора SELECT. Но что насчёт изменений, которые происходят во время выполнения оператора SELECT? Что, если оператор SELECT запущен, и интерфейс sqlite3_step() обрабатывает примерно половину его вывода, затем приложение выполняет операторы UPDATE, изменяющие таблицу, которую читает оператор SELECT, затем выполняются ещё вызовы sqlite3_step() для завершения оператора SELECT? Увидят ли последующие шаги оператора SELECT изменения, внесённые операторами UPDATE, или нет? Ответ заключается в том, что это поведение не определено. В частности, увидит ли оператор SELECT одновременные изменения зависит от версии SQLite, схемы файла базы данных, от того, был ли запущен ANALYZE, и от деталей запроса. В некоторых случаях это может зависеть и от содержимого файла базы данных. Нет хорошего способа узнать, увидит ли оператор SELECT изменения, внесённые в базу данных тем же подключением к базе данных после запуска оператора SELECT. Поэтому разработчики должны избегать написания приложений, предполагающих то, что произойдёт в этом случае.

Если приложение выполняет оператор SELECT для одной таблицы, например, "SELECT rowid, * FROM table WHERE ...", и начинает просматривать результат этого оператора с помощью sqlite3_step(), проверяя каждую строку, то для приложения безопасно удалить текущую строку или любую предыдущую строку с помощью "DELETE FROM table WHERE rowid=?". Также безопасно (в том смысле, что это не навредит базе данных) для приложения удалить строку, которая должна появиться позже в запросе, но еще не появилась. Однако, если строка в будущем удаляется, может случиться так, что она появится после последующего вызова sqlite3_step(), даже после того, как она, предположительно, была удалена. А может и нет. Это поведение не определено. Приложение также может вставлять новые строки в таблицу во время выполнения оператора SELECT, но то, появятся ли новые строки в последующих вызовах sqlite3_step() запроса, не определено. И приложение может обновить текущую строку или любую предыдущую строку, хотя это может привести к повторному появлению этой строки в последующем вызове sqlite3_step(). При условии, что приложение готово к работе с этими неоднозначностями, сами операции безопасны и не нанесут вреда файлу базы данных.

Для целей двух предыдущих абзацев две базы данных с одинаковым общим кэшем и включенным PRAGMA read_uncommitted считаются одним и тем же соединением с базой данных.

Краткое описание

1. Транзакции в SQLite являются SERIALIZABLE.

2. Изменения, внесенные в одном соединении с базой данных, не видны другим подключениям до момента фиксации.

3. Запрос видит все изменения, завершенные в том же соединении с базой данных до начала запроса, независимо от того, были ли эти изменения закреплены.

4. Если изменения происходят в том же соединении с базой данных после запуска запроса, но до его завершения, то не определено, увидит ли запрос эти изменения.

5. Если изменения происходят в том же соединении с базой данных после запуска запроса, но до его завершения, то запрос может вернуть измененную строку более одного раза или может вернуть строку, которая была ранее удалена.

6. Для целей предыдущих четырех пунктов два соединения с базой данных, использующие один и тот же общий кэш и включающие PRAGMA read_uncommitted, считаются одним и тем же соединением с базой данных, а не отдельными соединениями.