Свойство «изоляция» (isolation) базы данных определяет, когда изменения, внесённые одной операцией, становятся видимыми для других параллельных операций.
Как работает изоляция между подключениями SQLite
Если одно и то же соединение к базе данных считывается и записывается через разные объекты sqlite3, открытые с помощью отдельных вызовов sqlite3_open(), и эти соединения не используют общий кэш, то читатель увидит только те транзакции, которые были полностью зафиксированы писателем. Частично выполненные изменения, сделанные писателем, останутся невидимыми для читателя.
Это поведение стандартно для SQL-систем и работает независимо от того, находятся ли соединения в одном потоке, в разных потоках одного процесса или в разных процессах.
Это правило также применимо к ситуации с общим кэшем, при условии, что прагма read_uncommitted отключена. Она по умолчанию неактивна, и если приложение не меняет это значение, оно продолжает оставаться отключённым. Таким образом, если read_uncommitted не активирована, читатели не увидят изменения, сделанные одним соединением на другом соединении, использующем тот же кэш, пока писатель не зафиксирует свою транзакцию.
Когда два соединения используют один и тот же кэш и читатель активирует прагму read_uncommitted, он сможет наблюдать незавершенные изменения писателя сразу, еще до их фиксации. Совместное использование общего кэша с этой прагмой является единственным случаем, когда одно соединение может видеть незавершённые изменения другого. В остальных ситуациях соединения остаются изолированными.
За исключением случаев, когда используется общий кэш и активирована PRAGMA read_uncommitted, все транзакции в SQLite предоставляют «сериализуемую» изоляцию. SQLite реализует сериализуемые транзакции путем фактической сериализации записей, что позволяет одному соединению записывать в базу данных одновременно.
Может быть открыто множество соединений, и все они имеют возможность записывать в файл базы данных, но порядок выполнения этих операций должен соблюдаться. SQLite использует блокировки для автоматической сериализации записей, и приложению не требуется управлять этим вручную.
По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.
Изоляция и управление параллелизмом: WAL и журнал отката
SQLite реализует изоляцию и управление параллелизмом, а также атомарность, благодаря временным журналам, которые создаются в том же каталоге, что и файл базы данных. Существует два основных режима ведения журнала.
Старый «режим отката» соответствует параметрам DELETE, PERSIST или TRUNCATE для прагмы journal_mode. При этом изменения записываются непосредственно в файл базы данных, в то время как создается отдельный файл журнала отката, который может восстановить базу данных в первоначальное состояние в случае отката транзакции.
В этом режиме журнал отката, например, в случае DELETE, удаляется по завершении каждой транзакции и является стандартным поведением.
Начиная с версии 3.7.0 (2010-07-21), SQLite также поддерживает «режим WAL» (Write-Ahead Logging, журнал с упреждающей записью). В режиме WAL изменения не записываются в исходный файл базы данных. Вместо этого они записываются в файл WAL. Позже, после фиксации транзакции, эти изменения перемещаются из файла WAL обратно в исходную базу данных в операции, называемой «контрольной точкой» (checkpoint). Режим WAL включается командой PRAGMA journal_mode=WAL.
В режиме отката SQLite обеспечивает изоляцию, блокируя файл базы данных и предотвращая его чтение другими соединениями в процессе выполнения записывающей транзакции. Читатели могут оставаться активными в начале записи — до записи на диск, пока все изменения находятся в памяти писателя.
Тем не менее, до записи любых изменений на диск все активные читатели должны завершить чтение, чтобы предоставить писателю эксклюзивный доступ. Следовательно, читатели не могут видеть незавершенные транзакции, так как они недоступны во время записи. Только после полного завершения транзакции и её фиксации читатели могут вернуться к базе данных.
Режим WAL допускает одновременных читателей и писателей. Это возможно потому, что изменения не перезаписывают исходный файл базы данных, а вместо этого записываются в файл WAL. Читатели могут продолжать читать старое, неизменённое содержимое из исходного файла базы данных в то же время, когда писатель добавляет данные в журнал с упреждающей записью.
В режиме WAL SQLite демонстрирует «изоляцию снимков» (snapshot isolation). Когда начинается транзакция чтения, читатель продолжает видеть неизменяющийся «снимок» (snapshot) файла базы данных в том состоянии, в котором он существовал в момент начала транзакции.
Любые транзакции записи, которые фиксируются во время активной транзакции чтения, по-прежнему невидимы для неё, поскольку читатель видит снимок из более раннего момента времени.
Режим WAL допускает одновременную работу читателей и писателей, так как изменения не перезаписывают основной файл базы данных, а записываются в отдельный файл WAL. Читатели могут продолжать работать со старой, неизменённой информацией из файла базы данных в то время, как писатель добавляет данные в журнал. В режиме WAL SQLite демонстрирует «изоляцию снимков».
Когда начинается транзакция чтения, читатель видит неизменённый файл базы данных в том состоянии, в котором он находился на момент начала транзакции. Все транзакции записи, которые фиксируются во время активной транзакции чтения, скрыты от читателя.
Рассмотрим на примерах. Пусть есть две подключения: X и Y. X открывает транзакцию чтения с помощью BEGIN и выполняет несколько операторов SELECT. В это время Y выполняет оператор UPDATE, изменяя данные в базе. Когда X пытается выполнить SELECT для получения записей, измененных Y, он увидит старые, неизмененные записи, так как изменения Y остаются скрытыми для X до завершения транзакции чтения.
Чтобы увидеть изменения от Y, X необходимо завершить текущую транзакцию, произвести COMMIT, а затем снова открыть новую — выполнить BEGIN.
В другом случае X запускает транзакцию чтения с помощью BEGIN и SELECT, в это время Y вносит изменения через UPDATE. Если в дальнейшем X попытается выполнить свои изменения через UPDATE, операция завершится ошибкой SQLITE_BUSY_SNAPSHOT, так как снимок базы данных, который видит X, больше не является актуальным.
Если X сможет выполнить запись, это приведёт к разветвлению истории базы данных, что SQLite не допускает. Чтобы X мог выполнить запись, ему нужно сначала освободить свой снимок, например, через ROLLBACK, а затем начать новую транзакцию с помощью BEGIN.
Отсутствие изоляции между операциями в одном подключении
SQLite обеспечивает изоляцию между операциями в отдельных подключениях к базе данных. Однако между операциями, происходящими в рамках одного и того же подключения, изоляции нет — и это важно понимать при проектировании приложений.
Если X начинает транзакцию записи с помощью BEGIN IMMEDIATE, а затем выполняет один или несколько операторов UPDATE, DELETE и/или INSERT, то эти изменения видны последующим операторам SELECT, которые выполняются в том же подключении X. Операторы SELECT на другом подключении Y не будут показывать никаких изменений до тех пор, пока транзакция X не будет зафиксирована. Но операторы SELECT в X будут показывать изменения ещё до фиксации.
В рамках одного подключения X оператор SELECT всегда видит все изменения в базе данных, которые завершены до начала оператора SELECT, независимо от того, зафиксированы эти изменения или нет. И оператор SELECT, очевидно, не видит никаких изменений, которые происходят после его завершения. Но что насчёт изменений, которые происходят во время выполнения оператора SELECT?
Что если оператор SELECT запущен и интерфейс sqlite3_step() прошёл примерно через половину своих выходных данных, затем приложение выполняет некоторые операторы UPDATE, которые изменяют таблицу, читаемую оператором SELECT, а затем выполняются дополнительные вызовы sqlite3_step() для завершения оператора SELECT? Увидят ли последующие шаги изменения, сделанные UPDATE, или нет?
Ответ таков: это поведение не определено. Увидит ли оператор SELECT параллельные изменения или нет, зависит от версии SQLite, схемы файла базы данных, был ли запущен ANALYZE, и деталей конкретного запроса. В некоторых случаях это может также зависеть от содержимого файла базы данных.
Не существует надёжного способа узнать, увидит ли оператор SELECT изменения, внесённые в базу данных тем же подключением после запуска оператора. Именно поэтому разработчики должны тщательно избегать написания приложений, которые делают предположения о том, что произойдёт в этом случае.
Если приложение выполняет оператор SELECT для одной таблицы — например, SELECT rowid, * FROM table WHERE ... — и начинает проходить через выходные данные с помощью sqlite3_step(), то для приложения безопасно удалять текущую строку или любую предыдущую строку с помощью DELETE FROM table WHERE rowid=?.
Также безопасно (в том смысле, что это не навредит базе данных) удалять строку, которая ожидалась позже в запросе, но ещё не появилась. Однако если будущая строка удалена, может случиться так, что она всё равно появится после последующего sqlite3_step() — даже после того, как предположительно была удалена. А может и не появиться. Это поведение не определено.
Приложение также может вставлять новые строки в таблицу во время выполнения оператора SELECT, но появятся ли новые строки в последующих sqlite3_step() — тоже не определено. И приложение может обновлять текущую строку или любую предыдущую строку, хотя это может привести к тому, что эта строка снова появится в последующем sqlite3_step().
Пока приложение готово справляться с этими неоднозначностями, сами операции безопасны и не навредят файлу базы данных.
Для целей двух предыдущих абзацев два подключения к базе данных, которые имеют один и тот же общий кэш и которые включили PRAGMA read_uncommitted, считаются одним и тем же подключением к базе данных.
Типичные ошибки при работе с изоляцией в SQLite
Изучив документацию и поработав с SQLite на практике, я выделил несколько ошибок, которые встречаются чаще всего. Мой опыт показывает, что большинство из них возникают из-за неверных предположений о видимости изменений.
Предположение о видимости изменений между подключениями до фиксации. Разработчики иногда ожидают, что изменения, сделанные одним подключением, сразу видны другому. В SQLite это не так: другое подключение увидит изменения только после COMMIT.
Смешение BEGIN и BEGIN IMMEDIATE без понимания последствий. Если транзакция начата с обычного BEGIN в режиме WAL, а затем потребовалась запись, можно получить ошибку SQLITE_BUSY_SNAPSHOT. Использование BEGIN IMMEDIATE с самого начала позволяет избежать этой ситуации.
Изменение таблицы во время итерации по результатам SELECT. Как описано выше, поведение в этом случае не определено. Безопаснее собрать все нужные строки в память, завершить оператор SELECT, а затем выполнять изменения.
Неправильное понимание режима общего кэша с read_uncommitted. Включение PRAGMA read_uncommitted в режиме общего кэша фактически объединяет два подключения в одно с точки зрения изоляции — это нужно учитывать при проектировании многопоточных приложений.
Игнорирование разницы между режимами WAL и rollback. Режим WAL обеспечивает изоляцию снимков и допускает одновременных читателей и писателей, тогда как режим отката блокирует читателей на время записи. Выбор режима влияет на поведение параллельных операций.
Итоговые правила изоляции
Транзакции в SQLite являются сериализуемыми (SERIALIZABLE).
Изменения, сделанные в одном подключении к базе данных, невидимы для всех других подключений до фиксации.
Запрос видит все изменения, которые завершены в том же подключении до начала запроса, независимо от того, зафиксированы эти изменения или нет.
Если изменения происходят в том же подключении после начала выполнения запроса, но до его завершения, то не определено, увидит ли запрос эти изменения.
Для целей четырёх предыдущих пунктов два подключения, которые используют один и тот же общий кэш и которые включают PRAGMA read_uncommitted, считаются одним и тем же подключением к базе данных, а не отдельными подключениями.
Часто задаваемые вопросы об изоляции в SQLite
Что такое изоляция снимков в режиме WAL и чем она отличается от режима отката? В режиме WAL читатель видит неизменяющийся снимок базы данных на момент начала своей транзакции — даже если другие подключения фиксируют изменения в это время. В режиме отката читатели блокируются на время записи транзакции на диск, поэтому одновременных читателей и писателей там нет.
Почему возникает ошибка SQLITE_BUSY_SNAPSHOT и как её избежать? Эта ошибка возникает в режиме WAL, когда транзакция чтения пытается повыситься до транзакции записи, но снимок базы данных, который она видит, уже устарел — другое подключение успело зафиксировать изменения. Чтобы избежать этой ошибки, нужно начинать транзакцию с BEGIN IMMEDIATE, если заранее известно, что потребуется запись.
Безопасно ли изменять таблицу во время итерации по результатам SELECT в одном подключении? Сами операции не навредят файлу базы данных, однако поведение не определено: строки могут появляться повторно, не появляться вовсе или вести себя непредсказуемо. Рекомендуется завершить оператор SELECT перед выполнением изменений.
Что происходит, если два подключения используют общий кэш и включена PRAGMA read_uncommitted? В этом случае читатель сможет видеть незафиксированные изменения писателя. Кроме того, с точки зрения правил изоляции такие два подключения считаются одним и тем же подключением к базе данных.
Чем BEGIN IMMEDIATE отличается от обычного BEGIN? Обычный BEGIN начинает транзакцию отложенно и не блокирует других писателей сразу. BEGIN IMMEDIATE немедленно начинает транзакцию записи и блокирует всех остальных писателей — это гарантирует, что последующие операции в транзакции не завершатся ошибкой SQLITE_BUSY.
