Транзакции с Python sqlite3
Я пытаюсь перенести некоторый код в Python, который использует базы данных sqlite, и я пытаюсь заставить транзакции работать, и я действительно запутался. Я действительно смущен этим; Я часто использовал sqlite на других языках, потому что это здорово, но я просто не могу понять, что здесь не так.
Вот схема для моей тестовой базы данных (для подачи в инструмент командной строки sqlite3).
BEGIN TRANSACTION;
CREATE TABLE test (i integer);
INSERT INTO "test" VALUES(99);
COMMIT;
Вот тестовая программа.
import sqlite3
sql = sqlite3.connect("test.db")
with sql:
c = sql.cursor()
c.executescript("""
update test set i = 1;
fnord;
update test set i = 0;
""")
Вы можете заметить преднамеренную ошибку в этом. Это приводит к сбою сценария SQL во второй строке после выполнения обновления.
Согласно документам, with sql Предполагается, что оператор устанавливает неявную транзакцию вокруг содержимого, которая фиксируется только в случае успеха блока. Тем не менее, когда я запускаю его, я получаю ожидаемую ошибку SQL... но значение i устанавливается от 99 до 1. Я ожидаю, что оно останется равным 99, потому что это первое обновление должно быть откатано.
Вот еще одна тестовая программа, которая явно вызывает commit() а также rollback(),
import sqlite3
sql = sqlite3.connect("test.db")
try:
c = sql.cursor()
c.executescript("""
update test set i = 1;
fnord;
update test set i = 0;
""")
sql.commit()
except sql.Error:
print("failed!")
sql.rollback()
Это ведет себя точно так же --- меняют с 99 на 1.
Теперь я звоню BEGIN и COMMIT явно:
import sqlite3
sql = sqlite3.connect("test.db")
try:
c = sql.cursor()
c.execute("begin")
c.executescript("""
update test set i = 1;
fnord;
update test set i = 0;
""")
c.execute("commit")
except sql.Error:
print("failed!")
c.execute("rollback")
Это тоже не получается, но по-другому. Я получаю это:
sqlite3.OperationalError: cannot rollback - no transaction is active
Однако, если я заменю звонки на c.execute() в c.executescript(), тогда это работает (я остаюсь на 99)!
(Я должен также добавить, что если я поставлю begin а также commit внутри внутреннего вызова executescript тогда он ведет себя правильно во всех случаях, но, к сожалению, я не могу использовать этот подход в моем приложении. Кроме того, меняется sql.isolation_level кажется, не имеет никакого значения для поведения.)
Может кто-нибудь объяснить мне, что здесь происходит? Мне нужно это понять; если я не могу доверять транзакциям в базе данных, я не могу заставить свое приложение работать...
Python 2.7, python-sqlite3 2.6.0, sqlite3 3.7.13, Debian.
7 ответов
API-интерфейс БД Python пытается быть умным, и начинает и фиксирует транзакции автоматически.
Я бы порекомендовал использовать драйвер БД, который не использует API-интерфейс БД Python, например apsw.
Для тех, кто хотел бы работать с библиотекой sqlite3 независимо от ее недостатков, я обнаружил, что вы можете сохранить некоторый контроль над транзакциями, если будете выполнять эти две вещи:
- задавать
Connection.isolation_level = None(согласно документам это означает режим автоматической фиксации) - избегать использования
executescriptвообще, потому что согласно документам он "сначала выдает оператор COMMIT" - т.е. проблема. На самом деле я обнаружил, что это мешает любым вручную установленным транзакциям
Итак, для меня работает следующая адаптация вашего теста:
import sqlite3
sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
try:
c = sql.cursor()
c.execute("begin")
c.execute("update test set i = 1")
c.execute("fnord")
c.execute("update test set i = 0")
c.execute("commit")
except sql.Error:
print("failed!")
c.execute("rollback")
Согласно документам,
Объекты соединения могут использоваться в качестве контекстных менеджеров, которые автоматически фиксируют или откатывают транзакции. В случае исключения транзакция откатывается; в противном случае сделка совершается:
Поэтому, если вы позволите Python выйти из оператора with при возникновении исключения, транзакция будет откатана.
import sqlite3
filename = '/tmp/test.db'
with sqlite3.connect(filename) as conn:
cursor = conn.cursor()
sqls = [
'DROP TABLE IF EXISTS test',
'CREATE TABLE test (i integer)',
'INSERT INTO "test" VALUES(99)',]
for sql in sqls:
cursor.execute(sql)
try:
with sqlite3.connect(filename) as conn:
cursor = conn.cursor()
sqls = [
'update test set i = 1',
'fnord', # <-- trigger error
'update test set i = 0',]
for sql in sqls:
cursor.execute(sql)
except sqlite3.OperationalError as err:
print(err)
# near "fnord": syntax error
with sqlite3.connect(filename) as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM test')
for row in cursor:
print(row)
# (99,)
доходность
(99,)
как и ожидалось.
Вот что, я думаю, происходит на основе моего прочтения привязок Python к sqlite3, а также официальных документов Sqlite3. Короткий ответ: если вам нужна правильная транзакция, вы должны придерживаться этой идиомы:
with connection:
db.execute("BEGIN")
# do other things, but do NOT use 'executescript'
Вопреки моей интуиции, with connection не звонит BEGIN при входе в сферу. На самом деле это ничего не делает в __enter__, Это действует только тогда, когда вы __exit__ сфера, выбирая либо COMMIT или же ROLLBACK в зависимости от того, выходит ли область нормально или с исключением.
Следовательно, правильно всегда явно отмечать начало ваших транзакций, используя BEGIN, Это делает isolation_level не имеет значения в транзакциях, потому что, к счастью, это действует только при включенном режиме автоматической фиксации, а режим автоматической фиксации всегда блокируется в блоках транзакций.
Еще одна странность executescript, который всегда выдает COMMIT перед запуском вашего скрипта. Это может легко испортить транзакции, поэтому вы можете выбрать
- использовать ровно один
executescriptв рамках транзакции и ничего больше, или - избежать
executescriptполностью; ты можешь позвонитьexecuteстолько раз, сколько вы хотите, при условииexecuteограничение.
Вы можете использовать соединение как менеджер контекста. Затем он автоматически откатит транзакции в случае исключения или зафиксирует их в противном случае.
try:
with con:
con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
print("couldn't add Joe twice")
Нормальный .execute() работает как ожидалось с удобным режимом автоматической фиксации по умолчанию и with conn: ... менеджер контекста, выполняющий автоматическую фиксацию ИЛИ откат - за исключением защищенных транзакций чтения-изменения-записи, которые описаны в конце этого ответа.
Нестандартный модуль sqlite3 conn_or_cursor.executescript() не участвует в режиме автоматической фиксации (по умолчанию) (и поэтому не работает нормально с with conn: ... менеджер контекста) но пересылает скрипт довольно сырой. Для этого он просто фиксирует потенциально ожидающие транзакции автоматической фиксации при запуске, прежде чем "перейти в исходное состояние".
Это также означает, что без "НАЧАЛА" внутри скрипта executescript() работает без транзакции и, следовательно, без опции отката при ошибке или иным образом.
Так с executescript() нам лучше использовать явный BEGIN (так же, как ваш скрипт создания исходной схемы для инструмента командной строки sqlite в "сыром" режиме). И это взаимодействие показывает, шаг за шагом, что происходит:
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
OperationalError: near "FNORD": syntax error
>>> list(conn.execute('SELECT * FROM test'))
[(1,)]
>>> conn.rollback()
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>>
Скрипт не дошел до "COMMIT". И, таким образом, мы могли бы просмотреть текущее промежуточное состояние и принять решение об откате (или, тем не менее, зафиксировать)
Таким образом, работает пробный откат через excecutescript() выглядит так:
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> try: conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
... except Exception as ev:
... print("Error in executescript (%s). Rolling back" % ev)
... conn.executescript('ROLLBACK')
...
Error in executescript (near "FNORD": syntax error). Rolling back
<sqlite3.Cursor object at 0x011F56E0>
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>>
(Обратите внимание на откат через скрипт здесь, потому что нет .execute() взял на себя управление фиксацией)
А здесь заметка о режиме автоматической фиксации в сочетании с более сложной проблемой защищенной транзакции чтения-изменения-записи, которая заставила @Jeremie сказать: " Из всего много-много вещей, написанных о транзакциях в sqlite/python, это это единственное, что позволяет мне делать то, что я хочу (иметь эксклюзивную блокировку чтения в базе данных). "в комментарии к примеру, который включал c.execute("begin"), Хотя sqlite3 обычно не делает длинную блокирующую монопольную блокировку чтения, за исключением продолжительности фактической обратной записи, но более умные 5-ступенчатые блокировки обеспечивают достаточную защиту от перекрывающихся изменений.
with conn: Контекст автоматической фиксации еще не устанавливает или не вызывает достаточно сильную блокировку для защищенного чтения-изменения-записи в 5-этапной схеме блокировки sqlite3. Такая блокировка выполняется неявно только тогда, когда выдается первая команда для изменения данных, поэтому слишком поздно. Только явный BEGIN (DEFERRED) (TRANSACTION) запускает требуемое поведение:
Первая операция чтения для базы данных создает блокировку SHARED, а первая операция записи создает блокировку RESERVED.
Таким образом, защищенная транзакция чтения-изменения-записи, которая использует язык программирования в общем виде (а не специальное атомарное предложение SQL UPDATE), выглядит следующим образом:
with conn:
conn.execute('BEGIN TRANSACTION') # crucial !
v = conn.execute('SELECT * FROM test').fetchone()[0]
v = v + 1
time.sleep(3) # no read lock in effect, but only one concurrent modify succeeds
conn.execute('UPDATE test SET i=?', (v,))
В случае сбоя такая транзакция чтения-изменения-записи может быть повторена несколько раз.
Это немного старый поток, но если это поможет, я обнаружил, что откат объекта соединения делает свое дело.