Транзакция в SQLite может быть явно начата SQL-командой BEGIN TRANSACTION или неявно начинаться вместе с подключением к базе данных (второе поведение бывает, если подключение произведено не в режиме auto_commit. CLI клиент sqlite3 всегда работает в режиме auto_commit, библиотеки sqlite3 в различных языках программирования могут вести себя по-разному - напр. в питоновском модуле sqlite3 (он же pysqlite2.dbapi2 в python 2.4) auto_commit по умолчанию выключен и может быть включен необязательным аргументом isolation_level метода connect(), установленным в None.
Тут осмелюсь заметить, что - если многопользовательский доступ имеет для вас значение - я бы предложил так и делать - ибо при запуске транзакций явно SQL-командами BEGIN TRANSACTION они ведут себя так, как описано ниже, а при использовании же других значений isolation_level, кроме None - т.е. при неявном старте транзакций - их поведение показалось мне весьма своеобразным. Судя по всему, неявная транзакция запускается первой командой изменения данных, а при таком поведении разница между DEFERRED и IMMEDIATE (подробнее см. ниже) делается не наблюдаемой. Остается два вида транзакций - EXCLUSIVE и все остальные.
Если же мы подключились в режиме auto_commit и команда BEGIN TRANSACTION явно не запускалась, то каждая SQL-команда изменения схемы данных или самих данных будет запускаться отдельной транзакцией без возможности отката (в чем собственно и состоит auto_commit). Такое поведение проще для понимания, но для большого количества запросов не вполне рационально (напр. у меня 30 млн. команд INSERT в таблицу из 8 числовых полей выполнялись 13 минут без auto_commit (в одной транзакции), а с auto_commit дождаться окончания процесса так и не удалось - методом экспликации можно предположить, что процесс длился бы чуть больше месяца).
По умолчанию транзакция запускается в режиме DEFERRED (т.е. отложенный). Его отличие от возможных параметров IMMEDIATE (т.е. непостредственный) и EXCLUSIVE (это слово уже кажется переводить нет нужды) обсуждается ниже на примерах.
Дано: база данных /tmp/test.db с таблицей следующей схемы:
CREATE TABLE t1 (a integer primary key, b text);
Создавать несколько таблиц для нашего тестирования бесполезно т.к. в SQLite 3 транзакция все равно блокирует всю базу (как это ни печально).
Итак, поключаемся к базе двумя CLI клиентами (синий и красный) и начинаем тесты.
Сперва DEFERRED:
sqlite> begin transaction;
sqlite> select * from t1;
sqlite> insert into t1 (b) values ('red insert on deferred');
sqlite> select * from t1;
1|red insert on deferred
Как видно из этого абзаца, хотя транзакция уже запущена, база не заблокирована ни на чтение, ни на запись.
sqlite> insert into t1 (b) values ('blue insert on deffered');
sqlite> select * from t1;
1|red insert on deferred
sqlite> insert into t1 (b) values ('red insert on deferred');
Error: database is locked
а вот после первого INSERT в синем клиенте, красный уже не может писать в базу (такое поведение и называется отложенным). Читать он может, но результатов работы незаконченной "синей" транзакции он не видит - т.к. read_uncommited по умолчанию выключен.
sqlite> commit;
sqlite> select * from t1;
1|red insert on deferred
2|blue insert on deffered
sqlite> insert into t1 (b) values ('red insert on deferred');
sqlite> select * from t1;
1|red insert on deferred
2|blue insert on deffered
3|red insert on deferred
после "синего" COMMIT, "красный" увидел результаты накаченной транзакции - и вновь может писать в базу. Добавим еще, что в нашем случае "красный" работал в режиме auto_commit, но при DEFERRED транзакциях ничего бы не изменилось, если б он запускал BEGIN TRANSACTION, потому что при отложенном поведении база блокируется не началом транзакции, а первым оператором изменения данных в ней (т.е. фактически BEGIN TRANSACTION не делает ничего).
Теперь посмотрим, как поведут себя те же клиенты при IMMEDIATE транзакциях.
sqlite> begin immediate transaction;
sqlite> select * from t1;
sqlite> insert into t1 (b) values ('red insert on immediate');
Error: database is locked
sqlite> begin immediate transaction;
Error: database is locked
с началом транзакции база заблокирована на запись - вставить запись нельзя, начать транзакцию тоже нельзя.
sqlite> insert into t1 (b) values ('blue insert on immediate');
sqlite> select * from t1;
"красному" ничего не видно - т.к. read_uncommited никто не включал
sqlite> commit;
sqlite> select * from t1;
1|blue insert on immediate
sqlite> insert into t1 (b) values ('red insert on immediate');
sqlite> select * from t1;
1|blue insert on immediate
2|red insert on immediate
после наката транзакции стали видны ее результаты - и снялась блокировка на запись.
Теперь самый суровый вариант транзакции - EXCLUSIVE:
sqlite> begin exclusive transaction;
sqlite> select * from t1;
Error: database is locked
sqlite> insert into t1 (b) values ('red insert on exclusive');
Error: database is locked
sqlite> begin exclusive transaction;
Error: database is locked
"красному" нельзя ничего - база у нас теперь эксклюзивная.
Осталось написать только про read_uncommited. Включается она по идее должна так:
PRAGMA read_uncommited = 1;
Но увидеть ее в деле мне не удалось ни в версии 3.3.6, ни в 3.7.3 (платформа - Linux). Документация безстрастно сообщает, что некоторые прагмы могут не поддерживаться в некоторых версиях - и установка неизвестной прагмы ошибки не вызывает. Вероятно, это тот самый вариант.
Вообще знакомство с транзакциями в SQLite породило у меня ощущение, что ими можно пользоваться лишь для ускорения операций записи в базу данных (см. выше вопиющий пример с 30 млн. записей). Можно ли рассчитывать на них при реальном наличии конкурентного доступа к БД - лично для меня - большой вопрос.
